Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Rotterdam
    Posts
    17

    Unanswered: problem with NULL values in sql statement

    Oke, this is my sql statement i'm using in my acces project


    SeLECT ID_PERS, ACHTERNAAM + ',' + VOORLETTERS + ' ' + TUSSENVOEGSEL AS NAAM FROM dbo.RAR_TBL_PERSONEN WHERE (NOT (VOORLETTERS IS NULL)) ORDER BY ACHTERNAAM + ',' + VOORLETTERS + ' ' + TUSSENVOEGSEL

    problem is that the field "TUSSENVOEGSEL" does not always contain a value (so it is NULL). In this case the query gives an empty field as a result. (so not just de fields VOORNAAM and ACHTERNAAM together)

    Does anybody know how to fix this problem.

    thank you
    Peter Dorsman

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ID_PERS
         , iif(isnull(ACHTERNAAM),'',ACHTERNAAM+', ')
         + iif(isnull(VOORLETTERS),'',VOORLETTERS+' ')
         + iif(isnull(TUSSENVOEGSEL,'',TUSSENVOEGSEL) as NAAM 
      from dbo.RAR_TBL_PERSONEN 
     where not VOORLETTERS is null
    order 
        by NAAM
    maybe i did not do the commas and spaces correctly, but this should give you the right idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    i am a little out of SQL but how about this?

    SeLECT ID_PERS, ACHTERNAAM + ',' + VOORLETTERS + rtrim(' ' + TUSSENVOEGSEL) AS NAAM FROM dbo.RAR_TBL_PERSONEN WHERE (NOT (VOORLETTERS IS NULL)) ORDER BY ACHTERNAAM + ',' + VOORLETTERS + rtrim(' ' + TUSSENVOEGSEL)

    Greets from Amsterdam

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    namliam, rtrim(' ' + TUSSENVOEGSEL) will still be null when TUSSENVOEGSEL is null, and then the whole expression it is concatenated to will be null again

    there's not much you can do except coalesce it to an empty string

    whoops, did i say coalesce

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    Rotterdam
    Posts
    17
    Originally posted by r937
    Code:
    select ID_PERS
         , iif(isnull(ACHTERNAAM),'',ACHTERNAAM+', ')
         + iif(isnull(VOORLETTERS),'',VOORLETTERS+' ')
         + iif(isnull(TUSSENVOEGSEL,'',TUSSENVOEGSEL) as NAAM 
      from dbo.RAR_TBL_PERSONEN 
     where not VOORLETTERS is null
    order 
        by NAAM
    maybe i did not do the commas and spaces correctly, but this should give you the right idea
    When I use this statement, I get an error message, telling me the "isnull" function needs 2 argument.

    What's the second argument ?
    Peter Dorsman

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think (because i've never done it myself) that if you are running an "access project" then you actually have to use sql server syntax

    see the link for coalesce i gave
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Location
    Rotterdam
    Posts
    17

    Talking

    found the sollution: want to know

    SELECT ID_PERS, ACHTERNAAM + RTRIM(' ' + ISNULL(TUSSENVOEGSEL, N'')) + ', ' + VOORNAAM AS NAAM
    FROM dbo.RAR_TBL_PERSONEN
    WHERE (NOT (VOORLETTERS IS NULL))
    ORDER BY NAAM

    thank you all for thinking with me
    Peter Dorsman

Posting Permissions

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