Post reply

Union With IF

  • July 27, 2014 at 12:01 am

    #148664

    Hi

  • July 27, 2014 at 1:22 am

    #1732635

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 14, 2014 at 10:14 am

    #1738129

    Apologies for delay..Much appreciated..thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply