Remove From My Forums

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

답변

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
- 답변으로 제안됨 Xi JinMicrosoft contingent staff 2018년 7월 27일 금요일 오전 7:46
- 답변으로 표시됨 pituachMVP, Moderator 2018년 7월 29일 일요일 오전 7:49
2018년 7월 13일 금요일 오후 1:35

응답

|

인용

로그인하여 투표

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;

답변으로 제안됨 Xi JinMicrosoft contingent staff 2018년 7월 27일 금요일 오전 7:46
답변으로 표시됨 pituachMVP, Moderator 2018년 7월 29일 일요일 오전 7:49

2018년 7월 13일 금요일 오후 6:23

중재자

모든 응답

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
- 답변으로 제안됨 Xi JinMicrosoft contingent staff 2018년 7월 27일 금요일 오전 7:46
- 답변으로 표시됨 pituachMVP, Moderator 2018년 7월 29일 일요일 오전 7:49
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

응답

|

인용

로그인하여 투표

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

중재자

로그인하여 투표

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;

답변으로 제안됨 Xi JinMicrosoft contingent staff 2018년 7월 27일 금요일 오전 7:46
답변으로 표시됨 pituachMVP, Moderator 2018년 7월 29일 일요일 오전 7:49

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

응답

|

인용

로그인하여 투표

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

제품

Resources

Solutions

업데이트

평가판

관련 사이트

학습

인증 시험

기타 리소스

제품별 지원

기타 지원 링크

일반 사용자 지원

최고의 답변자

Using where in case clause

질문

답변

모든 응답