SQLite Forum

Using coalesce in where clause
Login

Using coalesce in where clause

(1) By anonymous on 2020-06-19 15:31:40 [link] [source]

I was trying to do something like
a. is base data, b are updates

<snip>
select coalesce(b.col1, a.col1), coalesce (b.col1, a.col1) as var, a.col3
from a
left join b
on coalsece (a.col, 'X') = coalesce (b.col1, 'X') -- could be null
and a.col3 = b.col3

-- up to here thare are no problems and the results are correct and I have many rows with var = 7

where coalesce(b.col1, a.col1) = 7

-- I get no results whatever
<snip>

The question is:  Can I use coalesce in the where clause or whatI an doing is too confusing to SQLite.  I yhink that this would work on SQL Server
ZA

(2) By David Raymond (dvdraymond) on 2020-06-19 16:06:14 in reply to 1 [link] [source]

You can use coalesce anywhere, including the where clause, yes.

So there's something going on. First question is to ask when you say you have many rows with var = 7, are you sure that those are the number 7, or could they be the single character text string of '7'?

For the ON clause, if you're just looking to make it so nulls on either side match each other, you can use IS. That way if they're both null it'll still return true.
...
on a.col1 IS b.col1
...

(3) By anonymous on 2020-06-19 17:24:17 in reply to 2 [link] [source]

Thank you for teaching me about 'is'. It is by far less cumbersome.

it is indeed defined as char, but trying '7' did not help

(4) By Stephan Beal (stephan) on 2020-06-19 17:35:14 in reply to 3 [link] [source]

it is indeed defined as char, but trying '7' did not help

Note that "defined as" (in the column declaration) means next to nothing in sqlite. sqlite will happily store any type in any column, and may store different types for the same column of different rows. What's significant is whether the data are actually saved as numbers or strings, not whether they're declared as such. See the "manifest typing" section of this page for details.

(5) By anonymous on 2020-06-19 21:51:51 in reply to 4 [link] [source]

I tried [= 7] and [= '7'] both failed miserably

(6) By Keith Medcalf (kmedcalf) on 2020-06-19 22:23:35 in reply to 5 [link] [source]

What version?

What does is the VDBE code (explain <statement> or .eqp full)?

(7) By Richard Hipp (drh) on 2020-06-19 23:09:59 in reply to 5 [link] [source]

Can you please post a test case that demonstrates your problem?

(8) By anonymous on 2020-06-20 09:28:16 in reply to 4 [source]

Note that "defined as" (in the column declaration) means next to nothing in sqlite

This is not true. I often had very strange problems when intuitively declaring a column as "STRING" instead of "TEXT". See e.g. this thread.