Oracle 9.2.0.5 WITH clause fails

[Neil C]

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

[ray.wurlod]

Can't you re-cast your query as follows?

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' ;

(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?

[Neil C]

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

[ray.wurlod]

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?)

[Neil C]

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.

[ray.wurlod]

Constructing an (Oracle 9i) IMPORT script in Word, the spell checker changed TOUSER to TROUSER