the issue is that your original query ends with:
SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT *
FROM J1 ORDER BY DISP_SEQ
and I gave you:
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
I apologize for this mistake.
Basically it's not appropriate to use select.with_prefix() in this
particular case as it applies prefixes subsequent to the SELECT
keyword. The SQLAlchemy Oracle dialect does not currently have
support for special strings added in front of the SELECT keyword. I
can provide you with a recipe that allows for this to be possible,
however, as a practical matter, this query is Oracle-specific in any
case, is there a reason you can't just use text() ? The reason
text() exists is for when one has the exact SQL they want already and
there is no need to work it into the expression language.
If you want to continue using the expression language I can show you a
recipe to add those special keywords to the left side of the SELECT
keyword.
On Thu, Apr 26, 2018 at 5:33 PM, Mike Bayer <[email protected]> wrote:
> 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.
