SQL help

[cjmartin]

I have tried several different things with none providing the correct results. Any help is appreciated.

I have the following:
Table A Table B
Pallet_No Pallet_No
Qty Qty

I need to return:
- All of the Pallet_No's from Table A that are not in Table B
- All of the Pallet_No's from Table B that are not in Table A
- All the Pallet_No's where there is a difference in Qty b/w Table A and Table B.

Thanks for your help.

[debasisdas]

would you mind to post here what are the different things that you have tried ?

[cjmartin]

would you mind to post here what are the different things that you have tried ?

Here is some of the SQL I have done to try to get this to work.

select e.EMPLOYEE_NAME, count(w.pallet_no) from pca_wms_fg w, kr_employee_v e
 where trunc (w.modified_date) >= TO_DATE('10/18/2009','mm/dd/yyyy')
 and trunc (w.modified_date) <= TO_DATE('10/21/2009','mm/dd/yyyy')
 and w.MODIFIED_BY = e.BADGENUM
 group by e.employee_name

-----------------

select e.EMPLOYEE_NAME, e.badgenum, count(w.pallet_no), sum(QTY)
from pca_wms_fg w, kr_employee_v e
where trunc (w.modified_date) >= TO_DATE('10/18/2009','mm/dd/yyyy')
and trunc (w.modified_date) <= TO_DATE('10/23/2009','mm/dd/yyyy')
and w.MODIFIED_BY = e.BADGENUM
group by e.employee_name, e.badgenum

-------------

select employee_name, NbrPallets, CartonQty, OrderQty from (
select employee_name, count(pallet_no) NbrPallets, sum(QTY)CartonQty, sum(qty) OrderQty from (
select EMPLOYEE_NAME, w.pallet_no, w.QTY, 0
from pca_wms_fg w, kr_employee_v e
where trunc (w.modified_date) >= TO_DATE('10/18/2009','mm/dd/yyyy')
and trunc (w.modified_date) <= TO_DATE('10/23/2009','mm/dd/yyyy')
and w.MODIFIED_BY = e.BADGENUM
group by e.employee_name
union all
select employee_name, '', 0, w.qty
from pca_wms_fg w join kr_employee_v e
on w.modified_by = e.badgenum
where w.location_to like ('6%')
and trunc (w.modified_date) >= TO_DATE('10/18/2009','mm/dd/yyyy')
and trunc (w.modified_date) <= TO_DATE('10/23/2009','mm/dd/yyyy')
)
group by employee_name, w.pallet_no
)

------------

select pallet_no, decode(Qty1,0,Qty2,Qty1) QTY from (
select pallet_no, sum(QTY) Qty1, sum(LOCT_ONHAND)Qty2 from (
Select pallet_no, QTY, 0 LOCT_ONHAND from pca_wms_fg
union all
select SUBLOT_NO pallet_no , 0, LOCT_ONHAND from XXPCA_INVENTORY_V@tLK
)
group by pallet_no
)

Thanks.

[debasisdas]

Try to use set operator, that should solve your Qry1 and Qry2.

for the third qry try to join both the tables.