469,642 Members | 1,230 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,642 developers. It's quick & easy.

Help: Complex Select Statement

Here is my SQL string:

"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON
DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON
DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result
of outer select) AS Total" _
& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'
AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnum
DESC"

The outter Select statement returns various amounts of order numbers
represented by 'to_ordnum' in the outer Select clause which has to
meet the critera in the outer WHERE clause. I would like to place
these numbers selected into the inner WHERE clause for the inner
select statement where DOMBOM2.b2_ordnum = ?the order selected by
outer select statement.

I have tried placing to_ordnum into that location but the SQL2000
server does not process it.

Any suggestions, ideas?

Thank you,

Brett
Jul 20 '05 #1
1 4299
On 5 Nov 2004 10:34:48 -0800, brett wrote:
Here is my SQL string:

"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON
DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON
DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result
of outer select) AS Total" _
& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'
AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnum
DESC"

The outter Select statement returns various amounts of order numbers
represented by 'to_ordnum' in the outer Select clause which has to
meet the critera in the outer WHERE clause. I would like to place
these numbers selected into the inner WHERE clause for the inner
select statement where DOMBOM2.b2_ordnum = ?the order selected by
outer select statement.

I have tried placing to_ordnum into that location but the SQL2000
server does not process it.

Any suggestions, ideas?

Thank you,

Brett


God, what a mess you posted. If you have a SQL question, try to get the
SQL separated from the client code that is sending it.

The confusion seems to be because you have DDTORD both in the outer select
and the inner select. You should use a table alias to differentiate them.

Here's one possibility, reformatted so it can be read:

SELECT
TBL1.to_ordnum,
TBL1.to_orddate,
( SELECT
SUM(
( DDPROD.pr_stanmat * DDPROD.pr_prfact)
* ( DOBOM2.b2_quant * DDORD.or_quant)
)
FROM DDPROD
INNER JOIN DOBOM2
ON DDPROD.pr_prodnum = DOBOM2.b2_prodnum
INNER JOIN DDORD
ON DOBOM2.b2_orid = DDORD.or_id
INNER JOIN DDTORD AS TBL2
ON DDORD.or_toid = TBL2.to_id
WHERE DOBOM2.b2_ordnum = TBL1.to_ordnum
) AS Total
FROM DDTORD AS TBL1
WHERE TBL1.to_trak2id IN (39, 40, 41)
AND TBL1.to_ordtype = 's'
AND TBL1.to_status = 'c'
GROUP BY TBL1.to_ordnum, TBL1.to_orddate
ORDER BY TBL1.to_ordnum DESC

However, without seeing DDL and sample inserts, it's impossible to test
whether this is what you meant.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by db2sysc | last post: by
28 posts views Thread by Siv | last post: by
3 posts views Thread by Slower Than You | last post: by
9 posts views Thread by pic078 via AccessMonster.com | last post: by
6 posts views Thread by Jon Bilbao | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.