It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×42,880
×6,037
×359
×42
×2
×1

question asked: Oct 31 '18, 5:19 p.m.
question was seen: 2,538 times
last updated: Nov 01 '18, 4:28 p.m.

Related questions

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

0
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

0
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.

Your answer

Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.