Post reply

Case Statement in where

  • December 15, 2021 at 1:54 pm

    #3963902

    Hi

    Is it possible to do like this

    @ItemGroup is a parameter

    Select Itemcode,Itemname,ItemGroup

    from tbl1

    Case when @ItemGroup is null then

    where itemgroup is null

    else

    where itemgroup is not null

    end

    Thanks

  • December 15, 2021 at 2:06 pm

    #3963907

    Maybe like this

    if @ItemGroup is null
        Select Itemcode,Itemname,ItemGroup
        from tbl1
        where itemgroup is null;
    else
        Select Itemcode,Itemname,ItemGroup
        from tbl1
        where itemgroup is not null;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • December 15, 2021 at 2:19 pm

    #3963909

    Hi Steve

    Can't we use with where clause only

    Thanks

  • December 15, 2021 at 2:27 pm

    #3963910

    This, maybe

    Select Itemcode,Itemname,ItemGroup
        from tbl1
        where (itemgroup is null and @ItemGroup is null) or (itemgroup is not null and @ItemGroup is not null);

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • December 15, 2021 at 4:53 pm

    #3963992

    My understanding, it won't work QUITE like that.  BUT what you could do is something like:

    SELECT Itemcode,Itemname,ItemGroup
    FROM tbl1
    WHERE (itemgroup IS NULL and @ItemGroup IS NULL) 
      OR (itemgroup IS NOT NULL and @ItemGroup IS NOT NULL)

    Same basic logic without the CASE statement.  You can prove this with a truth table pretty easily.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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