SQL ‘Coalesce’ qualities – just curious (SqlSrvr)

Topic

New Reply

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 ).

Thanks!
Pat

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote