Post reply

Use of Coalesce function

  • December 12, 2008 at 10:14 am

    #197177

    Hi

    I have a Display field where I wish to have the same data as in another field 'Number' which is of type int. I also want that if the 'Number' field is null then the Display should be populated with '# Not Assigned' . I am using this:

    (coalesce([Number],' Not Assigned'))

    but am not getting the desired result.Can you please help?

    Thanks

    Adish

  • December 12, 2008 at 10:23 am

    #911779

    i believe COALESCE requires all parameters to be the same data type, right?

    Coalesce(int,varchar) will fail.

    this fails:

    select(coalesce([Number],' Not Assigned')) from

    (SELECT 1 AS [Number] UNION

    SELECT 2 UNION

    SELECT NULL UNION

    SELECT 4) X

    Msg 245, Level 16, State 1, Line 1

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

    I think if you convert [Number] to a varchar, or use a case statement based on isnull or not, you'll get your desired results.

    I'd use

    CASE WHEN [Number] IS NULL

    THEN ' Not Assigned'

    ELSE CONVERT(VARCHAR,NUMBER)

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 12, 2008 at 10:38 am

    #911799

    Thanks for the prompt reply, Lowell.

    I am able to do it with 'IsNull', but not with 'Coalesce'.I even tried:

    (coalesce(CONVERT([nvarchar](20),[Number],0),'# Not Assigned'))

    but does not work.

    Thanks

    Adish

  • December 12, 2008 at 11:03 am

    #911819

    What is the error that you get? And can you show us the whole statement that fails and the data type that you are trying to assign it to?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 13, 2008 at 1:08 am

    #912011

    Hi Guys

    It looks like there was some old data in the table which made it appear that the display was not correct.However, the code is working fine. But there is still something which surprises me. If I write 'Not Assigned' or ' Not Assigned' or ' Not Assigned', I get the display as 'Not Assigned'. Can you explain what happens to the spaces and what can be done if the spaces are desired?

    Thanks for the help, guys.

    Adish

    BTW, if I did not make myself clear as to what I was doing-- I was trying to populate a column 'Display' as a computed column in the SQL Server Management Studio with different values based on whether a preferred column was null or not.

  • December 13, 2008 at 8:28 am

    #912046

    You would need to show us the expression that you are currently using and the datatypes of the columns involved.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 13, 2008 at 9:02 am

    #912054

    Hi

    The column Display is a computed column

    The column Number is int

    The expr used in the column properties for Computed Column Specification is:

    (coalesce(CONVERT([nvarchar](20),[Number],0),'Not Assigned'))

    or

    (coalesce(CONVERT([nvarchar](20),[Number],0),' Not Assigned'))

    or

    (coalesce(CONVERT([nvarchar](20),[Number],0),' Not Assigned'))

    But the result is either the value in the column Number or 'Not Assigned' if the Number column is null.

    However, I had expected the result to be ' Not Assigned' or ' Not Assigned'

    I wonder what happened to the spaces?

    Thanks for the help

    Adish

  • December 13, 2008 at 9:42 am

    #912067

    Hmm, well that is not happening to me. Is it possible that you have set the "persisted" option on the computed column and you are picking up the previous "Not Assinged" values?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

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

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