I am trying to execute some SQL on an Oracle 9i v9.2.0.5 system using the WITH clause to use subquery factoring. I get the following message from DataStage.
TestWithClause..ORA_LOOKUP: ORA-00904: "A_NAME": invalid identifier
TestWithClause..ORA_LOOKUP.DSLink1: DSP.Open GCI $DSP.Open error -2.
SQL is:
WITH A_NAME AS (
SELECT
EN.EXTERNAL_SYSTEM_NAME_ID
FROM EXTERNAL_SYSTEM_NAME EN
WHERE EN.NAME = 'ARBOR BP')
SELECT
EK.EXTERNAL_SYSTEM_KEY3, EK.TDW_TABLE_KEY1
FROM
EXTERNAL_SYSTEM_KEY EK
WHERE
EK.EXTERNAL_SYSTEM_NAME_ID = A_NAME
AND EK.EXTERNAL_SYSTEM_TABLE_NAME = 'BILL_INVOICE'
AND EK.EXTERNAL_SYSTEM_KEY3 IS NOT NULL
AND EK.TDW_TABLE_NAME = 'CMM'
Is it possable for DataSTage to utilise SQL clauses introduced in Oracle 9.2? If so, what am I missing?
TIA
Neil
Oracle 9.2.0.5 WITH clause fails
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Can't you re-cast your query as follows?
(or possibly IN rather than = to introduce the subquery)
Otherwise, what stage type are you using, and are you using generated or user-defined SQL?
Code: Select all
SELECT
EK.EXTERNAL_SYSTEM_KEY3, EK.TDW_TABLE_KEY1
FROM
EXTERNAL_SYSTEM_KEY EK
WHERE
EK.EXTERNAL_SYSTEM_NAME_ID = ( SELECT EXTERNAL_SYSTEM_NAME_ID
FROM EXTERNAL_SYSTEM_NAME EN
WHERE EN.NAME = 'ARBOR BP' )
AND EK.EXTERNAL_SYSTEM_TABLE_NAME = 'BILL_INVOICE'
AND EK.EXTERNAL_SYSTEM_KEY3 IS NOT NULL
AND EK.TDW_TABLE_NAME = 'CMM' ;
Otherwise, what stage type are you using, and are you using generated or user-defined SQL?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I am using an ORAOCI9 stage, and User Defined SQL query.
Yes, it could be coded like that, but I have been told (by someone who knows much more about Oracle than I do at this time) that the WHEN clause is more efficient than the current normal join coding.
Eg. SELECT ... FROM EK.key_table EN.name_table
Yes, it could be coded like that, but I have been told (by someone who knows much more about Oracle than I do at this time) that the WHEN clause is more efficient than the current normal join coding.
Eg. SELECT ... FROM EK.key_table EN.name_table
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Can't see it myself. Irrespective of the syntax the inner query must be executed and its result used in the outer query. The syntax (in this case) surely only affects where the value is stored.
I might be able to be convinced in the case of a correlated subquery, where some clever caching may be used, but even then I am skeptical. Can you post your Oracle expert's reasoning? (Or is it an unsupported assertion?)
I might be able to be convinced in the case of a correlated subquery, where some clever caching may be used, but even then I am skeptical. Can you post your Oracle expert's reasoning? (Or is it an unsupported assertion?)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
It is the caching of the WITH data that is the main bit. This query is evidently done only once per session.
With the query above that I am running now, it is returning all qualifying rows, so a correlated sub query would be just as good, or nearly so, I suspect. I am just using this as a test bed.
Other queries that result in a sigleton select from the KEY table (> 60M rows) will not need to access the NAME table (11 rows) to do each join or sub select. Over 60-70,000 queries this may make a reasonable difference. It may not, as well. Like you, I am a little sceptical yet.
I would still like to know why I can not run the code as written, even if it does not result in any performance gain. I will report back if I find the problem.
With the query above that I am running now, it is returning all qualifying rows, so a correlated sub query would be just as good, or nearly so, I suspect. I am just using this as a test bed.
Other queries that result in a sigleton select from the KEY table (> 60M rows) will not need to access the NAME table (11 rows) to do each join or sub select. Over 60-70,000 queries this may make a reasonable difference. It may not, as well. Like you, I am a little sceptical yet.
I would still like to know why I can not run the code as written, even if it does not result in any performance gain. I will report back if I find the problem.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Completely off topic
Constructing an (Oracle 9i) IMPORT script in Word, the spell checker changed TOUSER to TROUSER
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.