Simple Query Problem

[gazal]

Hi Friends
I am trying to achieve a very common business rule, i.e.:
I have following tables:

1) PO_HDR - PO_ID PK
2) PO_DTL - PO_ID FK
3) GRN_HDR - PO_ID FK FROM PO_HDR
4) GRN_DTL - GRN_ID FK

My requirement is to list only those PO's where the po_dtl qty is less than the grn_dtl qty.

Means in simple terms the qty received should not be greater than qty ordered.


I want the results in one single query...

Please help.

Oracle 9i on Windows 2000.

Gazal

 

[SantaMufasa]

Gazal,

Absent any sample data to work with, here is my assertion for a resolution to your question:

select ph.PO_ID,pd.qty
  from PO_HDR ph
      ,PO_DTL pd
      ,GRN_HDR gh
      ,GRN_DTL gd
where ph.po_id = pd.po_id
  and gh.po_id = ph.po_id
  and gd.grn_id = gh.grn_id
  and pd.qty < gd.qty;

Let us know if this meets your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[gazal]

Hi Mufasa

Very true i have not provided sample data... Anyways i am providing it now, coz the query does not serve the purpose i am looking for...

Lets say i have four tables namely

T1,T2,T3 AND T4
And their structure is

T1
===
COL1 NUMBER(10), PK
COL2 VARCHAR2(500)


T2
===
T2COL NUMBER(10), PK
COL1 NUMBER(10), FK
TDESC VARCHAR2(500),
T2QTY NUMBER(10),
ITEM_ID NUMBER(10)

T3
===
T3COL1 NUMBER(10), PK
T1COL1 NUMBER(10), FK TO T1
T3COLDESC VARCHAR2(600)

T4
====

T4COL1 NUMBER(10), PK
T3COL1 NUMBER(10), FK TO T3
T4COLDESC VARCHAR2(500),
ITEM_ID NUMBER(10),
T4COLQTY NUMBER(10)

NOW THE DATA PART
=================

T1
===
COL1 COL2
==== =====
1 arif
2 shaikh
3 umair

T2
====
T2COL COL1 TDESC T2QTY ITEM_ID
====== ===== ======= ====== =======
1 1 arif test 5 10
2 1 ariftest2 10 20
3 1 ariftest3 2 30
4 2 shaikh1 7 40
5 2 shaikh2 9 50
6 2 shaikh3 9 60
7 2 shaikh4 1 70
8 3 ummi 100 50
9 3 umairm 250 40

T3
====
T3COL1 T1COL1 T3COLDESC
====== ====== ========
1 1 first
2 3 third

T4
====

T4COL1 T3COL1 T4COLDESC T4COLQTY ITEM_ID
====== ====== ========= ======== ========
1 1 first of firsts 5 10
2 1 second of second 10 20
3 2 umair items 250 40
4 2 ummi 100 50


Now i want to list only those rows from t1 which are not there in t3 and the sum of t2qty is greater than sum of t4colqty. And it should be for each item_id.


Hope that makes it very clear

please help ....

Regards

Gazal

 

[SantaMufasa]

Gazal,

Before I spend any time composing a solution for you, I want to confirm something: For your data to make sense, it seems to me that the last two columns in your T4 table should be reversed, correct?...Specifically, should the values "10","20","40",and "50" be ITEM_IDs instead of T4COLQTYs?...And values "5","10","250", and "100" are T4COLQTYs and not ITEM_IDs?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Also:

...i want to list only those rows from t1...

If you are listing only column data from T1, you will not be seeing any ITEM_IDs and QTYs, right? And if that is true, then what business benefit does this show you?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[gazal]

Yes u r right, but anyways i got it sorted myself. Thanks alot for ur responses
here is the query which i wrote

 SELECT  a.col1 p_id,
        b.t2col p_dtl_id, b.item_id, SUM(b.t2QTY) p_qty, d.item_id, SUM(d.t4colqty) gr_qty
FROM  t1  A,
      t2 B,
      t3 C,
      t4 D
WHERE B.COL1 = A.COL1
 AND C.t1col1  = A.col1
 AND D.t3col1 = C.t3col1
 AND D.item_id = B.item_id
GROUP BY a.col1,
        b.t2col, b.item_id, d.item_id
HAVING NVL(SUM(b.t2QTY) ,0) > NVL(SUM(d.t4colqty) ,0)
UNION
SELECT  a.col1 p_id,
        b.t2col b_dtl_id, b.item_id, b.t2QTY p_qty, b.item_id, b.t2QTY gr_qty  
FROM  t1  A,
      t2 B
WHERE B.col1 = A.col1
 AND NOT EXISTS ( SELECT d.item_id
                FROM t3 C,
                     t4 D
               WHERE c.T1COL1 = a.col1
                 and d.t3col1= c.t3col1
                 AND d.item_id = b.item_id )

 

[SantaMufasa]

Gazal,

I used your code against the data that you posted, and the results seem to differ from your specifications:

P_ID   P_DTL_ID    ITEM_ID      P_QTY    ITEM_ID     GR_QTY
---- ---------- ---------- ---------- ---------- ----------
   1          3         30          2         30          2
   2          4         40          7         40          7
   2          5         50          9         50          9
   2          6         60          9         60          9
   2          7         70          1         70          1

First, Output-line 1 has a P_ID = 1, yet 1 is a P_ID that appears in T3, which breaks this rule:

...i want to list only those rows from t1 which are not there in t3...

Next,

the sum of t2qty is greater than sum of t4colqty. And it should be for each item_id.

...your Output-lines 4 & 5 show ITEM_IDs "60" and "70", yet there are no such ITEM_IDs in your T4 sample data.

Did you "pull a Fast One" on me?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]