var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
I would very much appreciate any help you can give me on a SQL statement I wish to run.
I have 3 tables;
AC_CODE_MATERIAL_SUPPLIERS (shopping lists) AC_PURCHASE_ORDER_HEADERS AC_PURCHASE_ORDER_DETAILS
The AC_CODE_MATERIAL_SUPPLIERS file contains a Shopping List for all Materials that have ever been purchased from Suppliers.
The AC_PURCHASE_ORDER_HEADERS table contains all Purchase Orders to Suppliers.
The AC_PURCHASE_ORDER_DETAILS table contains all Materials that were ordered on the Purchase Orders.
The AC_PURCHASE_ORDER_HEADERS and AC_PURCHASE_ORDER_DETAILS tables are linked by PURCHASE_ORDER_NO
I wish to delete Materials from a Suppliers Shopping list (where there were no Materials ordered since 2006.
I am using the following SQL statement to see when the last order for the Materials was;
Select h.supplier_code, d.material_code, MAX(H.PURCHASE_ORDER_DATE)
From ac_purchase_order_headers h,
(h.purchase_order_no = d.purchase_order_no)
HAVING MAX(H.PURCHASE_ORDER_DATE) < '01-JAN-2007' "
So in summary, I am attempting to delete all records in AC_CODE_MATERIAL_SUPPLIERS table where the Supplier (SUPPLIER_CODE) and Materials(MATERIAL_CODE) exists in the list that the above SQL produces.
I think it will go something like this ;
'SELECT * FROM AC_CODE_MATERIAL_SUPPLIERS where not exists
........................ ? '
Unfortunately after that I get stuck
Thank you very much in advance.
( SELECT 1
FROM ( SELECT h.supplier_code
FROM ac_purchase_order_headers h
JOIN ac_purchase_order_details d
ON d.purchase_order_no = h.purchase_order_no
HAVING MAX(h.purchase_order_date) < '01-JAN-2007'
) AS t
WHERE t.supplier_code = ac_code_material_suppliers.supplier_code
AND t.material_code = ac_code_material_suppliers.material_code
Thank you, thank you, thank you - your help is very much apprecaited