Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Complax Case and Select question

Posted on 2013-01-03
7
Medium Priority
?
457 Views
Last Modified: 2013-01-03
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

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38740074
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
 

Author Comment

by:lrbrister
ID: 38740113
Jared_S
I have it working with nested case statements.

How would I do the "IF" in my select and where?
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 1400 total points
ID: 38740151
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 600 total points
ID: 38740430
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
 

Author Closing Comment

by:lrbrister
ID: 38741281
Thanks guys...
Nod to Jared for being first with a solution
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38741355
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
 

Author Comment

by:lrbrister
ID: 38741638
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question