Help: Complex Select Statement

[brett]

Here is my SQL string:

"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_ stanmat * DDPROD.pr_prfac t) *
(DOBOM2.b2_quan t * DDORD.or_quant) ) FROM DDPROD INNER JOIN DOBOM2 ON
DDPROD.pr_prodn um = DOBOM2.b2_prodn um INNER JOIN DDORD ON
DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
DDTORD.TO_ID WHERE DOBOM2.b2_ordnu m = ''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_ordn um = ?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

[Ross Presser]

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_prfac t) *
(DOBOM2.b2_quan t * DDORD.or_quant) ) FROM DDPROD INNER JOIN DOBOM2 ON
DDPROD.pr_prodn um = DOBOM2.b2_prodn um INNER JOIN DDORD ON
DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
DDTORD.TO_ID WHERE DOBOM2.b2_ordnu m = ''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_ordn um = ?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_stanm at * DDPROD.pr_prfac t)
* ( DOBOM2.b2_quant * DDORD.or_quant)
)
FROM DDPROD
INNER JOIN DOBOM2
ON DDPROD.pr_prodn um = DOBOM2.b2_prodn um
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_ordnu m = 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.