Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: RESOLVED: Concatenated column returns null

    Hi folks,

    I have an issue with a column I created in my query called Instance.

    Code:
    SELECT        Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + CONVERT(varchar, 
                             Start_date, 103) + ') : ' + Event_type AS Instance
    FROM            Events
    ORDER BY Event_type
    Above is my query. The problem is because the start date column can be null, it also returns the Instance column as null for that row.

    I thought it would have just missed out the date and display the rest, but it doesn't.
    Is there any way I could get the Instance column to display a value, when the start date is null?

    Thanks

    Edit: Managed to sort it using ISNULL()
    Last edited by KevCB226; 03-07-08 at 06:57. Reason: Resolved issue
    <- Hides behind a rock.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i was going to reccomend coalesce.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    You can also use
    Code:
    SET CONCAT_NULL_YIELDS_NULL OFF
    but I'd probably just stick with COALESCE.

    From BOL...
    When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

  4. #4
    Join Date
    Mar 2008
    Location
    Athens, Greede
    Posts
    3
    Hello all, first post.

    Alternatively to COALESCE, you could use ISNULL. The folowing should work:
    Code:
    SELECT        Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + ISNULL(CONVERT(varchar, 
                             Start_date, 103) ,'')+ ') : ' + Event_type AS Instance
    FROM            Events
    ORDER BY Event_type

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personal preference - don't use ISNULL. It means something totally different in other MS languages (therefore confusing), the name of the function suggests something other than its outcome (therefore confusing), COALESCE can do more, COALESCE is ANSI.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Also, Coalesce is a bigger word and makes you look more smarterer when you use it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Also, Coalesce is a bigger word and makes you look more smarterer when you use it.
    Yup - smart people use smart, smarter people use smarter, the smartest people use smarterer.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Also, Coalesce is a bigger word and makes you look more smarterer when you use it.

    Dude, That's pretty lame

    and was smarterer a joke?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2008
    Location
    Athens, Greede
    Posts
    3
    Quote Originally Posted by pootle flump
    Personal preference - don't use ISNULL. It means something totally different in other MS languages (therefore confusing), the name of the function suggests something other than its outcome (therefore confusing), COALESCE can do more, COALESCE is ANSI.
    I agree that COALESCE can do more, but in this particular case ISNULL works equally fine AFAIK. I'm not aware of the context in which the keyword is used in other MS languages (I'd like a few examples, if it's not too much trouble). When you say that the name of the function suggests something different than its outcome, how's that? Finally, I'd like a bit of extra info on the ANSI part.

    Personally, I tend to use COALESCE when I need to check the values in more than one column, or when I want to perform some error handling (see for example this article on the use of COALESCE in conjunction with NULLIF). For a plain old check of null value in a column or variable, my personal (and I repeat: personal) preference is ISNULL.

    Maybe you're right. Maybe COALESCE is better. I just thought I'd show how I'd go about writing that query myself.

    Cheers.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Quote Originally Posted by yiangos
    I agree that COALESCE can do more, but in this particular case ISNULL works equally fine AFAIK.
    It does. However, if you need to do more then you have to use COALESCE, so using it when you do less too makes sense to me. Why switch between functions depending on the number of inputs when the action of the function is pretty well the same?

    Quote Originally Posted by yiangos
    I'm not aware of the context in which the keyword is used in other MS languages (I'd like a few examples, if it's not too much trouble).
    It is is used in VB\ VBA. IsNull() returns a boolean - if the input is null then it returns true. If the input is not null it returns false.

    Quote Originally Posted by yiangos
    When you say that the name of the function suggests something different than its outcome, how's that?
    IMHO any function that is named IsSomething should return true or false. It should test whether or not the input is the Something. IsNumeric does. IsDate does. IsNull does....in VB

    Quote Originally Posted by yiangos
    Finally, I'd like a bit of extra info on the ANSI part.
    American National Standards Institute - there is a SQL Standard. SQL implementations use their own extensions (e.g. TOP, IDENTITY, ISNULL). COALSCE is ANSI standard which means it is more likely to be supported on other platforms making it more portable. ISNULL almost certainly has to be rewritten (unless porting to Access...).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Dude, That's pretty lame

    and was smarterer a joke?
    It was, quite possibly, the lamerest joke ever.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by yiangos
    I'm not aware of the context in which the keyword is used in other MS languages (I'd like a few examples, if it's not too much trouble).
    In Visual Basic for Applications
    Code:
    If IsNull(someValue) Then
    Quote Originally Posted by yiangos
    When you say that the name of the function suggests something different than its outcome, how's that?
    I find that IsNull implies that it is checking for null and returning a boolean result, not "replacing" nulls with another value.
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Echoey in here ain't it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wrote that response very shortly after post 9, but never hit ctrl + S... It sat in a window all on it's lonesome until I noticed it
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2008
    Location
    Athens, Greede
    Posts
    3
    Thanks to both of you.

    About the isSomething and what it --should-- return, you're probably right. I always thought of ISNULL as "IFNULL" (sort of the opposite of NULLIF). Who knows, maybe that's what they **intended** to name it and someone hit the "s" instead of the "f" key

    As regards the ANSI part - I know what ANSI is, and I know that there is a SQL standard (from which almost all SQL products deviate to some degree, to the best of my knowledge). What I didn't know is that COALESCE is actually part of the standard. I thought it was pure Microsoft, as I don't recall seeing it in Oracle or MySQL...

    To be honest, that fact alone (COALESCE being ANSI) will probably make me switch over to your position (I'm a sucker for standards-compliant code, so sue me for changing my mind so easily :P)

    To the original poster:

    Please replace the ISNULL() call with a COALESCE(), same arguments, same order. It **should** run correctly.

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •