Replacement for CONNECT BY PRIOR
Hi ..
We have an application 500+ sqls using CONNECT BY. (11g R2)
Now we have a plan to upgrade to 12c. And we want to come out of this CONNECT BY and use new syntax (Sub query factoring)
1. Default "_OLD_CONNECT_BY_ENABLED" = fasle, but we are setting this to "_OLD_CONNECT_BY_ENABLED" = true in 11g R2 to make sure CONNECT BY works properly.
2. If we want to use default settings of 12c i.e FALSE, the CONNECT BY causes any issues? Please advise.
3. We have understood that WITH clause (Subquery factoring) is the best replacement for CONNECT BY. Can anbody have some tips for this conversion? And please help us converting below sample Query using WITH clause, if possible.
Sample Query:
SELECT distinct asap.serv_item_rel.serv_item_id_rel
FROM asap.serv_item_rel
WHERE level > 0
START WITH asap.serv_item_rel.serv_item_id_rel = :al_serv_item_id_rel
CONNECT BY prior asap.serv_item_rel.serv_item_id_rel = asap.serv_item_rel.serv_item_id and
asap.serv_item_rel.serv_item_id_rel <> asap.serv_item_rel.serv_item_id
Thank you!
Best Regards
Siva