Yeah, it seems to me that the coalesce on dcexpyears or dcexpdays should only be invoked if theres a null in the column on a matched record in doctype00. I don't understand how the coalesce is invoked when there isn't a matched record.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Monday, November 01, 2010 10:59 AM
To: Midrange Systems Technical Discussion
Subject: SQL curiosity - not sure why it works, but it does!
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.
midrange.com
