Hello Experts,
When I use the following sql, it works without an error.
INSERT /*+ append */ INTO deneme
with
main_q AS (
SELECT CONNECT_BY_ROOT avl.leaderid to_memberid, avl.leaderid leaderid, avl.memberid memberid, LEVEL level_, CUST.RECID,
CASE WHEN PRIOR CUST.RECID IS NULL THEN (SELECT RECID FROM CUSTTABLE C_TEMP WHERE substr(nls_lower(C_TEMP.DATAAREAID),1,7) = '201' and
substr(nls_lower(C_TEMP.ACCOUNTNUM),1,25) = AVL.LEADERID) ELSE PRIOR CUST.RECID END leader_recid
FROM avlmembers avl JOIN custtable cust
ON SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = SUBSTR(NLS_LOWER(CUST.DATAAREAID),1,7)
AND SUBSTR(NLS_LOWER(AVL.MEMBERID),1,25) = SUBSTR(NLS_LOWER(CUST.ACCOUNTNUM),1,25)
WHERE SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = '201'
--AND CONNECT_BY_ROOT avl.leaderid != '0' AND leaderid != '0'
AND (
(CONNECT_BY_ISLEAF = 0 AND SUBSTR (NLS_LOWER (AVL.MEMBERSTATUS), 1, 21) IN ('new','active')
AND SUBSTR (NLS_LOWER (AVL.AVPAIDROLEID), 1, 21) != '0_rep' AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN ('a', 'i', 'p'))
OR (CONNECT_BY_ISLEAF = 1 AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN ('a', 'i', 'p', 'r')))
CONNECT BY NOCYCLE PRIOR AVL.MEMBERID = AVL.LEADERID),
addr AS (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID FROM (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID, row_number() OVER(PARTITION BY ADDR.ADDRRECID ORDER BY ADDR.TYPE) sıra FROM address addr
WHERE SUBSTR(NLS_LOWER(ADDR.DATAAREAID),1,7) = '201' AND ((ADDR.TYPE = 2 AND ADDR.ADDRESS != CHR(2)) OR (ADDR.TYPE = 17))
) WHERE sıra = 1)
SELECT '201405' avcampaignid,
main_q.to_memberid, main_q.leaderid, main_q.memberid, level_, main_q.recid, main_q.leader_recid, addr.STATE, addr.COUNTY, addr.AVCITYID,
addr2.STATE l_state, addr2.COUNTY l_county, addr2.AVCITYID l_avcityid
FROM main_q JOIN addr ON main_q.recid = addr.addrrecid
LEFT JOIN addr addr2 ON main_q.leader_recid = addr2.addrrecid;
31 rows added
However, when I use it between BEGIN END block, it gives an error (ORA-06550). This is so weird, isn't it?
begin
INSERT /*+ append */ INTO deneme
with
main_q AS (
SELECT CONNECT_BY_ROOT avl.leaderid to_memberid, avl.leaderid leaderid, avl.memberid memberid, LEVEL level_, CUST.RECID,
CASE WHEN PRIOR CUST.RECID IS NULL THEN (SELECT RECID FROM CUSTTABLE C_TEMP WHERE substr(nls_lower(C_TEMP.DATAAREAID),1,7) = '201' and
substr(nls_lower(C_TEMP.ACCOUNTNUM),1,25) = AVL.LEADERID) ELSE PRIOR CUST.RECID END leader_recid
FROM avlmembers avl JOIN custtable cust
ON SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = SUBSTR(NLS_LOWER(CUST.DATAAREAID),1,7)
AND SUBSTR(NLS_LOWER(AVL.MEMBERID),1,25) = SUBSTR(NLS_LOWER(CUST.ACCOUNTNUM),1,25)
WHERE SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = '201'
--AND CONNECT_BY_ROOT avl.leaderid != '0' AND leaderid != '0'
AND (
(CONNECT_BY_ISLEAF = 0 AND SUBSTR (NLS_LOWER (AVL.MEMBERSTATUS), 1, 21) IN ('new','active')
AND SUBSTR (NLS_LOWER (AVL.AVPAIDROLEID), 1, 21) != '0_rep' AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN ('a', 'i', 'p'))
OR (CONNECT_BY_ISLEAF = 1 AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN ('a', 'i', 'p', 'r')))
CONNECT BY NOCYCLE PRIOR AVL.MEMBERID = AVL.LEADERID),
addr AS (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID FROM (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID, row_number() OVER(PARTITION BY ADDR.ADDRRECID ORDER BY ADDR.TYPE) sıra FROM address addr
WHERE SUBSTR(NLS_LOWER(ADDR.DATAAREAID),1,7) = '201' AND ((ADDR.TYPE = 2 AND ADDR.ADDRESS != CHR(2)) OR (ADDR.TYPE = 17))
) WHERE sıra = 1)
SELECT '201405' avcampaignid,
main_q.to_memberid, main_q.leaderid, main_q.memberid, level_, main_q.recid, main_q.leader_recid, addr.STATE, addr.COUNTY, addr.AVCITYID,
addr2.STATE l_state, addr2.COUNTY l_county, addr2.AVCITYID l_avcityid
FROM main_q JOIN addr ON main_q.recid = addr.addrrecid
LEFT JOIN addr addr2 ON main_q.leader_recid = addr2.addrrecid;
end;
ORA-06550: satır 3, sütun 43:
PL/SQL: ORA-00600: dahili hata kodu, bağımsız değişkenler: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []
ORA-06550: satır 3, sütun 17:
PL/SQL: SQL Statement ignored
The most interesting thing is if the third join is commented, it also works without an error.
begin
INSERT /*+ append */ INTO deneme
with
main_q AS (
SELECT CONNECT_BY_ROOT avl.leaderid to_memberid, avl.leaderid leaderid, avl.memberid memberid, LEVEL level_, CUST.RECID,
CASE WHEN PRIOR CUST.RECID IS NULL THEN (SELECT RECID FROM CUSTTABLE C_TEMP WHERE substr(nls_lower(C_TEMP.DATAAREAID),1,7) = '201' and
substr(nls_lower(C_TEMP.ACCOUNTNUM),1,25) = AVL.LEADERID) ELSE PRIOR CUST.RECID END leader_recid
FROM avlmembers avl JOIN custtable cust
ON SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = SUBSTR(NLS_LOWER(CUST.DATAAREAID),1,7)
AND SUBSTR(NLS_LOWER(AVL.MEMBERID),1,25) = SUBSTR(NLS_LOWER(CUST.ACCOUNTNUM),1,25)
WHERE SUBSTR(NLS_LOWER(AVL.DATAAREAID),1,7) = '201'
--AND CONNECT_BY_ROOT avl.leaderid != '0' AND leaderid != '0'
AND (
(CONNECT_BY_ISLEAF = 0 AND SUBSTR (NLS_LOWER (AVL.MEMBERSTATUS), 1, 21) IN ('new','active')
AND SUBSTR (NLS_LOWER (AVL.AVPAIDROLEID), 1, 21) != '0_rep' AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN ('a', 'i', 'p'))
OR (CONNECT_BY_ISLEAF = 1 AND SUBSTR(NLS_LOWER(CUST.AVACCOUNTSTATUS),1,21) IN ('a', 'i', 'p', 'r')))
CONNECT BY NOCYCLE PRIOR AVL.MEMBERID = AVL.LEADERID),
addr AS (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID FROM (
SELECT STATE, COUNTY, AVCITYID, ADDRRECID, row_number() OVER(PARTITION BY ADDR.ADDRRECID ORDER BY ADDR.TYPE) sıra FROM address addr
WHERE SUBSTR(NLS_LOWER(ADDR.DATAAREAID),1,7) = '201' AND ((ADDR.TYPE = 2 AND ADDR.ADDRESS != CHR(2)) OR (ADDR.TYPE = 17))
) WHERE sıra = 1)
SELECT '201405' avcampaignid,
main_q.to_memberid, main_q.leaderid, main_q.memberid, level_, main_q.recid, main_q.leader_recid, addr.STATE, addr.COUNTY, addr.AVCITYID,
--addr2.STATE l_state, addr2.COUNTY l_county, addr2.AVCITYID l_avcityid
'asd', 'asd', 'asd'
FROM main_q JOIN addr ON main_q.recid = addr.addrrecid;
--LEFT JOIN addr addr2 ON main_q.leader_recid = addr2.addrrecid;
end;
pl/sql procedure successfully completed
First time I encountered this kind of behaviour, what should I do?
Regards
Charlie