|
-
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"
-
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?
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote