Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • 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
 
PaulCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PaulCommented:
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
 
brgdotnetcontractorAuthor 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
 
brgdotnetcontractorAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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