Link to home
Start Free TrialLog in
Avatar of JDCam
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
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  

Open in new window

This is my attempt at adding the subquery to table C_ORD_PEND. it would return a value called PEND
Select
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

Open in new window

The ultimate final goal is to include the new value PEND in the math subtraction for AVAIL

Can anyone help please
Avatar of HainKurt
HainKurt
Flag of Canada image

please attach an excel that has sample data for involved table(s)
and show what you need as result...
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

Open in new window

Avatar of JDCam
JDCam

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 
but the original subquery was not working.. 
why? whats the error message?
Avatar of JDCam

ASKER

'Not a Group By Expression '

The current group by is only on column 1
ok, first you need to join 2 tables
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

Open in new window


Avatar of JDCam

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?
Avatar of JDCam

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

Open in new window

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. 
Avatar of JDCam

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

Open in new window

Avatar of JDCam

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

first of all, remove DATADATE  from sub queries...
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 

Open in new window

instead of guessing, just post an excel file
2 input table, with some sample data
and show what the result you want...
Avatar of JDCam

ASKER

I will do that.
It will take me a while to create. Please standby

Avatar of Alex [***Alex140181***]
I suppose the "outer" summing is "too much"...
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);

Open in new window

Avatar of JDCam

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
whats the logic for
 
AVAIL (ORIG)PENDAVAIL (New) 

and you need to extend the sample data you posted
some column are missing here...
like HOLD_NON_SHIP_QTY
please explain how did you get these numbers

7,0,7=?
5,4,1=?User generated image
Avatar of JDCam

ASKER

AVAIL (ORIG) is the original query
SUM(ON_HAND_QTY-ON_ORD_QTY-HOLD_NON_SHIP_QTY)Avail

Open in new window

PEND
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 - PEND)Avail
or
AVAIL - PEND

Open in new window

The column in excel is poorly named ON_HOLD_QTY should be HOLD_NON_SHIP_QTY
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JDCam

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

Open in new window

Avatar of JDCam

ASKER

Thank you