Does JRS block the use of "WITH" common table expressions?

Jason Spegal (21●1●4) | asked Oct 31 '18, 5:19 p.m.

Trying to create a recursive query using common table expressions in JRS and I keep getting crrgw5628e an com.foundationdb.sql.parser sqlparseexception error occurred when validating the input SQL string, caused by encountered " "with" "WITH"" at line 1, column 1.

The simplest query I can do is:

WITH cte AS

(

SELECT REQUEST1_ID, REQUEST2_ID

FROM RIDW.VW_REQUEST_RELATIONAL_LINK

)

SELECT * FROM cte

We're running Oracle 11.2, and near as I can tell it should be working. I've had other issues in the past with using "WITH" in a query through JRS. Any ideas?

One answer

Most liked answers ↓|Newest answers|Oldest answers

permanent link

Rafik Jaouani (5.0k●1●6) | answered Nov 01 '18, 10:34 a.m.
JAZZ DEVELOPER

JRS uses an open source library for SQL parsing. This is used mainly for injecting/enforcing read access control.

So currently the parser does not accept the WITH flavor of SELECT nesting. You need to use:

SELECT * FROM (

SELECT REQUEST1_ID, REQUEST2_ID

FROM RIDW.VW_REQUEST_RELATIONAL_LINK

) as cte

Comments

Jason Spegal commented Nov 01 '18, 3:34 p.m.

So how would I do a recursive query with this limitation?

WITH cte (REQUEST1_ID,REQUEST2_ID) AS

{

SELECT REQUEST1_ID,REQUEST2_ID

FROM RIDW.VW_REQUEST_RELATIONAL_LINK

UNION ALL

SELECT

p.REQUEST1_ID AS base,

c.REQUEST2_ID AS REQUEST2_ID,

c.REQUEST1_ID AS REQUEST1_ID

FROM cte p, RIDW.VW_REQUEST_RELATIONAL_LINK c

WHERE p.REQUEST2_ID = c.REQUEST1_ID

}

SELECT

T1.REQUEST1_ID AS base,

T1.REQUEST2_ID AS REQUEST2_ID,

T1.REQUEST1_ID AS REQUEST1_ID

FROM cte T1

Rafik Jaouani commented Nov 01 '18, 4:28 p.m.

JAZZ DEVELOPER

May be try to create a table view in your schema that you can use to hide the complexity of your SQL and have the report select against the view. This could be a workaround for your issue.

It's all about the answers!

Related questions

Does JRS block the use of "WITH" common table expressions?

One answer

Your answer