Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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)
0
brgdotnet
Asked:
brgdotnet
  • 3
  • 2
  • 2
  • +1
4 Solutions
 
PortletPaulCommented:
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?
0
 
b_levittCommented:
Honestly, just do it in two updates:

UPDATE STAGER_BLLD_ASSOCIATE_USERS
 SET
 PSTE_CODE = @ENVT_CODE,
 CEDM_ENVT_CODE = @PRODUCT_SUITE_CODE
 where POLM_USER_ID IN ( SELECT POLM_USER_ID FROM STAGER_BLLD_ENVT_USERIDS  )
 
 
 UPDATE STAGER_BLLD_ASSOCIATE_USERS
 SET
 PSTE_CODE = @ENVT_CODE,
 CEDM_ENVT_CODE = @PRODUCT_SUITE_CODE
 where POLM_USER_ID not IN ( SELECT POLM_USER_ID FROM STAGER_BLLD_ENVT_USERIDS  )
 

Open in new window

You could certainly combine those using a join but I figured the above might be more in your wheelhouse.  But if you're feeling dangerous:
 
update sbau
set PSTE_CODE = @ENVT_CODE,
CEDM_ENVT_CODE = case when sbeu.polm_user_id is null then null else @ENVT_CODE end
from STAGER_BLLD_ASSOCIATE_USERS sbau
left join (select distinct polm_user_id from STAGER_BLLD_ENVT_USERIDS) sbeu
  on sbau.polm_user_id=sbeu.polm_user_id  
 

Open in new window

0
 
b_levittCommented:
@PortletPaul,

Tip of the hat for catching comma, wag of the finger for the nested query in the select :).  OK for one offs, but you don't want to be teaching any bad habits.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
PortletPaulCommented:
Oh, I agree, I wasn't certain what was being asked.

I prefer to 2 updates you suggest

( except line 11 should be setting the value to NULL I think )
0
 
b_levittCommented:
Thank you sir.

Fixed query is:

UPDATE STAGER_BLLD_ASSOCIATE_USERS
 SET
 PSTE_CODE = @ENVT_CODE,
 CEDM_ENVT_CODE = @PRODUCT_SUITE_CODE
 where POLM_USER_ID IN ( SELECT POLM_USER_ID FROM STAGER_BLLD_ENVT_USERIDS  )
 
 
 UPDATE STAGER_BLLD_ASSOCIATE_USERS
 SET
 PSTE_CODE = @ENVT_CODE,
 CEDM_ENVT_CODE = null
where POLM_USER_ID not IN ( SELECT POLM_USER_ID FROM STAGER_BLLD_ENVT_USERIDS  )

Open in new window

0
 
brgdotnetAuthor Commented:
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.
0
 
brgdotnetAuthor Commented:
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!!!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now