Oracle syntax error when using "WITH temp_table AS"

Answered

IntelliJ IDEA 2016.3
Build #IU-163.7743.44, built on November 17, 2016

I have the following statement:

String selectStatement = "SELECT oi.order_id,\n" +
" to_char(oi.product_id) AS product_id,\n" +
" oi.specification_code AS product_spec_code,\n" +
" oi.offering_code AS product_offering_code,\n" +
" oi.apo_entity_action_code AS entity_action_code\n" +
" FROM t_oi oi\n" +
" WHERE oi.oi_id IN\n" +
" ( WITH w_resource_oi AS\n" +
" (SELECT oi5.oi_id \n" +
" FROM t_order o, t_order_status os, t_oi oi5, t_oi_res oires\n" +
" WHERE o.order_id = oi5.order_id\n" +
" AND o.order_status_code = os.order_status_code\n" +
" AND os.is_final = 0\n" +
" AND oires.resource_id = ?\n" +
" AND oires.oi_id = oi5.oi_id\n" +
" AND EXISTS\n" +
" (SELECT 1\n" +
" FROM t_oi_status_tml oistm\n" +
" WHERE oistm.oi_status_code = '105'\n" +
" AND oistm.oi_id IN\n" +
" (SELECT oi1.oi_id\n" +
" FROM t_oi oi1\n" +
" START WITH oi1.oi_id = oi5.oi_id\n" +
" CONNECT BY PRIOR oi1.oi_id = oi1.parent_id\n" +
" UNION\n" +
" SELECT oi2.oi_id\n" +
" FROM t_oi oi2\n" +
" START WITH oi2.oi_id = oi5.oi_id\n" +
" CONNECT BY PRIOR oi2.parent_id = oi2.oi_id)))\n" +
" SELECT oi6.oi_id\n" +
" FROM t_oi oi6\n" +
" START WITH oi6.oi_id IN (SELECT w_resource_oi.oi_id FROM w_resource_oi)\n" +
" CONNECT BY PRIOR oi6.oi_id = oi6.parent_id\n" +
" UNION\n" +
" SELECT oi7.oi_id\n" +
" FROM t_oi oi7\n" +
" START WITH oi7.oi_id IN (SELECT w_resource_oi.oi_id FROM w_resource_oi)\n" +
" CONNECT BY PRIOR oi7.parent_id = oi7.oi_id\n" +
" )\n";

Idea marks WITH in this line

        "       ( WITH w_resource_oi AS\n" +

as an error. I have dialect set to Oracle in the entire project. I guess that Idea doesn't know about this form of SQL. Do you plan to make Idea understand this?

0
1 comment

Hello Srecko,

 

Reported issue to YouTrack: https://youtrack.jetbrains.com/issue/DBE-3632

Please start following it to receive automatic notifications from YouTrack when the issue is updated.
See https://intellij-support.jetbrains.com/hc/en-us/articles/207241135-How-to-follow-YouTrack-issues-and-receive-notifications   if you are not familiar with YouTrack.

0

Please sign in to leave a comment.