none
Using where in case clause

    질문

  • I have a following User table:

    GroupID

    AssignedUserID

    RoleID

    ActionRequired

    ActionTaken

    ActualUserID

     

     

    A1

    1001

    10

    Submit

    Submit

    100

     

     

    A1

    101

    11

    Submit

    Sent Back

    101

     

     

    A1

    1001

    10

    Edit

    NULL

    NULL

     

     

    B1

    200

    1

    Submit

    Submit

    100

     

     

    B1

    201

    11

    Submit

    Submit

    101

     

     

    B1

    202

    1

    Submit

    NULL

    NULL

     

     

     

     

     

     

     

     

     

     

     

    I need to get  USerID from two different columns of the tables based on certain conditions

    Select UserID=CASE WHEN AssignedUserID >1000 And ActionRequired=’Edit’, Then ActualUserID where RoleID=10 And ActionTaken=’Submit’))

                                           ELSE AssignedUserID

    FROM Users

    WHERE UserID=@UserID

    How can this be achieved?

     

    Thanks in advance.


    2018년 7월 13일 금요일 오후 12:43

모든 응답

  • SELECT CASE WHEN AssignedUserId>1000 AND ActionRequired='Edit' AND RoleId=10 AND ActionTaken='Submit' THEN ActualUserId ELSE AssignedUserId END UserId

    FROM Users


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    • 편집됨 Ousama EL HOR 2018년 7월 13일 금요일 오후 12:53
    2018년 7월 13일 금요일 오후 12:53
  • Two ways

    using CASE.. WHEN

    Select 
    
    UserID=CASE 
    WHEN AssignedUserID >1000 
    And ActionRequired='Edit' 
    And RoleID=10 
    And ActionTaken='Submit'
    THEN ActualUserID
    ELSE AssignedUserID
    END
    FROM Users
    WHERE UserID=@UserID

    using IIF

    Select 
    
    UserID=IIF(AssignedUserID >1000 
    And ActionRequired='Edit' 
    And RoleID=10 
    And ActionTaken='Submit',ActualUserID,AssignedUserID)
    FROM Users
    WHERE UserID=@UserID


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 13일 금요일 오후 1:35
  • It seems when four conditions are met, UserID is equal to ActualUserID. Otherwise it is AssignedUserID. If yes, Ousama's query is your solution. If not, list the output of your sample data.

    A Fan of SSIS, SSRS and SSAS

    2018년 7월 13일 금요일 오후 1:35
  • I think I need to be more specific.

    If  AssignedUserID >1000 And ActionRequired=’Edit’ returns null, then I need to fetch  ActualUserID where RoleID=10 And ActionTaken=’Submit’

    In above case, row 3 gives me a null value for ActualUserID, so I need to get the actualUserId from first row.

    2018년 7월 13일 금요일 오후 5:20
  • Post your expected result from your sample data in a tabular format. Thanks.
    2018년 7월 13일 금요일 오후 5:31
    중재자
  • @UserId      O/P(UserID)

    1001            100 (Given condition is satisfied, the value is null for ActualUserID so ActualUserID from 1st record is                              fetched)

    101               101(Given condition is not  satisfied, so ActualUserID  is fetched)

    202               202 (Given condition is not satisfied, the value is null for ActualUserID so AssignedUserID is fetched)

    2018년 7월 13일 금요일 오후 5:52
  • Just your expected result from your sample data like the way you posted in your beginning question.

    (we will figure it out).

    GroupID

    AssignedUserID

    RoleID

    ActionRequired

    ActionTaken

    ActualUserID

     

     

    A1

    1001

    10

    Submit

    Submit

    100

     

     

    A1

    101

    11

    Submit

    Sent Back

    101

     

     

    A1

    1001

    10

    Edit

    NULL

    NULL

     

     

    B1

    200

    1

    Submit

    Submit

    100

     

     

    B1

    201

    11

    Submit

    Submit

    101

     

     

    B1

    202

    1

    Submit

    NULL

    NULL

     

     

    your expected result:

    GroupID

    AssignedUserID

    RoleID

    ActionRequired

    ActionTaken

    ActualUserID

     

     

     

     

     

    2018년 7월 13일 금요일 오후 5:56
    중재자
  • A simple self join will do what you want:

    select [UserID] = case
                          when [u].[AssignedUserID] > 1000
                               and [u].[ActionRequired] = 'Edit'
                          then [u10].[ActualUserID]
                          else [u].[AssignedUserID]
                      end
    from [Users] [u]
         left outer join [Users] [u10]
             on [u].[GroupId] = [u10].[GroupId]
                and [u].[AssignedUserId] = [u10].[AssignedUserId]
                and [u10].[RoleId] = 10
                and [u10].[ActionTaken] = 'Submit'
    where [u].[UserID] = @UserID;

    2018년 7월 13일 금요일 오후 6:23
    중재자
  • Our first problem is that the CASE Construct in SQL is an expression, and not a clause. This is a really fundamental concept in programming and you’ve missed it. This is not just SQL!

    >> I have a following User table: <<

    Then you fail to post any DDL, but the column names you published don’t follow ISO 11179 naming rules and we have no DDL anyway. This means that we have to sit down and do everything that you were supposed to post for you. This does not make friends for you. 

    CREATE TABLE Something_Users
    (group_id CHAR(2) NOT NULL
     CHECK(group_id LIKE ‘[A-Z][0-9]’),
    assigned_user_id CHAR(4) NOT NULL 
     CHECK (‘[0-9][0-9][0-9][0-9]’)
    role_id CHAR(2) NOT NULL CHECK (‘[0-9][0-9]’),
    required_something_action VARCHAR(10) 
     CHECK (required_something_action IN ('Submit', 'Sent Back', 'Edit'),
    taken_something_action VARCHAR(10) 
     CHECK (required_something_action IN ('Submit', 'Sent Back', 'Edit'),
    actual_user_id CHAR(4) NOT NULL 
     CHECK (‘[0-9][0-9][0-9][0-9]’),
    --- only possible key!!!
    PRIMARY KEY (group_id, assigned_user_id, role_id, required_something_action)
    );
     
    INSERT INTO Something_Users
    VALUES, 
    (‘A1', '1001', '10', 'Submit', 'Submit', '100'), 
    (‘A1', '0101', '11', 'Submit', 'Sent Back', '101'), 
    (‘A1', '1001', '10', 'Edit', 'NULL', 'NULL'), 
    (‘B1', '0200', '01', 'Submit', 'Submit', '100'), 
    (‘B1', '0201', '11', 'Submit', 'Submit', '101'), 
    (‘B1', '0202', '01', 'Submit', 'NULL', 'NULL');

    it’s unusual to have a key in a short, well-designed table that is four columns wide. It’s not automatically wrong, but it is highly suspect. I had to make guesses at the data types. I had some help; anything that is shown as a “_id” has to be character data and not numeric. You cannot do math on an identifier, because it is on a nominal scale (have you had a course in basic data modeling and types of encoding?). I also guessed at the check clauses that you didn’t bother to post

     >> I need to get user_id from two different columns of the tables based on certain conditions <<

    What tables? You never showed us any DDL for even one table. Let’s look at what you posted. You invented your own language! That syntax does not exist in SQL and never has.  Perhaps something like this? 

    SELECT CASE WHEN assigned_user_id  > ‘1000’
                              AND required_something_action = ’edit’
                           THEN actual_user_id 
                            ELSE  assigned_user_id
                           END AS something_user_id
    FROM Something_Users;

    Would you  like t try do this correctly, or just a get kludge? 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2018년 7월 15일 일요일 오후 7:19
  • SELECT UserID = 
    CASE 
    	WHEN AssignedUserID > 1000 AND ActionRequired = 'Edit'
    	THEN
    		CASE 
    			WHEN ActualUserID IS NULL 
    			THEN 
    				CASE 
    					WHEN RoleID = 10 AND ActionTaken= 'Submit'
    					THEN ActualUserID
    					ELSE ???
    				END
    			ELSE AssignedUserID
    		END
    	ELSE ????
    END



    A Fan of SSIS, SSRS and SSAS


    • 편집됨 Guoxiong 2018년 7월 16일 월요일 오후 12:47
    2018년 7월 16일 월요일 오후 12:45