merge with case and anlytical functions not working in pl/sql block - is this bug?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: merge with case and anlytical functions not working in pl/sql block - is this bug?

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    merge with case and anlytical functions not working in pl/sql block - is this bug?

    If i run as SQL oracle executes it pretty fine, but same sql if put in PL/SQL block is not..

    Code:
    WW04_PRO> ed
    Wrote file afiedt.buf
    
      1  Begin
      2    Merge Into OPEN_ORDER_DETAIL_TEST   OOD
      3    Using
      4    (
      5    SELECT /*+ Use_NL(A, B) */
      6      A.SALES_ORGANIZATION_CD,
      7      A.SALES_ORDER_NO,
      8      A.SALES_ORDER_ITEM_NO,
      9      A.SCHEDULE_LINE_NO,
     10      A.SOURCE_NM,
     11      CASE WHEN   COUNT(1)  OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM) =1 THEN
     12                     B.SHIP_QT
     13      ELSE    CASE  WHEN  B.SHIP_QT - SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW )  >=0 THEN
     14                              ORDER_SLS_UNIT_QT
     15              ELSE
     16                    CASE  WHEN  FIRST_VALUE(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW ) -
     17                                SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW ) = 0 AND
     18                                FIRST_VALUE(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW ) > 0 THEN
     19                              B.SHIP_QT
     20                    ELSE
     21                          CASE   WHEN
     22                                 B. SHIP_QT- SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM  ORDER BY   NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS    BETWEEN UNBOUNDED  PRECEDING   AND 1 PRECEDING)  >=0  THEN
     23                                                        B.SHIP_QT- SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM  ORDER BY    NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS    BETWEEN UNBOUNDED  PRECEDING   AND 1 PRECEDING)
     24                          ELSE 0
     25                          END
     26                    END
     27              END
     28      END SHIP_QT
     29    FROM
     30      OPEN_ORDER_DETAIL_TEST   A ,
     31      (SELECT
     32              A.SALES_ORGANIZATION_CD,
     33              A.REFERENCE_DOCUMENT_NO SALES_ORDER_NO,
     34              A.REFRENCE_DOCUMENT_ITEM_NO SALES_ORDER_ITEM_NO,
     35              A.SOURCE_NM,
     36              SUM(A.ACTUAL_DELIVERED_QT) SHIP_QT
     37       FROM
     38              (Select /*+ Ordered Use_Nl(X) */ * From ShipDtl X
     39                 Where  REFERENCE_DOCUMENT_NO In
     40                       (select /*+ ordered use_nl(x) */ distinct SALES_ORDER_NO
     41                        from slsord x where (SALES_ORGANIZATION_CD, SALES_ORDER_NO ) in
     42                                            (select distinct SALES_ORGANIZATION_CD, reference_document_no from temp_shipdtl)
     43                                       and   ORDER_STATUS_CD = '0'
     44                                Union
     45                        Select Distinct Sales_Order_No From open_order_vw X)
     46              ) A
     47       GROUP BY
     48              A.SALES_ORGANIZATION_CD,
     49              A.REFERENCE_DOCUMENT_NO,
     50              A.REFRENCE_DOCUMENT_ITEM_NO,
     51              A.SOURCE_NM
     52      ) B
     53    WHERE
     54      A.SALES_ORGANIZATION_CD    =   B.SALES_ORGANIZATION_CD AND
     55      A.SALES_ORDER_NO           =   B.SALES_ORDER_NO AND
     56      A.SALES_ORDER_ITEM_NO      =   B.SALES_ORDER_ITEM_NO AND
     57      SUBSTR(A.SOURCE_NM,-3,3)   =   SUBSTR(B.SOURCE_NM,-3,3)
     58    ) Process_OOD
     59    On
     60    (
     61      OOD.SALES_ORGANIZATION_CD  =   PROCESS_OOD.SALES_ORGANIZATION_CD  And
     62      OOD.SALES_ORDER_NO         =   PROCESS_OOD.SALES_ORDER_NO         And
     63      OOD.SALES_ORDER_ITEM_NO    =   PROCESS_OOD.SALES_ORDER_ITEM_NO    And
     64      OOD.SCHEDULE_LINE_NO       =   PROCESS_OOD.SCHEDULE_LINE_NO       And
     65      OOD.SOURCE_NM              =   PROCESS_OOD.SOURCE_NM
     66    )
     67    When Matched Then
     68    Update Set
     69      OOD.SHIP_QT                =   PROCESS_OOD.SHIP_QT
     70    When Not Matched Then
     71    Insert
     72    (
     73      OOD.SALES_ORGANIZATION_CD,
     74      OOD.SALES_ORDER_NO,
     75      OOD.SALES_ORDER_ITEM_NO,
     76      OOD.SCHEDULE_LINE_NO,
     77      OOD.SOURCE_NM,
     78      OOD.SHIP_QT
     79    )
     80    Values
     81    (
     82      null,
     83      null,
     84      null,
     85      null,
     86      null,
     87      null
     88    );
     89* End;
    WW04_PRO> /
                      CASE  WHEN  FIRST_VALUE(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW ) -
                                                                                                                                                                                                                                 *
    ERROR at line 16:
    ORA-06550: line 13, column 287:
    PL/SQL: ORA-00905: missing keyword
    ORA-06550: line 2, column 3:
    PL/SQL: SQL Statement ignored
    Am i missing something very basic?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is this 8i, where the SQL engine for PL/SQL wasn't as complete as that for native SQL?

    If so, use execute immediate to run the SQL code within the block
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    its 9.2.0.4.. and yes i did use execute immediate also but no luck...

    possibly a bug?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    its mind blowing !!!

    Finally i got a work around but what i didnt understand is "Can Oracle get so stupid??"

    All I did was created a VIEW for that subquery in that Merge Statement.. And replaced subquery with View and it stared working..

    How on earth can this happen??

    Ok here is the view..

    Code:
    Create View Shipment_Alloc_Ood_Vw As
      SELECT /*+ Full(A) Parallel(A, 4) PQ_DISTRIBUTE(B NONE, BROADCAST) */
        A.SALES_ORGANIZATION_CD,
        A.SALES_ORDER_NO,
        A.SALES_ORDER_ITEM_NO,
        A.SCHEDULE_LINE_NO,
        A.SOURCE_NM,
        CASE WHEN   COUNT(1)  OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM) =1 THEN
        /* Need To Check This Case */
        --        CASE  WHEN  B.SHIP_QT - SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW )  >=0 THEN
        --	         ORDER_SLS_UNIT_QT
        --        ELSE
                       B.SHIP_QT 
        --        END
        ELSE    CASE  WHEN  B.SHIP_QT - SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW )  >=0 THEN
        	                 ORDER_SLS_UNIT_QT
                ELSE
                      CASE  WHEN  FIRST_VALUE(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW ) - 
                                  SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW ) = 0 AND
                                  FIRST_VALUE(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM   ORDER BY      NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS  BETWEEN UNBOUNDED  PRECEDING   AND   CURRENT ROW ) > 0 THEN
                                B.SHIP_QT 
                      ELSE    
                            CASE   WHEN
                                   B. SHIP_QT- SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM  ORDER BY   NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS    BETWEEN UNBOUNDED  PRECEDING   AND 1 PRECEDING)  >=0  THEN
                                                          B.SHIP_QT- SUM(A.ORDER_SLS_UNIT_QT) OVER (PARTITION BY A.SALES_ORGANIZATION_CD,A.SALES_ORDER_NO,A.SALES_ORDER_ITEM_NO,A.SOURCE_NM  ORDER BY    NVL(CURRENT_SCHEDULED_SHIP_DT,0),TO_NUMBER(A.SCHEDULE_LINE_NO)  ROWS    BETWEEN UNBOUNDED  PRECEDING   AND 1 PRECEDING)
                            ELSE 0    
                            END
                      END
                END
        END SHIP_QT
      FROM 
        OPEN_ORDER_DETAIL_TEST   A ,
        (SELECT     
                A.SALES_ORGANIZATION_CD,
                A.REFERENCE_DOCUMENT_NO SALES_ORDER_NO,
                A.REFRENCE_DOCUMENT_ITEM_NO SALES_ORDER_ITEM_NO,
                A.SOURCE_NM,
                SUM(A.ACTUAL_DELIVERED_QT) SHIP_QT 
         FROM 
                (Select /*+ Ordered Use_Nl(X) */ * From ShipDtl X
                   Where  REFERENCE_DOCUMENT_NO In 
                         (select /*+ ordered use_nl(x) */ distinct SALES_ORDER_NO 
                          from slsord x where (SALES_ORGANIZATION_CD, SALES_ORDER_NO ) in
                                              (select /*+ Parallel(X, 4) */ distinct SALES_ORGANIZATION_CD, reference_document_no from temp_shipdtl x)
                                         and   ORDER_STATUS_CD = '0'
                                  Union
                          Select /*+ Parallel(X, 4) */ Distinct Sales_Order_No From process_open_order X)
                ) A
         GROUP BY 
                A.SALES_ORGANIZATION_CD,
                A.REFERENCE_DOCUMENT_NO,
                A.REFRENCE_DOCUMENT_ITEM_NO,
                A.SOURCE_NM
        ) B
      WHERE
        A.SALES_ORGANIZATION_CD    =   B.SALES_ORGANIZATION_CD AND
        A.SALES_ORDER_NO           =   B.SALES_ORDER_NO AND
        A.SALES_ORDER_ITEM_NO      =   B.SALES_ORDER_ITEM_NO AND
        SUBSTR(A.SOURCE_NM,-3,3)   =   SUBSTR(B.SOURCE_NM,-3,3)
    ;
    
    And now
    
    Begin
      Merge /*+ Full(OOD) Parallel(OOD, 4) PQ_DISTRIBUTE(PROCESS_OOD HASH, HASH) */ Into OPEN_ORDER_DETAIL_TEST   OOD
      Using
      Shipment_Alloc_Ood_Vw Process_OOD
      On
      (
        OOD.SALES_ORGANIZATION_CD  =   PROCESS_OOD.SALES_ORGANIZATION_CD  And
        OOD.SALES_ORDER_NO         =   PROCESS_OOD.SALES_ORDER_NO         And
        OOD.SALES_ORDER_ITEM_NO    =   PROCESS_OOD.SALES_ORDER_ITEM_NO    And
        OOD.SCHEDULE_LINE_NO       =   PROCESS_OOD.SCHEDULE_LINE_NO       And
        OOD.SOURCE_NM              =   PROCESS_OOD.SOURCE_NM
      )
      When Matched Then
      Update Set
        OOD.SHIP_QT                =   PROCESS_OOD.SHIP_QT
      When Not Matched Then 
      Insert
      (
        OOD.SALES_ORGANIZATION_CD,
        OOD.SALES_ORDER_NO,
        OOD.SALES_ORDER_ITEM_NO,
        OOD.SCHEDULE_LINE_NO,
        OOD.SOURCE_NM
      )
      Values
      (
        null,
        null,
        null,
        null,
        null
      );
    End;
    /
    
    Started working !!!!
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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



Click Here to Expand Forum to Full Width