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,

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