Re: SQL curiosity - not sure why it works, but it does! -- MIDRANGE-L

coalesce(dcexpyears, 0) dcexpyears,
coalesce(dcexpdays, 0) dcexpdays

Vern, notice that you've given the resulting column a name that
happens to match the field you've coalesced on...

Perhaps this would seem clearer...

update docs00 oo
set expdate =
(select case
when dcexpdaysNOTNULL > 0
then chkdate + dcexpdaysNOTNULL days
when dcexpyearsNOTNULL > 0
then chkdate + dcexpyearsNOTNULL years
else
timestamp('0001-01-01-00.00.00.000000') end
from docs00 ii
left outer join
(select distinct
dctype,
coalesce(dcexpyears, 0) AS dcexpyearsNOTNULL,
coalesce(dcexpdays, 0) AS dcexpdaysNONULL
from doctype00) as dt
on doctype2 = dctype
where ii.docid = oo.docid)

HTH,
Charles

On Mon, Nov 1, 2010 at 11:58 AM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

Y'all

I have an UPDATE for a column from a scalar subselect that contains a
CASE, because there are 2 columns, whose values will be used if
non-zero, tested in a certain order. It is also possible for the table
(doctype00) whose values are being used to update the other, it's
possible there is not a match on the JOIN field

The one that works (and I'm not sure why) is this -

update docs00 oo
set expdate =
(select case
when dcexpdays > 0
then chkdate + dcexpdays days
when dcexpyears > 0
then chkdate + dcexpyears years
else
timestamp('0001-01-01-00.00.00.000000') end
from docs00 ii
left outer join
(select distinct
dctype,
coalesce(dcexpyears, 0) dcexpyears,
coalesce(dcexpdays, 0) dcexpdays
from doctype00) as dt
on doctype2 = dctype
where ii.docid = oo.docid)

What seems odd is that putting the COALESCE in the 2nd dial of the LEFT
OUTER JOIN, well, that it actually works. I can kind of bend my brain
around it. I mean, COALESCE is something I think of as accommodating the
NULLs that result from a LEFT OUTER JOIN, not something to put in the
column list of the 2nd dial of that kind of JOIN.

Dan K, here, asked about it, too - he had the same mood-altering
experience with it. So here's a variant that seems more sensible - maybe!

update docs00 oo
set expdate =
(select case
when coalesce(dcexpdays, 0) > 0
then chkdate + coalesce(dcexpdays, 0) days
when coalesce(dcexpyears, 0) > 0
then chkdate + coalesce(dcexpyears, 0) years
else
timestamp('0001-01-01-00.00.00.000000') end
from docs00 ii
left outer join
(select distinct
dctype,
dcexpyears,
dcexpdays
from doctype00) as dt
on doctype2 = dctype
where ii.docid = oo.docid)

Have at it - both work, both get the same result.

Vern
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.