I can confirm. I copied and pasted Mike's Python verbatim, and got exactly same issues with generated SQL
On Thu, Apr 26, 2018 at 10:21 PM, Jeremy Flowers <[email protected] > wrote: > 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.
