Post reply

Need help on improving a where clause, thanks.

  • December 16, 2013 at 9:14 am

    #287485

    Hello all,

    I have an existing select query with where clause as:

    and g.ID in

    (Select ID From Groups where GroupName = 'SVP - LATAM'

    Union

    Select ID From Groups where Sub = 1 )

    I need to add another condition here:

    If RoleID= 5 then the existing filter is ignored otherwise include the existing filter, so it is something like:

    and (Case @RoleID <> 5 then g.ID in

    (Select ID From Groups where GroupName = 'SVP - LATAM'

    Union

    Select ID From Groups where Sub = 1 )

    Thanks.

  • December 16, 2013 at 9:23 am

    #1673753

    I think you'd just extend the WHERE statements in the query to also compare the @RoleId :

    how about this?

    and g.ID in

    (Select ID From Groups

    where GroupName = 'SVP - LATAM'

    AND @RoleID <> 5

    Union

    Select ID From Groups

    where Sub = 1

    AND @RoleID <> 5 )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 16, 2013 at 9:24 am

    #1673754

    There are several ways of doing that.

    and ( @RoleID = 5

    OR g.ID in

    (Select ID From Groups where GroupName = 'SVP - LATAM'

    Union ALL

    Select ID From Groups where Sub = 1 )

    )

    OR

    and ( g.ID in

    (Select ID From Groups where GroupName = 'SVP - LATAM'

    Union ALL

    Select ID From Groups where Sub = 1

    Union ALL

    SELECT g.ID

    )

    )

    Or you could use dynamic code. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 16, 2013 at 9:33 am

    #1673765

    Thanks guys, I was thinking it too complex. :w00t:

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

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