April 26, 2012 at 4:10 am
Please advise with this case statement:
CASE [ApprovalID]
when '35CD3B40-DD5B-4231-9FF4-C4549BBAD6CF' then 'Mickey Mouse'
when '3784053A-7134-4640-A880-2A4E2E72E4BE' then 'Donald Duck'
when 'CB339FE1-CF9F-4C41-91A1-08C326FB2BE7' then 'Roger Rabbit'
END as 'approved by'
if the guids in the case statement are also userid's held in a users table that holds the name of that user, then how can i modify this case statement so that the 'then' part queries the users table and returns the name of the user so some things like this:
CASE [ApprovalID]
when '35CD3B40-DD5B-4231-9FF4-C4549BBAD6CF' then 'select FirstName from user where userID = ApprovalUserID '
END as 'approved by'
thank you
April 26, 2012 at 4:14 am
The DDL along with some sample data will immensely help us in providing a tested solution
Please check the link in my signature on how to do it
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2012 at 4:19 am
Hi thank you, the languagre is t-sql 2005
April 26, 2012 at 4:28 am
AIRWALKER-375999 (4/26/2012)
Hi thank you, the languagre is t-sql 2005
We normally assume it to be SQL Server 2005 as you are posting it in the 2005 forum
Please provide the DDl, sample data and the expected output
Take some time to read the article references in my signature
I am sure you will not regret the time spent when you will receive the answers to your questions
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2012 at 4:41 am
i think what you are trying to do is either an inner join or some kind of where clause ?
e.g.
select name from users where id='1234567890'
rather than
select case when userid='1234567890' then 'mike'
is this for a stored procedure ?
if so then
create proc proc_lookupuser @userid uniqueidentifier
as
select name from user where id=@uniqueidentifier
or do you have some kind of join requirement? does you code looks something like
select time,
case when userid='1234567890' then 'mike' ....
,lastlogon
from logontable
if so then you need a join
select time,
username,
lastlogon
from logontable inner join users on user.id=longontable.userid
you really need to post more details of what you are trying to acheive - including the ddl (ie the definition of the tables involved)
MVDBA
April 26, 2012 at 5:25 am
Without knowing what your tables are and what your full query is, the maximum what I can come up with:
SELECT t.ApprovalID
,u.UserName AS [Approved By]
FROM TableWithApprovalIds AS t
JOIN UserTable AS u --if you don't have ApprovalIds in every "source" record you need to use LEFT JOIN
ON u.UserId = t.ApprovalID
Please follow the link at the bottom of my signature to find out how to ask such type of questions on this forum.
April 26, 2012 at 6:22 am
Thank you all for you're assistance, I think I'll need to redesign the query as what I want do I dont think is possible within a case statement, I think I'll need to write a cursor. Just to confirm, is it possible to have some kind of sub query within a case statement like this pseudo-code:
so example I would write this query against a table called planes, but _parts is another table in the same database
Case [aeroplane_name]
when 'f16' then (select FullName from _parts where name = 'f16')
when 'b52' then (select FullName from _parts where name= 'b52')
would I need pass a variable as aeroplane_name ?
Is something like this even possible witin a case?
April 26, 2012 at 6:27 am
AIRWALKER-375999 (4/26/2012)
Thank you all for you're assistance, I think I'll need to redesign the query as what I want do I dont think is possible within a case statement, I think I'll need to write a cursor. Just to confirm, is it possible to have some kind of sub query within a case statement like this pseudo-code:so example I would write this query against a table called planes, but _parts is another table in the same database
Case [aeroplane_name]
when 'f16' then (select FullName from _parts where name = 'f16')
when 'b52' then (select FullName from _parts where name= 'b52')
would I need pass a variable as aeroplane_name ?
Is something like this even possible witin a case?
then you need a join - not a cursor
select
p.x,
p.y,
p.z
z.fullname
from planes p
inner join _part z on z.aeroplane_name = p.aeroplane_name
is this some kind of homework assignment ?
MVDBA
April 26, 2012 at 6:28 am
AIRWALKER-375999 (4/26/2012)
Thank you all for you're assistance, I think I'll need to redesign the query as what I want do I dont think is possible within a case statement, I think I'll need to write a cursor. Just to confirm, is it possible to have some kind of sub query within a case statement like this pseudo-code:so example I would write this query against a table called planes, but _parts is another table in the same database
Case [aeroplane_name]
when 'f16' then (select FullName from _parts where name = 'f16')
when 'b52' then (select FullName from _parts where name= 'b52')
would I need pass a variable as aeroplane_name ?
Is something like this even possible witin a case?
From whatever I could understand from your description, I don't think you will need a CURSOR or a CASE expression for that matter
If you provide the information we are asking you for, I am sure we can come up with tested solution
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2012 at 6:30 am
Eugene Elutin (4/26/2012)
Without knowing what your tables are and what your full query is, the maximum what I can come up with:
SELECT t.ApprovalID
,u.UserName AS [Approved By]
FROM TableWithApprovalIds AS t
JOIN UserTable AS u --if you don't have ApprovalIds in every "source" record you need to use LEFT JOIN
ON u.UserId = t.ApprovalID
Please follow the link at the bottom of my signature to find out how to ask such type of questions on this forum.
You can use a subquery in a CASE statement but it almost certainly isn't the best way to obtain the result you are looking for. If you can't post sample table scripts etc as others have urged, can you at least post the query you are working on? Eugene has already posted a best guess - but until we see your code and better still some tables from which to work, that's all it will be - a best guess.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2012 at 6:32 am
U can also Write your query with out cursor like
select
p.*
,t.FullName
from plan p
cross apply
(select FullName from part pp where p.name=p.aeroplane_name) t
April 26, 2012 at 6:44 am
I have no idea what you are trying to accomplish with this, but you should be abe to use the statement
CASE [ApprovalID]
when '35CD3B40-DD5B-4231-9FF4-C4549BBAD6CF' then (select FirstName from user where userID = ApprovalUserID)
else ' '
END as 'approved by'
http://msdn.microsoft.com/en-us/library/ms181765(v=sql.90).aspx
If the statement is failing what is the error message it returns?
Dave
April 27, 2012 at 1:52 am
Dave Brooking (4/26/2012)
I have no idea what you are trying to accomplish with this, but you should be abe to use the statement
CASE [ApprovalID]
when '35CD3B40-DD5B-4231-9FF4-C4549BBAD6CF' then (select FirstName from user where userID = ApprovalUserID)
else ' '
END as 'approved by'
http://msdn.microsoft.com/en-us/library/ms181765(v=sql.90).aspx
If the statement is failing what is the error message it returns?
Dave
This is it, thanks very much!!!!
April 27, 2012 at 2:37 am
Why not just use LEFT JOIN?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply