최고의 답변자
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.
질문
답변
-
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
-
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
모든 응답
-
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
-
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
-
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.
-
@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)
-
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
-
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
-
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
-
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