Does JRS block the use of "WITH" common table expressions?
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
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
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
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.