Home » SQL & PL/SQL » SQL & PL/SQL » problem in union
problem in union [message #262242] Sat, 25 August 2007 10:06 Go to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
hii

i have this query that select item from store and compare in with sales quantities
i am working on one item
[CODE]
SELECT SA.SA_ITEM ITEMSS,ST.ST_SALESPER SALPER,ST.ST_QTY STQ,SA.SA_QTY SAQ
FROM
(SELECT L.ITEM_CODE ST_ITEM ,S.SALESPER_ID ST_SALESPER,S.SUBINVENTORY_CODE ST_INV, SUM(L.S_QTY-L.RS_QTY) ST_QTY
FROM TRX_HEADERS@HAGAR H,TRX_LINES@HAGAR L,SALESPERSONS@HAGAR S
WHERE H.TRX_HEADER_ID = L.TRX_HEADER_ID
AND S.SALESPER_ID = H.SALESPER_ID
--AND H.SALESPER_ID= '3740'
AND H.TRX_DATE ='01-AUG-2007'
AND H.TRX_TYPE_ID =1
AND L.ITEM_CODE ='BEEF-LAUNCHEON-MEAT-340GM*24'
GROUP BY L.ITEM_CODE,S.SALESPER_ID,S.SUBINVENTORY_CODE
ORDER BY L.ITEM_CODE
) ST
,

(SELECT I.ITEM_CODE SA_ITEM,S.SALESPER_ID SA_SALESPER,S.SUBINVENTORY_CODE SA_INV,SUM(D.QUANTITY_ORDERED) SA_QTY
FROM RA_CUSTOMER_TRX_ALL M,RA_CUSTOMER_TRX_LINES_ALL D,MTL_SYSTEM_ITEMS I,JTF_RS_SALESREPS S
WHERE M.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.SALESPER_ID = M.PRIMARY_SALESREP_ID
AND M.TRX_DATE ='01-AUG-2007'
AND I.ITEM_CODE ='BEEF-LAUNCHEON-MEAT-340GM*24'
GROUP BY I.ITEM_CODE,D.INVENTORY_ITEM_ID,S.SALESPER_ID
,S.SUBINVENTORY_CODE
ORDER BY I.ITEM_CODE
) SA

, MTL_SYSTEM_ITEMS I

WHERE ST.ST_ITEM = SA.SA_ITEM
AND I.ITEM_CODE = SA.SA_ITEM
AND I.ITEM_CODE = ST.ST_ITEM
AND SA.SA_INV = ST.ST_INV
--ORDER BY SA.SA_ITEM



this results 11 row
   	ITEMSS	                        SALPER	STQ	SAQ
1	BEEF-LAUNCHEON-MEAT-340GM*24	3900	24	24
2	BEEF-LAUNCHEON-MEAT-340GM*24	3940	60	30
3	BEEF-LAUNCHEON-MEAT-340GM*24	3740	168	144
4	BEEF-LAUNCHEON-MEAT-340GM*24	3720	108	96
5	BEEF-LAUNCHEON-MEAT-340GM*24	3281	144	120
6	BEEF-LAUNCHEON-MEAT-340GM*24	3361	180	72
7	BEEF-LAUNCHEON-MEAT-340GM*24	3600	288	96
8	BEEF-LAUNCHEON-MEAT-340GM*24	3880	36	36
9	BEEF-LAUNCHEON-MEAT-340GM*24	3581	264	192
10	BEEF-LAUNCHEON-MEAT-340GM*24	3140	276	192
11	BEEF-LAUNCHEON-MEAT-340GM*24	3283	240	240
12	BEEF-LAUNCHEON-MEAT-340GM*24	3861	120	120
13	BEEF-LAUNCHEON-MEAT-340GM*24	3143	72	72




but there is item in store not found in the sales
see this query on of the store

SELECT L.ITEM_CODE ST_ITEM ,S.SALESPER_ID ST_SALESPER,SUM(L.S_QTY-L.RS_QTY) STQ
FROM TRX_HEADERS@HAGAR H,TRX_LINES@HAGAR L,SALESPERSONS@HAGAR S
WHERE H.TRX_HEADER_ID = L.TRX_HEADER_ID
AND S.SALESPER_ID = H.SALESPER_ID
--AND H.SALESPER_ID= '3740'
AND H.TRX_DATE ='01-AUG-2007'
AND H.TRX_TYPE_ID =1
AND L.ITEM_CODE = 'BEEF-LAUNCHEON-MEAT-340GM*24'
GROUP BY L.ITEM_CODE,S.SALESPER_ID



which result 13 rows
   	ST_ITEM	                     ST_SALESPER STQ
1	BEEF-LAUNCHEON-MEAT-340GM*24	3600	288
2	BEEF-LAUNCHEON-MEAT-340GM*24	3900	24
3	BEEF-LAUNCHEON-MEAT-340GM*24	3140	276
4	BEEF-LAUNCHEON-MEAT-340GM*24	3143	72
5	BEEF-LAUNCHEON-MEAT-340GM*24	3180	72
6	BEEF-LAUNCHEON-MEAT-340GM*24	3281	144
7	BEEF-LAUNCHEON-MEAT-340GM*24	3283	240
8	BEEF-LAUNCHEON-MEAT-340GM*24	3361	180
9	BEEF-LAUNCHEON-MEAT-340GM*24	3581	264
10	BEEF-LAUNCHEON-MEAT-340GM*24	3620	120
11	BEEF-LAUNCHEON-MEAT-340GM*24	3720	108
12	BEEF-LAUNCHEON-MEAT-340GM*24	3740	168
13	BEEF-LAUNCHEON-MEAT-340GM*24	3861	120
14	BEEF-LAUNCHEON-MEAT-340GM*24	3880	36
15	BEEF-LAUNCHEON-MEAT-340GM*24	3940	60


this query is the first query in the shown first block

so i make a union between them like that
[CODE]
SELECT SA.SA_ITEM ITEMSS,ST.ST_SALESPER SALPER,ST.ST_QTY STQ,SA.SA_QTY SAQ
FROM
(SELECT L.ITEM_CODE ST_ITEM ,S.SALESPER_ID ST_SALESPER,S.SUBINVENTORY_CODE ST_INV, SUM(L.S_QTY-L.RS_QTY) ST_QTY
FROM TRX_HEADERS@HAGAR H,TRX_LINES@HAGAR L,SALESPERSONS@HAGAR S
WHERE H.TRX_HEADER_ID = L.TRX_HEADER_ID
AND S.SALESPER_ID = H.SALESPER_ID
--AND H.SALESPER_ID= '3740'
AND H.TRX_DATE ='01-AUG-2007'
AND H.TRX_TYPE_ID =1
GROUP BY L.ITEM_CODE,S.SALESPER_ID,S.SUBINVENTORY_CODE
ORDER BY L.ITEM_CODE
) ST
,

(SELECT I.ITEM_CODE SA_ITEM,S.SALESPER_ID SA_SALESPER,S.SUBINVENTORY_CODE SA_INV,SUM(D.QUANTITY_ORDERED) SA_QTY
FROM RA_CUSTOMER_TRX_ALL M,RA_CUSTOMER_TRX_LINES_ALL D,MTL_SYSTEM_ITEMS I,JTF_RS_SALESREPS S
WHERE M.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.SALESPER_ID = M.PRIMARY_SALESREP_ID
AND M.TRX_DATE ='01-AUG-2007'
AND I.ITEM_CODE ='BEEF-LAUNCHEON-MEAT-340GM*24'
GROUP BY I.ITEM_CODE,D.INVENTORY_ITEM_ID,S.SALESPER_ID
,S.SUBINVENTORY_CODE
ORDER BY I.ITEM_CODE
) SA

, MTL_SYSTEM_ITEMS I

WHERE ST.ST_ITEM = SA.SA_ITEM
AND I.ITEM_CODE = SA.SA_ITEM
AND I.ITEM_CODE = ST.ST_ITEM
AND SA.SA_INV = ST.ST_INV
--ORDER BY SA.SA_ITEM

UNION ALL
SELECT L.ITEM_CODE ST_ITEM ,S.SALESPER_ID ST_SALESPER,SUM(L.S_QTY-L.RS_QTY) STQ,0 SAQ
FROM TRX_HEADERS@HAGAR H,TRX_LINES@HAGAR L,SALESPERSONS@HAGAR S
WHERE H.TRX_HEADER_ID = L.TRX_HEADER_ID
AND S.SALESPER_ID = H.SALESPER_ID
--AND H.SALESPER_ID= '3740'
AND H.TRX_DATE ='01-AUG-2007'
AND H.TRX_TYPE_ID =1
AND L.ITEM_CODE = 'BEEF-LAUNCHEON-MEAT-340GM*24'
GROUP BY L.ITEM_CODE,S.SALESPER_ID
--ORDER BY L.ITEM_CODE

   	ITEMSS	                       SALPER	STQ	SAQ
1	BEEF-LAUNCHEON-MEAT-340GM*24	3900	24	24
2	BEEF-LAUNCHEON-MEAT-340GM*24	3940	60	30
3	BEEF-LAUNCHEON-MEAT-340GM*24	3740	168	144
4	BEEF-LAUNCHEON-MEAT-340GM*24	3720	108	96
5	BEEF-LAUNCHEON-MEAT-340GM*24	3281	144	120
6	BEEF-LAUNCHEON-MEAT-340GM*24	3361	180	72
7	BEEF-LAUNCHEON-MEAT-340GM*24	3600	288	96
8	BEEF-LAUNCHEON-MEAT-340GM*24	3880	36	36
9	BEEF-LAUNCHEON-MEAT-340GM*24	3581	264	192
10	BEEF-LAUNCHEON-MEAT-340GM*24	3140	276	192
11	BEEF-LAUNCHEON-MEAT-340GM*24	3283	240	240
12	BEEF-LAUNCHEON-MEAT-340GM*24	3861	120	120
13	BEEF-LAUNCHEON-MEAT-340GM*24	3143	72	72
14	BEEF-LAUNCHEON-MEAT-340GM*24	3361	180	0
15	BEEF-LAUNCHEON-MEAT-340GM*24	3581	264	0
16	BEEF-LAUNCHEON-MEAT-340GM*24	3861	120	0
17	BEEF-LAUNCHEON-MEAT-340GM*24	3140	276	0
18	BEEF-LAUNCHEON-MEAT-340GM*24	3283	240	0
19	BEEF-LAUNCHEON-MEAT-340GM*24	3600	288	0
20	BEEF-LAUNCHEON-MEAT-340GM*24	3740	168	0
21	BEEF-LAUNCHEON-MEAT-340GM*24	3940	60	0
22	BEEF-LAUNCHEON-MEAT-340GM*24	3900	24	0
23	BEEF-LAUNCHEON-MEAT-340GM*24	3281	144	0
24	BEEF-LAUNCHEON-MEAT-340GM*24	3143	72	0
25	BEEF-LAUNCHEON-MEAT-340GM*24	3180	72	0
26	BEEF-LAUNCHEON-MEAT-340GM*24	3620	120	0
27	BEEF-LAUNCHEON-MEAT-340GM*24	3880	36	0
28	BEEF-LAUNCHEON-MEAT-340GM*24	3720	108	0


the past query result items again with zero values
i want the result be 15 row (13 + 2 of zero value)


the difference between the query that results 13 rows
and the one that result 15 row
is that condition AND SA.SA_INV = ST.ST_INV
Re: problem in union [message #262254 is a reply to message #262242] Sat, 25 August 2007 12:52 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Are you sure you want to combine the results of query 1 and 2 in the union? Due to the different columns specified in the GROUP BY, it appears to me that you will have inconsistent results.


Anyway, back to your original problem...I'm guessing the rows are missing from the result set of query 1 due to missing data in a joining table. Try an outer join. I modified your query to use ANSI joins because of the way the three tables join together. If you are in a pre-9i environment, this solution will not work (please post your version of Oracle next time).

SELECT SA.SA_ITEM ITEMSS
     , ST.ST_SALESPER SALPER
     , ST.ST_QTY STQ
     , SA.SA_QTY SAQ
FROM ( SELECT I.ITEM_CODE SA_ITEM
            , S.SALESPER_ID SA_SALESPER
            , S.SUBINVENTORY_CODE SA_INV
            , SUM(D.QUANTITY_ORDERED) SA_QTY
       FROM   RA_CUSTOMER_TRX_ALL M
            , RA_CUSTOMER_TRX_LINES_ALL D
            , MTL_SYSTEM_ITEMS I
            , JTF_RS_SALESREPS S
       WHERE  M.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID
         AND  D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
         AND  S.SALESPER_ID = M.PRIMARY_SALESREP_ID
         AND  M.TRX_DATE ='01-AUG-2007'
         AND  I.ITEM_CODE ='BEEF-LAUNCHEON-MEAT-340GM*24'
       GROUP  BY I.ITEM_CODE
            , D.INVENTORY_ITEM_ID
            , S.SALESPER_ID
            , S.SUBINVENTORY_CODE
       ORDER  BY I.ITEM_CODE ) SA
JOIN   MTL_SYSTEM_ITEMS I
  ON   I.ITEM_CODE = SA.SA_ITEM
RIGHT
JOIN ( SELECT L.ITEM_CODE ST_ITEM 
            , S.SALESPER_ID ST_SALESPER
            , S.SUBINVENTORY_CODE ST_INV
            , SUM(L.S_QTY-L.RS_QTY) ST_QTY
       FROM   TRX_HEADERS@HAGAR H
            , TRX_LINES@HAGAR L
            , SALESPERSONS@HAGAR S
       WHERE  H.TRX_HEADER_ID = L.TRX_HEADER_ID
         AND  S.SALESPER_ID = H.SALESPER_ID
         --AND H.SALESPER_ID= '3740'
         AND  H.TRX_DATE ='01-AUG-2007'
         AND  H.TRX_TYPE_ID =1
         AND  L.ITEM_CODE ='BEEF-LAUNCHEON-MEAT-340GM*24'
       GROUP  BY L.ITEM_CODE,S.SALESPER_ID,S.SUBINVENTORY_CODE
       ORDER  BY L.ITEM_CODE ) ST
  ON   ST.ST_ITEM = SA.SA_ITEM
 AND   I.ITEM_CODE = ST.ST_ITEM
 AND   SA.SA_INV = ST.ST_INV
Re: problem in union [message #262551 is a reply to message #262242] Mon, 27 August 2007 08:17 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
emadnabil wrote on Sat, 25 August 2007 11:06

AND H.TRX_DATE ='01-AUG-2007'



Please do not compare a DATE column to a character string.
FOO SCOTT>l
  1* select 1 from dual where sysdate > '01-AUG-2007'
FOO SCOTT>/
select 1 from dual where sysdate > '01-AUG-2007'
                                   *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Previous Topic: Grant previlages
Next Topic: PLS-00123 Error
Goto Forum:
  


Current Time: Fri Dec 09 03:51:07 CST 2016

Total time taken to generate the page: 0.14990 seconds