merge with case and anlytical functions not working in pl/sql block

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.

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

its 9.2.0.4.. and yes i did use execute immediate also but no luck...

possibly a bug?

its mind blowing !!!

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

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

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.