asked on

Need help with CASE statement in select statement

I am trying to use a case statement in my UPDATE statement below in order to set the variable CEDM_ENVT_CODE. I have not had any success, and need to ask for some expert help.
Can someone help me out here?

DECLARE @ENVT_CODE AS CHAR(10)
DECLARE @PRODUCT_SUITE_CODE AS CHAR(10)

SET @ENVT_CODE = 'BOL'
SET @PRODUCT_SUITE_CODE = 'U2FD'

UPDATE STAGER_BLLD_ASSOCIATE_USERS
SET
PSTE_CODE = @ENVT_CODE
CEDM_ENVT_CODE = (CASE WHEN POLM_USER_ID IN ( SELECT POLM_USER_ID FROM STAGER_BLLD_ENVT_USERIDS )
THEN @PRODUCT_SUITE_CODE
ELSE NULL
END)

PortletPaul

there is a comma missing between the 2 fields being set, see line 9 below

DECLARE @ENVT_CODE AS char(10)
DECLARE @PRODUCT_SUITE_CODE AS char(10)

SET @ENVT_CODE = 'BOL'
SET @PRODUCT_SUITE_CODE = 'U2FD'

UPDATE STAGER_BLLD_ASSOCIATE_USERS
SET PSTE_CODE = @ENVT_CODE
  , CEDM_ENVT_CODE = (CASE
          WHEN POLM_USER_ID IN (
                      SELECT
                            POLM_USER_ID
                      FROM STAGER_BLLD_ENVT_USERIDS
                ) THEN @PRODUCT_SUITE_CODE
          ELSE NULL
    END)

Open in new window

or is it something more that you need?

ASKER CERTIFIED SOLUTION

b_levitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

b_levitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

b_levitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brgdotnet

ASKER

Yes, I forgot the Comma Portlet Paul. I am not sure about using two updates, I am still trying to digest breaking it up into two statements.

brgdotnet

ASKER

I gave Portlet Paul good points, because he caught the error, and b levitt so graciously accomdated the change based upon portelt pauls answer. Thank you guys!!!

Jim Horn

Beyond Paul catching the comma, do you have any other needs not stated in this question?

>in order to set the variable CEDM_ENVT_CODE.
btw I don't see a @ before CEDM_ENVT_CODE, so it's not a variable. Is it a column on table STAGER_BLLD_ASSOCIATE_USERS, or is there a typo somewhere else?

btw I have an article out there on SQL Server CASE Solutions, with a wompload of examples, if it helps.