December 24, 2007 at 10:37 am
Is there a way to write a Select statment with in a CASE Statement:
Such as the following:
SET @ConsultantID = '0003065';
SELECTc1.ConsultantID
,c1.EffectiveDate
,c1.FirstName + ' ' + c1.LastName as ConsultantName
,'NACConsultant' =
CASE
WHEN NACDATE IS NOT NULL THEN 'NAC Consultant'
ELSE NULL
END
,NacDate
,'Consultant' =
CASE
WHEN ConsultantDate IS NOT NULL THEN 'Consultant'
ELSE NULL
END
,ConsultantDate
,'SrConsultant' =
CASE
WHEN SrConsultantDate IS NOT NULL THEN 'Senior Consultant'
ELSE NULL
END
,SrConsultantDate
,'TeamLeader' =
CASE
WHEN TeamLeaderDate IS NOT NULL THEN 'Team Leader'
ELSE NULL
END
,TeamLeaderDate
,'TeamManager' =
CASE
WHEN TeamManagerDate IS NOT NULL THEN 'Team Manager'
ELSE NULL
END
,TeamManagerDate
,'SrTeamManager' =
CASE
WHEN SrTeamManagerDate IS NOT NULL THEN 'Senior Team Manager'
ELSE NULL
END
,SrTeamManagerDate
,'TeamMentor' =
CASE
WHEN TeamMentorDate IS NOT NULL THEN 'Team Mentor'
WHEN (SELECT r.AchieveTitle FROM Repromotes r WHERE RepFlag = 'X' AND AchieveTitle = 'Team Mentor) THEN 'Team Mentor*'
ELSE NULL
END
,TeamMentorDate
,c1.SponsorID
,(SELECT DISTINCT b1.FirstName + ' ' + b1.LastName FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1 ON c1.SponsorID = B1.ConsultantID) AS SponsorName,
FROM dbo.consultant c1
LEFT OUTER JOIN Repromotes r On c1.Consultantid = r.consultantID
where c1.Effectivedate =
(
SELECT
MAX (c2.EffectiveDate)
FROM
Consultant c2
where c1.Consultantid = c2.ConsultantID
)
AND c1.ConsultantID = @ConsultantID
Our how could I write this. I need to have an asterik appear by Achieve Title if there is a X in the Repflag from the repromote table.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 24, 2007 at 12:27 pm
Yes - you can, but I am not sure you need this. Anyways, for that part - you can use an EXISTS, or check for NOT NULL. Examples:
CASE
WHEN EXISTS(SELECT ... FROM ... WHERE ...) THEN 'value'
WHEN (SELECT ... FROM ... WHERE ...) IS NOT NULL THEN 'value'
END
Based upon what you have shown, I don't think you need this. Your sub-query is not a correlated sub-query, so you could set a variable and just use that variable.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 24, 2007 at 12:38 pm
Can you explain that last statement? An Example might help.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 24, 2007 at 12:53 pm
Sure, what you have is:
,'TeamMentor' =
CASE
WHEN TeamMentorDate IS NOT NULL THEN 'Team Mentor'
WHEN (SELECT r.AchieveTitle FROM Repromotes r WHERE RepFlag = 'X' AND AchieveTitle = 'Team Mentor) THEN 'Team Mentor*'
ELSE NULL
END
Your sub-query is not correlated, which means there are no column values you are checking to determine the value of Team Mentor. So, you can do this instead:
DECLARE @TeamMentorFLAG varchar(12);
SET @TeamMentorFLAG =
CASE WHEN (SELECT r.AchieveTitle FROM Repromotes r WHERE RepFlag = 'X' AND AchieveTitle = 'Team Mentor) IS NOT NULL THEN '*' ELSE '' END;
And in your query, it can be changed to:
,'TeamMentor' =
CASE WHEN TeamMentorDate IS NOT NULL Then 'Team Mentor' + @TeamMentorFLAG
ELSE NULL END;
Of course, I am guessing that this is probably not the intent - and that you want a correlated sub-query here.
BTW - personally, I would create another column called 'TeamMentorFLAG' instead of concantenating the value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy