Post reply

need help with case statement in Joins

  • November 14, 2013 at 4:08 pm

    #395830

    declare @Parameter varchar(10)

    I need to have a conditional join based on the parameter value

    If @parameter = 'Yes'

    THEN JOIN CONDITION 1

    else

    then JOIN CONDITION 2

    tried below but didnt work

    (CASE when @parameter = 'Yes' THEN

    (LEFT JOIN dbo.test t ( NOLOCK ) ON a.id = t.id

    aND ISNULL(C.parameter,'') = @parameter )

    ELSE

    (LEFT JOIN dbo.test t ( NOLOCK ) ON a.id = t.id

    AND a.rowid = t.rowid

    AND ISNULL(C.parameter,'') = @parameter )

    END )

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • November 14, 2013 at 4:15 pm

    #1666579

    Here's an approach that would put you in the right direction syntactically.

    LEFT JOIN dbo.test t (NOLOCK)

    ON a.id = t.id

    AND (C.parameter = @parameter OR C.parameter IS NULL)

    AND a.rowid = CASE WHEN @parameter = 'Yes' THEN t.rowid ELSE a.rowid END

    But in terms of performance of this query I strongly recommend you'll have a look at Gails great article (http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) to get a more detailed understanding regarding the possible performance issue you might suffer...

    Edit: I also removed the NOLOCK hint since this might result in duplicate or missing result sets. You'll need to optimize the query and/or the indexing related to it instead of just adding the NOLOCK hint...


    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]

  • November 14, 2013 at 4:26 pm

    #1666584

    Thanks for the response..

    I need to add few more fields in the case statement based on the parameter ..I will test it .

    Thanks

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • November 14, 2013 at 5:13 pm

    #1666588

    Thanks lutz.. It worked like a charm.

    I will look into optimizing it now .

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

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

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