Post reply

Check if field is null, if yes, use this field - multiple fields...

  • September 5, 2016 at 5:08 am

    #317062

    Hi,

    Please can someone help me with the syntax of an expression I'm struggling with.

    I have 5 fields in a table, I would like to return the value of a field, if it's null then field 2, if this is null then field 3....going up to the 5 fields and a zero if they are all null.

    I've tried the following:

    COALESCE(Field1, Field2, Field3, Field4, Field5)

    I'm getting the following error:

    Conversion failed when converting the varchar value '500.00' to data type int.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Please could someone point out where I am going wrong or if there is a better way to do it.

    Many thanks

  • September 5, 2016 at 5:16 am

    #1899182

    Coalesce is how you do this. You're getting the error because the data types are different, SQL's trying to do implicit conversions and fails. All the entrie in Coalesce must be the same data type. Pick the data type you want returned and explicitly convert any columns that don't match to that data type.

    Something like

    COALESCE(CAST(Field1 AS ...), CAST(Field2 AS ...), ...)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 5, 2016 at 5:27 am

    #1899184

    Perfect, thank you Gila.

    I was just missing the CAST clauses.

    Thank you.

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

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