Post reply

Trying to put NULL in the IN Clause

  • March 22, 2011 at 8:55 am

    #234509

    I have a query the uses an IN clause:

    SELECT SUM(CASE WHEN jobtran.ind_code IN

    ('CLN', 'SAF', 'SAT', 'SS', 'SUN', 'TA', 'TCR', 'TRN', 'WHK')

    THEN a_hrs ELSE 0 END) AHours from IndCode

    The a_hrs column has NULL values that I would like to put into this query. Is there a way to do this? The table is part of a proprietary system, so I can not replace the NULLs with a value without blowing things up.

    Thanks.

  • March 22, 2011 at 9:03 am

    #1301923

    What problems are you getting? SUM will ignore the null in the total and you'll get accurate data.

  • March 22, 2011 at 9:04 am

    #1301924

    I'm not sure what you mean. Please will you provide some table DDL, sample data and expected results so that we can visualise it.

    Thanks

    John

  • March 22, 2011 at 9:32 am

    #1301940

    Sorry, I should have done this first. Here is the code to create the data:

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#jobtran','U') IS NOT NULL DROP TABLE #jobtran

    GO

    --===== Create the test table

    CREATE TABLE #jobtran

    (

    IndCodevarchar(10),

    A_Hours decimal(21,8)

    )

    --===== Insert the test data into the test table

    INSERT INTO #jobtran

    (IndCode, A_Hours)

    SELECT 'CLN', 1 UNION ALL

    SELECT 'SAF', 3 UNION ALL

    SELECT 'SAT', 2 UNION ALL

    SELECT 'SS', 1 UNION ALL

    SELECT 'SUN', 5 UNION ALL

    SELECT 'TA', 3 UNION ALL

    SELECT 'TCR', 2 UNION ALL

    SELECT 'TRN', 2 UNION ALL

    SELECT 'WHK', 1

    INSERT INTO #jobtran

    (A_Hours)

    SELECT 7

    --==== SELECT the records

    SELECT * FROM #jobtran

    --==== The desired End Result

    SELECT SUM(CASE WHEN IndCode IN

    (NULL, 'CLN', 'SAF', 'SAT', 'SS', 'SUN', 'TA', 'TCR', 'TRN', 'WHK')

    THEN A_Hours ELSE 0 END) AHours from #jobtran

    Doing it this way, I get a total of 20. What I am looking for is a total of 27, the total of ALL rows.

    I know that putting the NULL in the IN clause like this is incorrect. I am trying to show what I am needing.

    If need anything else from me, please let me know.

    Thanks.

    Steve

  • March 22, 2011 at 9:36 am

    #1301942

    SELECT SUM(CASE WHEN IndCode IN ( 'CLN', 'SAF', 'SAT', 'SS', 'SUN', 'TA',

    'TCR', 'TRN', 'WHK' ) THEN A_Hours

    WHEN IndCode IS NULL THEN A_Hours

    ELSE 0

    END) AHours

    FROM #jobtran

  • March 22, 2011 at 9:42 am

    #1301943

    Or a bit more clean, I think:

    SELECT SUM(A_Hours)

    FROM #jobtran

    WHERE IndCode IN ( 'CLN', 'SAF', 'SAT', 'SS', 'SUN', 'TA', 'TCR', 'TRN',

    'WHK' )

    OR IndCode IS NULL

  • March 22, 2011 at 9:56 am

    #1301960

    Thank you, Pam! The last one works perfectly!

    Steve

Viewing 7 posts - 1 through 6 (of 6 total)

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