Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Those darn nulls

    SELECT title_id,advance,
    CASE advance
    WHEN advance ISNULL THEN '0'
    -- i tried is null also and keep getting error
    ELSE advance
    END as 'How much to I advance'
    FROM titles

    Gives me this error:

    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near 'ISNULL'.

    WHen i use just null i get null in my result set where i want 0

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "ISNULL" is an MS Access function. It is not correct TSQL Syntax.
    Your CASE syntax is incorrect as well.
    Try this:
    Code:
    SELECT title_id,advance,
    CASE WHEN advance IS NULL THEN '0' 
        ELSE advance
        END as 'How much to I advance'
    FROM titles
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    "ISNULL" is an MS Access function. It is not correct TSQL Syntax.
    Your CASE syntax is incorrect as well.
    Try this:
    Code:
    SELECT title_id,advance,
    CASE WHEN advance IS NULL THEN '0' 
        ELSE advance
        END as 'How much to I advance'
    FROM titles
    You tired blindman? ISNULL() is T-SQL too - just not a test for nulls. And is that not a bit verbose?
    Code:
    SELECT COALESCE(advance, '0') AS [How much to I advance]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are no longer a lightweight, todd

    pootle, you are a beacon of sanity, the voice of reason in the pursuit of simplicity

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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. I am tired. Dealing with an extremely loose and denormalized security schema on my project. But in my defense ISNULL was not correct TSQL syntax in the manner the poster used, and the poster needed to know why his CASE logic was failing.

    ...and Rudy, now you are just feeding Pootle new signatures.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, yeah

    the one he has makes me look like an idiot
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    the one he has makes me look like an idiot
    I dunno about the "makes me look like bit" but the rest is ok
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    I dunno about the "makes me look like bit" but the rest is ok
    "the one he has an idiot"??? That makes no sense at all, Pootle.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ANSI POOTS!!!!
    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.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    ANSI POOTS!!!!
    I'm never going to have to struggle thinking up a sig again
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    "the one he has an idiot"??? That makes no sense at all, Pootle.
    Yeah - I thought that at the time. Hoped no one would notice....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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