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

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)

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
The IF just precedes the query block, so:

IF @vType = 'P'
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)
IF @vType NOT IN ('N','P')
... the second version of the query

If you had multiple statements in the same query, you would want to use Begin and End:
IF...
Begin
<query>
<query>
End

It MIGHT give you a slight performance boost, I'm not sure. It would be easy enough to try it and see. Otherwise, if it ain't broke...
0
 
Jared_SCommented:
You could do this with nested cases, but an IF statement would make it much easier to read.

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
0
 
Larry Bristersr. DeveloperAuthor Commented:
Jared_S
I have it working with nested case statements.

How would I do the "IF" in my select and where?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Scott PletcherConnect With a Mentor Senior DBACommented:
I would just change the LEFT JOIN conditions so that you don't have to maintain two different versions of the query, something like below.  (I really don't performance should be hurt here -- if it is dramatically worse, you might be forced to use two different queries):



Select                   vw.Pending,
                  (      
                        CASE WHEN ISNULL(ContactV.id, ContactVN.id, 0)= 0 AND ISNULL(vw.[Contact ID],0) > 0 THEN 'Add'
                        WHEN ISNULL(ContactV.id, ContactVN.id, 0)> 0 AND ISNULL(vw.[Contact ID],0) > 0
                                    AND NOT Exists (Select 1 from dbo.ClientVisitTrackingContacts WHERE vw.[Contact ID] = ISNULL(ContactV.[Contact ID], ContactVN.[ContactID]) AND visitID = @VisitID ) THEN 'Add'
                        WHEN ISNULL(vw.[Contact ID],0) = 0 THEN ''
                        ELSE 'Remove' END
                  ) AS AddRemove,
                  (
                        CASE WHEN ISNULL(ContactV.id, ContactVN.id, 0) = 0
                        THEN 0
                        ELSE 1 END
                  ) AS ContactVisitSort,
                  ISNULL(ContactV.VisitID, ContactVN.VisitID) vid,
                  @VisitID id
      FROM      dbo.vw_MarketingVisitationContacts AS vw
      LEFT OUTER JOIN
                   dbo.ClientVisitTrackingContacts AS ContactV ON @vType = 'P' AND (vw.[Client ID] = ContactV.[Client ID] AND vw.[Contact ID] = ContactV.[Contact ID])
      LEFT OUTER JOIN
                  dbo.ClientVisitTrackingNearBy ContactVN ON @vType NOT IN ('N', 'P') AND (vw.[Client ID] = COntactVN.nearbyClientID AND VW.[Contact ID] = ContactVN.nearbyContactID)
      WHERE            (LEN(vw.fullName) > 0) OR (vw.ur = 1)
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks guys...
Nod to Jared for being first with a solution
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Larry Bristersr. DeveloperAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.