Oracle

SQL help

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2009

Posts: 2

Reputation: cjmartin is an unknown quantity at this point

Solved Threads: 0

cjmartin

Offline

Newbie Poster

SQL help

25 Days Ago

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.

•

Join Date: Feb 2007

Posts: 2,093

Reputation: debasisdas will become famous soon enough

Solved Threads: 126

debasisdas debasisdas is offline

Offline

Postaholic

25 Days Ago

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

Share your Knowledge.

•

Join Date: Nov 2009

Posts: 2

Reputation: cjmartin is an unknown quantity at this point

Solved Threads: 0

cjmartin

Offline

Newbie Poster

25 Days Ago

•

Originally Posted by debasisdas

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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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, 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

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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 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

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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 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
)

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
)
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.

Last edited by peter_budo; 23 Days Ago at 6:13 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)