Results 1 to 3 of 3

Thread: MINUS keyword

  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: MINUS keyword

    Hie guys, Im having a problem here..
    The problem is that im inserting from one table to another table and im using the keyword MINUS to check if the row already exist, and do not insert.. Its working fine on the same day but it duplicates the data the next day since there is a column to generate sysdate and when the sysdate changes it duplicates.

    i tried to remove the "date_reconciliation" and "week_no" from the MINUS statement, but it says that "query block has inconsistent number of result columns"

    does anyone knows how to overcome this by editing the code ??
    (have attached the code, pls view and ur feedback is appreciated)
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    The order of columns in the SELECT lists must also match, and these don't seem to:

    Code:
    SELECT current_date, week_no, customer_number, order_number, ...
    ...
    MINUS
    SELECT distributor_id, order_no, amount_uploaded, amount_purchased, ...
    btw, Word isn't the best attachment format. It would be better to post the code directly and use [code] tags, like this:

    Code:
    INSERT INTO trap_ra_oms
        (date_reconcilation, week_no, distributor_id, order_no, amount_uploaded,
         amount_purchased, discount, order_type, order_status, date_purchased, date_uploaded, sales_order_no, date_mod)
        ( SELECT current_date, week_no, customer_number, order_number, ordered_quantity, value, discount,
          order_type, order_status, order_date, approve_date, cust_po_number, current_date
    FROM
        ( SELECT DISTINCT
        ( SELECT TRUNC(sysdate) from dual
        ) AS current_date,
        ( SELECT to_char(sysdate,'WW') from dual
        ) AS week_no,
          customer_number, order_number, ordered_quantity, value,
        ( SELECT distinct (a.unit_selling_price-a.unit_list_price)
          FROM   trap_stage_oms a
          WHERE  a.customer_number=b.customer_number
          AND    a.cust_po_number=b.cust_po_number
        ) AS discount,
          'DiGi to Distributor' as order_type, order_status, order_date, approve_date, cust_po_number
    FROM   trap_stage_oms b)
    MINUS
    SELECT distributor_id, order_no, amount_uploaded, amount_purchased, discount, order_type, order_status, date_purchased, date_u
    ploaded, sales_order_no, date_mod FROM trap_ra_oms
    );
    I'm guessing you need something like this:

    Code:
    INSERT INTO trap_ra_oms
         ( date_reconcilation
         , week_no
         , distributor_id
         , order_no
         , amount_uploaded
         , amount_purchased
         , discount
         , order_type
         , order_status
         , date_purchased
         , date_uploaded
         , sales_order_no
         , date_mod )
    SELECT TRUNC(sysdate)
         , TO_CHAR(sysdate,'WW')
         , customer_number
         , order_number
         , ordered_quantity
         , value
         , discount
         , order_type
         , order_status
         , order_date
         , approve_date
         , cust_po_number
         , current_date
    FROM   ( SELECT customer_number
                  , order_number
                  , ordered_quantity
                  , value
                  , ( SELECT DISTINCT a.unit_selling_price-a.unit_list_price
                      FROM   trap_stage_oms a
                      WHERE  a.customer_number=b.customer_number
                      AND    a.cust_po_number=b.cust_po_number ) AS discount
                  , 'DiGi to Distributor' AS order_type
                  , order_status
                  , order_date
                  , approve_date
                  , cust_po_number
             FROM   trap_stage_oms b
             MINUS
             SELECT distributor_id
                  , order_no
                  , amount_uploaded
                  , amount_purchased  AS value
                  -- , discount  -- ??
                  , order_type
                  , order_status
                  , date_purchased
                  , date_uploaded
                  , sales_order_no
                  -- , date_mod ??
             FROM   trap_ra_oms );
    Last edited by WilliamR; 11-21-05 at 04:47.

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    hie williamr,
    i tried the way u gave but it still says "query block has incorrect number of result columns"

    anyway sorry,
    the original minus statement contains all of the columns in the table as in

    Code:
    MINUS
    SELECT  date_reconcilation, week_no, distributor_id, order_no, amount_uploaded, amount_purchased, discount, order_type, order_
    status, date_purchased, date_uploaded, sales_order_no, date_mod FROM trap_ra_oms
    Any idea ?

Posting Permissions

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