Post reply

Need Help Writing a CASE Statement???

  • March 8, 2005 at 7:08 am

    #162885

    I have a column in my table (let's call it Col014), and I have some NULL values...along with whole values such as 197, 180 etc...

    How can I write the CASE statment so that whenever Col014 ISNULL then change the value of Col014 to 7.45???

    This is important because I need to sum up the values in this column and need to replace the Null values with 7.45 to make this work...

    Can someone give me a helping hand?

    Thank you!

  • March 8, 2005 at 8:06 am

    #545876

    HI

    This is what u r looking for:

    select isnull(Col014,7.45) from your_table

    hth

    JP

  • March 8, 2005 at 8:19 am

    #545882

    CASE

    WHEN COL014 IS NULL THEN COL014 = 7.45

    .

    .

    may do...

  • March 9, 2005 at 1:19 am

    #546056

    No, that will not do. You cannot assign the output value to the column in the CASE statement.

    the statement : select ISNULL(COL014, 7.45) AS COL014 would work fine. Prefer the flexibility of the COALESCE function which allows you to examine several values successively for NULL.

    HABIB.

     

    Kindest Regards,

    Habib Zmerli (MVP)

  • March 9, 2005 at 4:36 am

    #546088

    Alternatively, if the data column must include a value why not update the column to 7.45 where you have NULLS.  Will make the actual query easier!

  • March 9, 2005 at 7:10 am

    #546144

    UPDATE tblMyTable

    SET Col014 = 7.45

    WHERE Col014 IS NULL

  • March 9, 2005 at 10:59 am

    #546201

    SELECT Col014  = COALESCE(Col014 , 7.45 ) , * FROM tblMyTable

  • March 9, 2005 at 7:43 pm

    #546277

    Once you have all null values updated to 7.45 in the table, you may want to set Col014 to not null and set a default value of 7.45 on the field to prevent any more nulls.

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

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