pdbernier
asked on
Help with correlated sub query in select list
I am struggling to make this query below work and am looking for help.
The query is running against a Pervasive 2000i database.
SELECT
B.BKIC_PROD_CODE Part_Num,
B.BKIC_PROD_DESC Description,
W.MTWO_WIP_COMQTY COMQTY,
W.MTWO_WIP_SQTY SQTY,
W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
W.MTWO_WIP_ELABOR ELABOR,
W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
(SELECT SUM(WO.MTWORO_ESTHRS)
FROM
WORKOUTOLD WO
WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
W.MTWO_WIP_AFIN AFINISH,
W.MTWO_WIP_WOPRE WOPRE
FROM
BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WI P_CODE
WHERE
W.MTWO_WIP_STATUS='C' AND
W.MTWO_WIP_AFIN>='2003-03- 01' AND
W.MTWO_WIP_AFIN<='2006-03- 29' AND
B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
B.BKIC_PROD_CLASS,
B.BKIC_PROD_CODE
The query runs fine when I remove the subquery in the select list.
Any thoughts as to what I have wrong?
The query is running against a Pervasive 2000i database.
SELECT
B.BKIC_PROD_CODE Part_Num,
B.BKIC_PROD_DESC Description,
W.MTWO_WIP_COMQTY COMQTY,
W.MTWO_WIP_SQTY SQTY,
W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
W.MTWO_WIP_ELABOR ELABOR,
W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
(SELECT SUM(WO.MTWORO_ESTHRS)
FROM
WORKOUTOLD WO
WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
W.MTWO_WIP_AFIN AFINISH,
W.MTWO_WIP_WOPRE WOPRE
FROM
BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WI
WHERE
W.MTWO_WIP_STATUS='C' AND
W.MTWO_WIP_AFIN>='2003-03-
W.MTWO_WIP_AFIN<='2006-03-
B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
B.BKIC_PROD_CLASS,
B.BKIC_PROD_CODE
The query runs fine when I remove the subquery in the select list.
Any thoughts as to what I have wrong?
ASKER
The message I get from the Pervasive SQL Editor is
Some error(s) encountered while executing SQL statement(script).
Not very helpful.
Some error(s) encountered while executing SQL statement(script).
Not very helpful.
you can't have SELECT statement in the select vaules
use the following.
SELECT
B.BKIC_PROD_CODE Part_Num,
B.BKIC_PROD_DESC Description,
W.MTWO_WIP_COMQTY COMQTY,
W.MTWO_WIP_SQTY SQTY,
W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
W.MTWO_WIP_ELABOR ELABOR,
W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
SUM(WO.MTWORO_ESTHRS)
FROM
WORKOUTOLD WO
WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
W.MTWO_WIP_AFIN AFINISH,
W.MTWO_WIP_WOPRE WOPRE
FROM
BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WI P_CODE
WHERE
W.MTWO_WIP_STATUS='C' AND
W.MTWO_WIP_AFIN>='2003-03- 01' AND
W.MTWO_WIP_AFIN<='2006-03- 29' AND
B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
B.BKIC_PROD_CLASS,
B.BKIC_PROD_CODE
use the following.
SELECT
B.BKIC_PROD_CODE Part_Num,
B.BKIC_PROD_DESC Description,
W.MTWO_WIP_COMQTY COMQTY,
W.MTWO_WIP_SQTY SQTY,
W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
W.MTWO_WIP_ELABOR ELABOR,
W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
SUM(WO.MTWORO_ESTHRS)
FROM
WORKOUTOLD WO
WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
W.MTWO_WIP_AFIN AFINISH,
W.MTWO_WIP_WOPRE WOPRE
FROM
BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WI
WHERE
W.MTWO_WIP_STATUS='C' AND
W.MTWO_WIP_AFIN>='2003-03-
W.MTWO_WIP_AFIN<='2006-03-
B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
B.BKIC_PROD_CLASS,
B.BKIC_PROD_CODE
you can re-write it to
SELECT
B.BKIC_PROD_CODE Part_Num,
B.BKIC_PROD_DESC Description,
W.MTWO_WIP_COMQTY COMQTY,
W.MTWO_WIP_SQTY SQTY,
W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
W.MTWO_WIP_ELABOR ELABOR,
W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
WO.tot_estimated_hours,
W.MTWO_WIP_AFIN AFINISH,
W.MTWO_WIP_WOPRE WOPRE
FROM
BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WI P_CODE
left outer join
(
SELECT SUM(MTWORO_ESTHRS) as tot_estimated_hours, MTWORO_WOPRE, MTWORO_WOSUF
FROM WORKOUTOLD
group by MTWORO_WOPRE, MTWORO_WOSUF
) as WO on
WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF
WHERE
W.MTWO_WIP_STATUS='C' AND
W.MTWO_WIP_AFIN>='2003-03- 01' AND
W.MTWO_WIP_AFIN<='2006-03- 29' AND
B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
B.BKIC_PROD_CLASS,
B.BKIC_PROD_CODE
OR, you could just try assigning a column name to the subquery, which your editor may require, your query looks like it would run in MS SQL Query Analyser. I don't know the datatype of MTWORO_ESTHRS but I assume you tested the sub-query separately and it is bringing back the correct summed value.
'tot_estimated_hours' = (SELECT SUM(WO.MTWORO_ESTHRS)
FROM
WORKOUTOLD WO
WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
Tom
SELECT
B.BKIC_PROD_CODE Part_Num,
B.BKIC_PROD_DESC Description,
W.MTWO_WIP_COMQTY COMQTY,
W.MTWO_WIP_SQTY SQTY,
W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
W.MTWO_WIP_ELABOR ELABOR,
W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
WO.tot_estimated_hours,
W.MTWO_WIP_AFIN AFINISH,
W.MTWO_WIP_WOPRE WOPRE
FROM
BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WI
left outer join
(
SELECT SUM(MTWORO_ESTHRS) as tot_estimated_hours, MTWORO_WOPRE, MTWORO_WOSUF
FROM WORKOUTOLD
group by MTWORO_WOPRE, MTWORO_WOSUF
) as WO on
WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF
WHERE
W.MTWO_WIP_STATUS='C' AND
W.MTWO_WIP_AFIN>='2003-03-
W.MTWO_WIP_AFIN<='2006-03-
B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
B.BKIC_PROD_CLASS,
B.BKIC_PROD_CODE
OR, you could just try assigning a column name to the subquery, which your editor may require, your query looks like it would run in MS SQL Query Analyser. I don't know the datatype of MTWORO_ESTHRS but I assume you tested the sub-query separately and it is bringing back the correct summed value.
'tot_estimated_hours' = (SELECT SUM(WO.MTWORO_ESTHRS)
FROM
WORKOUTOLD WO
WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
Tom
ASKER
Tom,
I've tried both of your suggestions, but still no luck. I am wondering if Pervasive 2000i cannot handle a subquery like this.
I've tried both of your suggestions, but still no luck. I am wondering if Pervasive 2000i cannot handle a subquery like this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That query does run, but the entire query does not.
Thanks for the help.
Thanks for the help.
what's the error message, resp problem result?