Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Union With IF Expand / Collapse
Author
Message
Posted Sunday, July 27, 2014 12:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 23, 2014 12:36 AM
Points: 25, Visits: 90
Hi
Post #1596578
Posted Sunday, July 27, 2014 1:22 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 6,842, Visits: 13,364
Instead of

Union
If(@NPI is not null or @TIN IS Not null)
Begin

You'll need to remove the If condition as well as the BEGIN ... END.
Add the following line to the WHERE Condition of each of the threee union statements you want to exclude:
AND ISNULL(@NPI,@TIN) IS NOT NULL

e.g. the first "conditional union" would look like
Where Cl.keyvaluechar IN (
Select Distinct claim_num from dw..claim where svc_provider_npi=@NPI or Payto_Provider_NPI=@NPI
)
AND ISNULL(@NPI,@TIN) IS NOT NULL

But the question is: Why do you need to do the UNION in the first place?
You could run each statement individually as a separate INSERT into #temptable.
If you want to exclude duplicates, you could add
AND NOT EXISTS (SELECT 1 FROM #temptable WHERE [add the check you want to perform])

In that scenario the section
If(@NPI is not null or @TIN IS Not null)
Begin
is valid and then UNION needs to be removed and separa Insert into #temptable need to be added.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1596581
Posted Thursday, August 14, 2014 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 23, 2014 12:36 AM
Points: 25, Visits: 90
Apologies for delay..Much appreciated..thanks
Post #1603378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse