Scope problem? UDB DB2 R8.1.5 Linux

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 29 Jun 2005 02:38 GMT

Apparently I have a scope problem wrt t1.start_date in the second sub
select's sub select. The message is:

S0002--[IBM][CLI Driver][DB2/LINUX] SQL0204N "T1.START_DATE" is an
undefined name. SQLSTATE=42704

The query is:

SELECT t1.*,
start_date + (heat_day-1) days AS heat_date_proj,
start_date + (flush_day-1) days AS recov_date_proj,
t3.datex
FROM $schema.flushes t1
JOIN $schema.flush_protocols t2 ON t1.protocol_id=t2.protocol_i
AND t1.controller=t2.controller
LEFT OUTER JOIN
( SELECT * FROM $schema.heat_obs
WHERE t1.donor_bhid=bhid
AND datex =
( SELECT max(datex) FROM $schema.heat_obs
WHERE t1.donor_bhid=bhid
AND datex BETWEEN t1.start_date
AND t1.start_date + 18 days
)
) t3 ON t1.donor_bhid=t3.bhid
WHERE donor_bhid=$bhid
AND start_date='$start_date'";

I thought (obviously erroneously) that the data from any enclosing
select was referencable within a sub select. What is the correct form
for this? Something involving WITH?

Reply to this Message

fred.sobotka@gmail.com - 29 Jun 2005 07:29 GMT

I think this is closer to what you want. Since you only wanted one
column back from t3 and you were willing to accept a NULL datex (as
evidenced by the LEFT OUTER JOIN in your query), you can get the
desired result from a nested SELECT:

SELECT
t1.*
, start_date + (heat_day - 1) DAYS AS heat_date_proj
, start_date + (flush_day - 1) DAYS AS recov_date_proj
, (SELECT MAX(datex) FROM $schema.heat_obs t3
    WHERE t3.bhid = t1.donor_bhid
    AND t3.datex BETWEEN t1.start_date AND
        t1.start_date + 18 DAYS
) AS datex
FROM $schema.flushes t1
INNER JOIN $schema.flush_protocols t2
    ON t1.protocol_id = t2.protocol_id
    AND t1.controller = t2.controller
WHERE t1.donor_bhid = $bhid
AND t1.start_date = '$start_date'
;

There may be syntax errors, but I hope this is of some help to you.

Fred Sobotka
FRS Consulting, Inc
http://www.frsconsulting.com

Reply to this Message

Bob Stearns - 29 Jun 2005 16:27 GMT

> I think this is closer to what you want. Since you only wanted one
> column back from t3 and you were willing to accept a NULL datex (as
[quoted text clipped - 23 lines]
> FRS Consulting, Inc
> http://www.frsconsulting.com

That would work, but this is just my most simple example; I usually want
multiple columns from the selected row, if there is one. A later poster
(Tonkuma) pointed me to a more general solution, the keyword TABLE.

Reply to this Message

Tonkuma - 29 Jun 2005 12:03 GMT

"TABLE" keyword will resolve your problem.

SELECT t1.*,
start_date + (heat_day-1) days AS heat_date_proj,
start_date + (flush_day-1) days AS recov_date_proj,
t3.datex
FROM $schema.flushes t1
JOIN $schema.flush_protocols t2 ON t1.protocol_id=t2.protocol_id
AND t1.controller=t2.controller
LEFT OUTER JOIN
TABLE
( SELECT * FROM $schema.heat_obs
WHERE t1.donor_bhid=bhid
AND datex =
( SELECT max(datex) FROM $schema.heat_obs
WHERE t1.donor_bhid=bhid
AND datex BETWEEN t1.start_date
AND t1.start_date + 18 days
)
) t3 ON t1.donor_bhid=t3.bhid
WHERE donor_bhid=$bhid
AND start_date='$start_date'";

Reply to this Message

Bob Stearns - 29 Jun 2005 16:24 GMT

> "TABLE" keyword will resolve your problem.
>
[quoted text clipped - 18 lines]
> WHERE donor_bhid=$bhid
> AND start_date='$start_date'";

Thank you. That is just what I needed.

Is there some documentation of this keyword (as opposed to the function
by the same name) other than the one or two "must" references in Vol. 1
of the SQL Reference Manual?

Reply to this Message

Tonkuma - 30 Jun 2005 02:52 GMT

I don't know other documentation than SQL Reference.
SQL Reference Volume 1 ---> Chapter4. Queries ---> Subselect --->
Table-reference ---> Correlated reference in Table-reference

Reply to this Message

Serge Rielau - 30 Jun 2005 11:41 GMT

> I don't know other documentation than SQL Reference.
> SQL Reference Volume 1 ---> Chapter4. Queries ---> Subselect --->
> Table-reference ---> Correlated reference in Table-reference

The SQL standard keyword is LATERAL. It is tolerated in DB2 UDB V8.2.
Googling for SQL LATERAL yields a bit more information.

Here is a longer blurb on on the lore of TABLE and LATERAL as it has
been handed down to me:
http://www.webservertalk.com/archive220-2004-6-268771.html

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message