I hacked the parameterised query:
This works..
WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS (
(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS
jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS
jobmst_type, 1 AS lvl
FROM jobmst
WHERE jobmst.jobmst_prntid IS NULL
)
UNION ALL
SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
j1.lvl + 1 As lvl
FROM jobmst j2
JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
WHERE j2.jobmst_prntid IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type,
j1.lvl
FROM j1 ORDER BY DISP_SEQ
So, the AS names are ok it seems... But, the SELECT should come after
DISP_SEQ too
On Thu, Apr 26, 2018 at 10:17 PM, Jeremy Flowers <[email protected]
> wrote:
> *Mike I know what the problem is.*
> *The SQL emitted by SQLAlchemy is wrong!*
> This is why I get: *ORA-00923: FROM keyword not found where expected. *You
> only get error code when you try to execute the code - with first(), all(),
> slice()
> Look for the <----- in the generated code.... (x4)
>
> ====
> This is my Original SQL:
>
> WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (
> SELECT * FROM (
> SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
> FROM TIDAL.JOBMST
> WHERE JOBMST_PRNTID IS NULL
> )
> UNION ALL
> SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE,
> J1.LVL + 1
> FROM TIDAL.JOBMST J2
> INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
> WHERE J2.JOBMST_PRNTID IS NOT NULL
> )
> SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
> SELECT *
> FROM J1
> ORDER BY DISP_SEQ
>
> ===
>
> This is the code output by printing the Oracle dialect:
>
> WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS .
> <----- Look no opening parenthesis
> (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS
> jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS
> jobmst_type, :param_1 AS lvl
> FROM jobmst
> WHERE jobmst.jobmst_prntid IS NULL
>
> <!----- LOOK no closing parenthesis
> UNION ALL
> SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
> j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
> j1.lvl + :lvl_1 AS anon_1 . <!----- the AS names should match what's
> inside WITH()
> FROM jobmst j2
> JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
> WHERE j2.jobmst_prntid IS NOT NULL
> )
> SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id,
> j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl . <----- There
> should be no SELECT before SEARCH (my why prefix - should be suffix)
> FROM j1 ORDER BY DISP_SEQ
>
> ===
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
