× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Ok I missed the closing paren, was originally thinking the the "simple
subselect" was a nested table expression with a correlated
reference...

left outer join
(select distinct
dctype,
coalesce(dcexpyears, 0) dcexpyears,
coalesce(dcexpdays, 0) dcexpdays
from doctype 00
) as dt
on doctype2 = dctype
where ii.docid = oo.docid)

While it is a nested table expression, as opposed to a "simple
subselect"...there's no correlated reference....

I'd agree, it seems strange that it works...would be interesting to
see what IBM would say about it.

Charles

On Mon, Nov 1, 2010 at 6:05 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:
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.


--
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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.