Re: How to get CASE statement to recognize null ?
> On 10 Mar 2021, at 21:00, David Gauthier wrote:
>
> Hey, Thanks Tom !
>
> It's actually a little more complicated than the simple example, so I'm not
> sure it can be shoehorned into coalesce...
>
> CASE
>WHEN sr.nightly_cl_display_suffix is null THEN cast (d.p4_changelist
> as varchar)
> ELSE
>cast (d.p4_changelist as varchar)||'-'||sr.nightly_cl_display_suffix
> END as changelist
Sure it can:
cast(d.p4_changelist as varchar) ||
coalesce('-'||sr.nightly_cl_display_suffix, '')
> On Wed, Mar 10, 2021 at 2:46 PM Tom Lane wrote:
> David Gauthier writes:
> > dvdb=# select
> > CASE col1
> > WHEN null THEN 'z'
> > ELSE col1
> >END as col1,
> >col2
> > from foo;
>
> This test is equivalent to "col1 = null" which will always fail.
> You could try something like
>
> CASE WHEN col1 IS NULL THEN ... ELSE ... END
>
> Although I think the particular thing you're doing here would
> be better solved with COALESCE(col1, 'z').
>
> regards, tom lane
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
Re: How to get CASE statement to recognize null ?
Hey, Thanks Tom ! It's actually a little more complicated than the simple example, so I'm not sure it can be shoehorned into coalesce... CASE WHEN sr.nightly_cl_display_suffix is null THEN cast (d.p4_changelist as varchar) ELSE cast (d.p4_changelist as varchar)||'-'||sr.nightly_cl_display_suffix END as changelist On Wed, Mar 10, 2021 at 2:46 PM Tom Lane wrote: > David Gauthier writes: > > dvdb=# select > > CASE col1 > > WHEN null THEN 'z' > > ELSE col1 > >END as col1, > >col2 > > from foo; > > This test is equivalent to "col1 = null" which will always fail. > You could try something like > > CASE WHEN col1 IS NULL THEN ... ELSE ... END > > Although I think the particular thing you're doing here would > be better solved with COALESCE(col1, 'z'). > > regards, tom lane >
Re: How to get CASE statement to recognize null ?
David Gauthier writes: > dvdb=# select > CASE col1 > WHEN null THEN 'z' > ELSE col1 >END as col1, >col2 > from foo; This test is equivalent to "col1 = null" which will always fail. You could try something like CASE WHEN col1 IS NULL THEN ... ELSE ... END Although I think the particular thing you're doing here would be better solved with COALESCE(col1, 'z'). regards, tom lane
