Results 1 to 4 of 4

Thread: Sql

  1. #1
    Join Date
    Aug 2012
    Posts
    6

    Unanswered: Sql

    Hi there,

    I'm not sure if I am in the correct place!!.

    I am using SQL on an Oracle 10g database.

    I am looking for some help in a SQL script and your help would be much appreciated.


    I have 2 tables;
    AC_CODE_MATERIAL_SUPPLIERS
    and
    AC_PURCHASE_ORDER_HEADERS

    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.

    I want to delete the Shopping lists for all Suppliers that have not had a Purchase Order since 2006.

    I can get a list of the SUPPLIER_CODEs that I want to delete by using the following code;

    Select
    SUPPLIER_CODE,
    Max(PURCHASE_ORDER_DATE) as "MostRecentPoDate"
    from
    AC_PURCHASE_ORDER_HEADERS
    Group by
    SUPPLIER_CODE
    Having
    Max(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) exists in the list that the above SQL produces.

    Thank you in advance

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Should look something like this:
    Code:
    delete from AC_CODE_MATERIAL_SUPPLIERS s
     where not exists(select 'X' 
                        from AC_PURCHASE_ORDER_HEADERS o
                       where s.SUPPIER_CODE = o.SUPPLIER_CODE
                         and o.PURCHASE_ORDER_DATE >= '01-JAN-2007');

  3. #3
    Join Date
    Aug 2012
    Posts
    6
    Thank you so much - you have been extremely helpful.

  4. #4
    Join Date
    Aug 2012
    Posts
    6

    SQl

    Hi again and thanks once again for your invaluable help last week.

    I would like to prevail on your excellence again. if I may.

    Following on from my query, I wish to run a different query.


    I have 3 tables;
    AC_CODE_MATERIAL_SUPPLIERS
    and
    AC_PURCHASE_ORDER_HEADERS
    and
    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.


    I now want 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,
    ac_purchase_order_details d
    where
    (h.purchase_order_no = d.purchase_order_no)
    GROUP BY
    D.MATERIAL_CODE,
    H.SUPPLIER_CODE

    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.

    Thank you very much in advance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •