Larry Brister
asked on
Complax Case and Select question
In the following select statement I am now passing in a paramater @vType
In the two Case statements when @vType = 'P' I want to use what I have below....referencing ContactV
When @vType <> 'N' I want the CASE statements to use ContactVN
In the two Case statements when @vType = 'P' I want to use what I have below....referencing ContactV
When @vType <> 'N' I want the CASE statements to use ContactVN
Select vw.Pending,
(
CASE WHEN ISNULL(ContactV.id, 0)= 0 AND ISNULL(vw.[Contact ID],0) > 0 THEN 'Add'
WHEN ISNULL(ContactV.id, 0)> 0 AND ISNULL(vw.[Contact ID],0) > 0
AND NOT Exists (Select 1 from dbo.ClientVisitTrackingContacts WHERE vw.[Contact ID] = ContactV.[Contact ID] AND visitID = @VisitID ) THEN 'Add'
WHEN ISNULL(vw.[Contact ID],0) = 0 THEN ''
ELSE 'Remove' END
) AS AddRemove,
(
CASE WHEN ISNULL(ContactV.id, 0) = 0
THEN 0
ELSE 1 END
) AS ContactVisitSort,
ContactV.VisitID vid,
@VisitID id
FROM dbo.vw_MarketingVisitationContacts AS vw
LEFT OUTER JOIN
dbo.ClientVisitTrackingContacts AS ContactV ON (vw.[Client ID] = ContactV.[Client ID] AND vw.[Contact ID] = ContactV.[Contact ID])
LEFT OUTER JOIN
dbo.ClientVisitTrackingNearBy ContactVN ON (vw.[Client ID] = COntactVN.nearbyClientID AND VW.[Contact ID] = ContactVN.nearbyContactID)
WHERE (LEN(vw.fullName) > 0) OR (vw.ur = 1)
ASKER
Jared_S
I have it working with nested case statements.
How would I do the "IF" in my select and where?
I have it working with nested case statements.
How would I do the "IF" in my select and where?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys...
Nod to Jared for being first with a solution
Nod to Jared for being first with a solution
So speed not quality. I got it, will remember that next time and avoid wasting my time posting if you already have any kind of answer to your q.
ASKER
Scott,
I in no way meant to offend you.
I've used your answers for years and they are always of the highest quality.
This one time I gave a nod on a complex question to another person because quite frankly, running a shop alone I had to move on.
Again...sorry if I offended. If I get the time one evening I already have a reminder to further investigate your answer.
I in no way meant to offend you.
I've used your answers for years and they are always of the highest quality.
This one time I gave a nod on a complex question to another person because quite frankly, running a shop alone I had to move on.
Again...sorry if I offended. If I get the time one evening I already have a reminder to further investigate your answer.
Either way, you're going to need to modify your criteria since
@vType = 'P'
and
@vType <> 'N'
are not mutually exclusive.
I would use:
IF @vType = 'P'
query1
IF @vType not in ('N','P')
query2