Using where in case clause

모든 응답

• 

  

  1

  로그인하여 투표

  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 "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

  응답

  |

  인용
• 

  

  0

  로그인하여 투표

  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

  응답

  |

  인용
• 

  

  0

  로그인하여 투표

  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

  응답

  |

  인용
• 

  

  0

  로그인하여 투표

  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

  응답

  |

  인용
• 

  

  0

  로그인하여 투표

  Post your expected result from your sample data in a tabular format. Thanks.

  2018년 7월 13일 금요일 오후 5:31

  응답

  |

  인용

  중재자
• 

  

  0

  로그인하여 투표

  @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

  응답

  |

  인용
• 

  

  0

  로그인하여 투표

  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

  • 편집됨 Jingyang LiModerator 2018년 7월 13일 금요일 오후 5:58

  2018년 7월 13일 금요일 오후 5:56

  응답

  |

  인용

  중재자
• 

  

  0

  로그인하여 투표

  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

  응답

  |

  인용

  중재자
• 

  

  0

  로그인하여 투표

  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

  응답

  |

  인용
• 

  

  0

  로그인하여 투표

  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

  응답

  |

  인용