Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL 'Coalesce' qualities - just curious (SqlSrvr)

    This is not a problem -- because I'll go a different route (stored procedure that can use the case statement). So, all you lovely souls out there that help so much, this is just for curiosity, and if anyone has anything to offer, that would be great!

    I was trying to do a(n) SQL pull via a pass-through query in Access, and I found that Access seemed to choke on the CASE statement, so I edited my code to avoid the case statement (a bit of a drill). This was done earlier when I wasn't well versed in creating stored procedures ...

    The field "theRating" is a char field, that I was converting to numeric after pulling data into a local Access table, and wanted to get it into a numeric format in the pull so I wouldn't have to convert it afterwards. "theRating" may or may not be null, otherwise should have numeric values.

    Here is my successful Case statement that I use outside of Access (and I'm using smallmoney just because, I chose that over decimal, float, and money):

    rating_smallmoney = case
    when isnumeric(theRating) = 1 then convert(smallmoney, theRating)
    else 0
    end

    Here is my non-successful attempts to do the same w/o the case statement (and I've pulled the conversion to small money out of these samples, because the error happens just on the coalesce):

    coalesce(theRating,0)
    This delivers "Select error: Syntax error converting the varchar value '1.0' to a column of data type int. Huh?

    Then I try:
    coalesce(theRating,0.0)
    On this one, I don't get any errors, but the only rating that gets converted is the 0.5 -- I get null for 1.0, 2, 2.75, for example (unless it's 0 for theRatings that are null).

    I've looked at the coalesce info in Sql Server Books Online, but can't find any hints.

    (I'm not protecting for a non-convertable char possibility on this one yet with this following, but that's not the issue at the moment <g>).

    Thanks!
    Pat

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: SQL 'Coalesce' qualities - just curious (SqlSrvr)

    If you can create a View in SQL server that uses the Case statement, you should be able to take the SQL string and paste it into a pass-through query in Access and have it process without problems. I don't know why Access would choke on it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL 'Coalesce' qualities - just curious (SqlSrvr)

    Well ....I find out that you were right! I was in too much of a hurry when I was first doing this thing, so didn't trouble-shoot it fully (and, didn't check whether this latest case statement would work, because I assumed it wouldn't ..). Basically, the following case statement works in a different report writer that I am using:
    SeguedCat = case
    when Cat like 'Cr%' then "Crumula"
    else Cat
    end
    But in Access I get an error message, "Invalid column name 'Crumula'. " HOWEVER! If I just change the double-quotes around "Crumula" in Access to single ones, it works like a charm. I guess I was just getting away with sloppier stuff in the other, more forgiving front-end -- and now, I see that I can go back to using the case statement in a pass-through query. Thank you! I'll just be more careful with my quotes.
    Pat

Posting Permissions

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