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

Actually using the same name doesn't bother me at all - just like "counting flowers on the wall..." - sorry - old song, old memories!

I actually use the same name in the alias as the original column fairly often - it gives me something consistent in naming when it doesn't need differentiation. Here I just want to work with a field of the name that is already in the file. Pays your money, etc.

The thing that appears odd is that the coalesce() function is in a simple subselect - I've always used that function in the outside field list of a left outer join, not the inside field list of one of the join "files" (subselect here) that itself is not a left outer join.

Hope that explains what seems odd. Again, the coalesce() in there seems, on first glance, that it's not important, since THAT innermost subselect will never, on its own, have a NULL in those columns - they all have true values. In other words, if you take that innermost subselect out to live on its own, the COALESCE has no purpose.

But it appears that the COALESCE does take effect, when there is a non-matching record in the first join file - docs00 - and that as implemented, the expression is applied to the NULL result of the non-match. I can only hope that that behavior is intentional - that it won't break in a later release.

That is why the 2nd version is probably safer!!

This just seemed kind of fun to throw at y'all!

Vern

On 11/1/2010 1:15 PM, Charles Wilt wrote:

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.