JDCam
asked on
Oracle - Subselect query syntax
Experts,
I am trying to add a subselect to an existing query and struggling with the syntax. The subquery is calling a different table.
This is the original query
Can anyone help please
I am trying to add a subselect to an existing query and struggling with the syntax. The subquery is calling a different table.
This is the original query
Select
INVT_LEV1,
SUM(ON_HAND_QTY-ON_ORD_QTY-HOLD_NON_SHIP_QTY)Avail,
SUM(ON_HAND_QTY)OnHand,
to_Char(sysdate,'YYYY-MM-DD HH24:MI:SS')DataDate
FROM C_INVT
Where Comp_code = 'A1' and cust_code = 'SGF'
GROUP BY INVT_LEV1
This is my attempt at adding the subquery to table C_ORD_PEND. it would return a value called PENDSelect
I.INVT_LEV1,
(SELECT SUM(ORD_PEND_QTY) as PEND
from C_ORD_PEND P
WHERE P.COMP_CODE = I.COMP_CODE and P.CUST_CODE = I.CUST_CODE and P.INVT_LEV1 = I.INVT_LEV1 and P.ORD_LINE_TP = 'P')Subq2,
SUM(I.ON_HAND_QTY - I.ON_ORD_QTY - I.HOLD_NON_SHIP_QTY )Avail,
SUM(I.ON_HAND_QTY)OnHand,
to_Char(sysdate,'YYYY-MM-DD HH24:MI:SS')DataDate
FROM C_INVT I
Where I.Comp_code = 'A1' and I.cust_code = 'SGF'
GROUP BY I.INVT_LEV1
The ultimate final goal is to include the new value PEND in the math subtraction for AVAILCan anyone help please
I can try without understanding whats going on here
with t as (
Select
INVT_LEV1,
(SELECT SUM(ORD_PEND_QTY) as PEND
from C_ORD_PEND P
WHERE P.COMP_CODE = I.COMP_CODE and P.CUST_CODE = I.CUST_CODE and P.INVT_LEV1 = I.INVT_LEV1 and P.ORD_LINE_TP = 'P') Subq2,
SUM(ON_HAND_QTY-ON_ORD_QTY-HOLD_NON_SHIP_QTY) Avail,
SUM(ON_HAND_QTY)OnHand,
to_Char(sysdate,'YYYY-MM-DD HH24:MI:SS')DataDate
FROM C_INVT
Where Comp_code = 'A1' and cust_code = 'SGF'
GROUP BY INVT_LEV1
)
select t.*, t.Avail - t.Subq2 as NewColumn from t
ASKER
I think I see what you are doing, but the original subquery was not working.. so the same errors occur.
As-is it complains about the GROUP BY. My attempts to add an aggregate or include the column in the group by both fail
As-is it complains about the GROUP BY. My attempts to add an aggregate or include the column in the group by both fail
but the original subquery was not working..why? whats the error message?
ASKER
'Not a Group By Expression '
The current group by is only on column 1
The current group by is only on column 1
ok, first you need to join 2 tables
then group by...
is this working fine?
then group by...
is this working fine?
Select INVT_LEV1,
to_Char(sysdate,'YYYY-MM-DD HH24:MI:SS') DataDate,
SUM(ORD_PEND_QTY) Subq2,
SUM(ON_HAND_QTY-ON_ORD_QTY-HOLD_NON_SHIP_QTY) Avail,
SUM(ON_HAND_QTY)OnHand,
FROM C_INVT I
inner join C_ORD_PEND P
on P.COMP_CODE = I.COMP_CODE
and P.CUST_CODE = I.CUST_CODE
and P.INVT_LEV1 = I.INVT_LEV1 and P.ORD_LINE_TP = 'P'
Where Comp_code = 'A1' and cust_code = 'SGF'
GROUP BY INVT_LEV1
ASKER
No errors.. I get data.
It looks like it is only returning rows where data exists in C_ORD_PEND. Most rows are in C_INVT only. Are we sure the join type is correct?
It looks like it is only returning rows where data exists in C_ORD_PEND. Most rows are in C_INVT only. Are we sure the join type is correct?
ASKER
I switched to LEFT JOIN and the results look correct.
Select I.INVT_LEV1,
sum(P.ORD_PEND_QTY) Subq2,
to_Char(sysdate,'YYYY-MM-DD HH24:MI:SS') DataDate,
SUM(I.ON_HAND_QTY-I.ON_ORD_QTY-I.HOLD_NON_SHIP_QTY) Avail,
SUM(I.ON_HAND_QTY)OnHand
FROM C_INVT I
LEFT join C_ORD_PEND P
on P.COMP_CODE = I.COMP_CODE
and P.CUST_CODE = I.CUST_CODE
and P.INVT_LEV1 = I.INVT_LEV1 and P.ORD_LINE_TP = 'P'
Where I.Comp_code = 'A1' and I.cust_code = 'SGF'
GROUP BY I.INVT_LEV1
Where there is no value in C_ORD_PEND, it returns (null). I guess maybe I add COALESCE and return a 0 as default so it can be used in subtraction.
ASKER
I need to audit the results, but this appears to be working
SELECT
INVT_LEV1, SUM(AVAIL-PEND), SUM(ONHAND), DATADATE
FROM (
Select I.INVT_LEV1,
sum(COALESCE(P.ORD_PEND_QTY,0)) PEND,
to_Char(sysdate,'YYYY-MM-DD HH24:MI:SS') DataDate,
SUM(I.ON_HAND_QTY-I.ON_ORD_QTY-I.HOLD_NON_SHIP_QTY) Avail,
SUM(I.ON_HAND_QTY)OnHand
FROM C_INVT I
LEFT join C_ORD_PEND P
on P.COMP_CODE = I.COMP_CODE
and P.CUST_CODE = I.CUST_CODE
and P.INVT_LEV1 = I.INVT_LEV1 and P.ORD_LINE_TP = 'P'
Where I.Comp_code = 'A1' and I.cust_code = 'SGF'
GROUP BY I.INVT_LEV1
)
GROUP BY INVT_LEV1, DATADATE
ASKER
The results are no good.
The values are overstated as a result of the join. I dont think these tables can be properly joined and a subquery must be used
The values are overstated as a result of the join. I dont think these tables can be properly joined and a subquery must be used
first of all, remove DATADATE from sub queries...
it does not depend on anything...
you can add it to final select if you wish
it does not depend on anything...
you can add it to final select if you wish
SELECT INVT_LEV1, SUM(AVAIL-PEND), SUM(ONHAND),
to_Char(sysdate,'YYYY-MM-DD HH24:MI:SS') DataDate
FROM (
Select I.INVT_LEV1,
sum(COALESCE(P.ORD_PEND_QTY,0)) PEND,
SUM(I.ON_HAND_QTY-I.ON_ORD_QTY-I.HOLD_NON_SHIP_QTY) Avail,
SUM(I.ON_HAND_QTY)OnHand
FROM C_INVT I
LEFT join C_ORD_PEND P
on P.COMP_CODE = I.COMP_CODE
and P.CUST_CODE = I.CUST_CODE and P.INVT_LEV1 = I.INVT_LEV1 and P.ORD_LINE_TP = 'P'
Where I.Comp_code = 'A1' and I.cust_code = 'SGF'
GROUP BY I.INVT_LEV1
)
GROUP BY INVT_LEV1
instead of guessing, just post an excel file
2 input table, with some sample data
and show what the result you want...
2 input table, with some sample data
and show what the result you want...
ASKER
I will do that.
It will take me a while to create. Please standby
It will take me a while to create. Please standby
I suppose the "outer" summing is "too much"...
But without further input it remains very vague...
But without further input it remains very vague...
select INVT_LEV1,
AVAIL - PEND,
ONHAND,
to_Char(sysdate, 'YYYY-MM-DD HH24:MI:SS') DataDate
from (select I.INVT_LEV1,
sum(COALESCE(P.ORD_PEND_QTY, 0)) PEND,
sum(I.ON_HAND_QTY - I.ON_ORD_QTY - I.HOLD_NON_SHIP_QTY) Avail,
sum(I.ON_HAND_QTY) OnHand
from C_INVT I
left join C_ORD_PEND P
on P.COMP_CODE = I.COMP_CODE
and P.CUST_CODE = I.CUST_CODE
and P.INVT_LEV1 = I.INVT_LEV1
and P.ORD_LINE_TP = 'P'
where I.Comp_code = 'A1'
and I.cust_code = 'SGF'
group by I.INVT_LEV1);
ASKER
Attached is an Excel file with some simplified sample data for both tables and the desired output.
Pretty sure a join will not work as the rows are not 1:1. See record BB555 in the data.
SGF PEND.xlsx
Pretty sure a join will not work as the rows are not 1:1. See record BB555 in the data.
SGF PEND.xlsx
whats the logic for
and you need to extend the sample data you posted
some column are missing here...
like HOLD_NON_SHIP_QTY
AVAIL (ORIG) | PEND | AVAIL (New) |
and you need to extend the sample data you posted
some column are missing here...
like HOLD_NON_SHIP_QTY
ASKER
AVAIL (ORIG) is the original query
is the SUM amount from the new table we are trying to add to the original query
AVAIL (NEW) is the final answer
SUM(ON_HAND_QTY-ON_ORD_QTY-HOLD_NON_SHIP_QTY)Avail
PENDis the SUM amount from the new table we are trying to add to the original query
AVAIL (NEW) is the final answer
SUM(ON_HAND_QTY-ON_ORD_QTY-HOLD_NON_SHIP_QTY - PEND)Avail
or
AVAIL - PEND
The column in excel is poorly named ON_HOLD_QTY should be HOLD_NON_SHIP_QTY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent. Works well.
I had to rename a couple a columns and add some where clauses.
Here is a final copy for future reference
I had to rename a couple a columns and add some where clauses.
Here is a final copy for future reference
with t as (
select INVT_LEV1,
sum(I.ON_HAND_QTY-I.ON_ORD_QTY-I.HOLD_NON_SHIP_QTY) Avail,
nvl((select sum (ORD_PEND_QTY)
from C_ORD_PEND o
where o.INVT_LEV1=i.INVT_LEV1 and o.comp_code='A1' and o.cust_code='SGF'),0) pend
from C_INVT i
where I.comp_code = 'A1' and I.Cust_code = 'SGF'
group by INVT_LEV1
)
select t.*,
avail-pend as avail_new,
to_Char(sysdate, 'YYYY-MM-DD HH24:MI:SS') DataDate
from t
order by INVT_LEV1
ASKER
Thank you
and show what you need as result...