Post reply

Case statement - boolean expression help needed

  • July 1, 2016 at 1:06 pm

    #308835

    Hi All,

    I want to have the distinct values for the column which is satisfied in the case statement and also want to get the condition satisfied for the variable &divisionbox which contains Boolean value but oracle does not accept Booleans. So how can I put the Boolean condition? &regionname, &divisionid, &divisionbox are the variables

    Select CASE WHEN '&REGIONDIVISION' is not null AND ('&DIVISIONID' NOT IN ( 'Non-Technical', 'Technical')) AND '&DIVISIONBOX' = FALSE THEN SEGMENTNAME

    ELSE REGIONNAME END as Segment,

    sUM(spreadamt) AS SPREAD, FROM table a

    GROUP BY CASE WHEN '&REGIONDIVISION' is not null AND('&DIVISIONID' NOT IN ( 'Non-Technical', 'Technical')) THEN SEGMENTNAME

    ELSE REGIONNAME END

    Thank you

  • July 2, 2016 at 10:37 am

    #1888198

    MMITTAL 758 (7/1/2016)

    Hi All,

    I want to have the distinct values for the column which is satisfied in the case statement and also want to get the condition satisfied for the variable &divisionbox which contains Boolean value but oracle does not accept Booleans. So how can I put the Boolean condition? &regionname, &divisionid, &divisionbox are the variables

    Select CASE WHEN '&REGIONDIVISION' is not null AND ('&DIVISIONID' NOT IN ( 'Non-Technical', 'Technical')) AND '&DIVISIONBOX' = FALSE THEN SEGMENTNAME

    ELSE REGIONNAME END as Segment,

    sUM(spreadamt) AS SPREAD, FROM table a

    GROUP BY CASE WHEN '&REGIONDIVISION' is not null AND('&DIVISIONID' NOT IN ( 'Non-Technical', 'Technical')) THEN SEGMENTNAME

    ELSE REGIONNAME END

    Thank you

    You can make your @divisionbox variable any numeric and test for 0 or 1 as false or true. Bit is the best for this obviously as it is 1 bit of storage (although it will take a byte on a column but the engine will store up to 8 of them per single byte of row storage).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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