Hi All,


I have two table

Table A

Col1 Col2 Col3 Qty
---- ---- ---- ----
1 2 3 1
1 2 2 2
1 2 3 3


Table B

Col1 Col2 Col3 Qty
---- ---- ---- ----
1 2 3 1
1 2 3 2
1 2 3 3
1 2 3 4
1 2 3 5
1 2 3 6
1 2 3 7

Now i have to compare the quantity of two table based on Col1, Col2, Col3

The query which i have written is

SELECT SAP_INV.PLANT, SAP_INV.MATNR,
Decode(SAP_INV.BATCH,'NOBATCHNO','',SAP_INV.BATCH),
SAP_INV.SLOC,SAP_INV.SAP_QTY, USER_INV.USER_QTY,
SAP_INV.SAP_QTY - USER_INV.USER_QTY QTY_DIFF
FROM
(SELECT UPPER(PLANT) PLANT,UPPER(MATNR) MATNR,
UPPER(NVL (BATCH,'NOBATCHNO'))BATCH,UPPER(SLOC)SLOC,
SUM(BOOK_QTY) SAP_QTY FROM PI_SLOC_SAP_INV
GROUP BY UPPER(PLANT), UPPER(MATNR),
UPPER(NVL(BATCH,'NOBATCHNO')),
UPPER(SLOC)) SAP_INV,
(SELECT UPPER(MATNR)MATNR,UPPER(NVL(BATCH,'NOBATCHNO'))BATCH,
UPPER(SLOC)SLOC,SUM(COUNT_QTY) USER_QTY
FROM PI_SLOC_USER_INV
GROUP BY UPPER(MATNR),UPPER(NVL(BATCH,'NOBATCHNO')),
UPPER(SLOC)) USER_INV
WHERE SAP_INV.MATNR = USER_INV.MATNR
AND SAP_INV.BATCH = USER_INV.BATCH
AND SAP_INV.SLOC = USER_INV.SLOC

What i have done is grouping of Table and A
then Grouping of Table B and then Compare the resultset of this grouping.

Can u suggest me anyother efficient way to write this query


Thanks in advance

With Regards

Hitesh Parikh