By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

Scope problem? UDB DB2 R8.1.5 Linux

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
"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'";

Nov 12 '05 #3

P: n/a
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?
Nov 12 '05 #4

P: n/a
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.
Nov 12 '05 #5

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

Nov 12 '05 #6

P: n/a
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
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.