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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply