OK, your original SQL works, will find the difference On Thu, Apr 26, 2018 at 5:32 PM, Mike Bayer <[email protected]> wrote: > Jeremy - > > This whole thread is based on an exact SQL that you have asked for, > and that was the query I gave you. Going back to the script I > posted, the SQL output when I run it via query.all() vs. just > query.statement.compile() is identical except for the label names > applied to the final column names, which is not the cause of this > error. > > please give me **the exact SQL statement that you know runs > correctly** and I will show you how to render it. > > > On Thu, Apr 26, 2018 at 5:30 PM, Jeremy Flowers > <[email protected]> wrote: >> Does this need to be submitted as an error on Git somewhere? >> >> On Thu, Apr 26, 2018 at 10:29 PM, Jeremy Flowers >> <[email protected]> wrote: >>> >>> 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.
-- 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.
