Scope problem? UDB DB2 R8.1.5 Linux

[Bob Stearns]

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?

[fred.sobotka]

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

[Tonkuma]

"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'";

[Bob Stearns]

Tonkuma wrote:

"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'";

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?

[Bob Stearns]

fr**********@gmail.com wrote:

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

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.

[Tonkuma]

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

[Serge Rielau]

Tonkuma wrote:

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/archive...-6-268771.html

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab