I'm looking for advice about sending SQL to an Oracle database using WITH syntax. Here's what it would look like.

WITH tempname as (
select field1, field2, field3
from tablea
)
select field1, field2, field3
from tempname
;

Is WITH syntax supported?

I got past an error message that said

(FOC1400) SQLCODE IS 32034 (HEX: 00007D22)
: ORA-32034: unsupported use of WITH clause

But now it doesn't seem to recognize fieldnames in the SQLOUT file.

(FOC003) THE FIELDNAME IS NOT RECOGNIZED: field1
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

(error message modified to fit limited example above)

Joan,

There are actually two methods of using SQL within WF. The first actually uses the MFD (aka .mas) and has restricted SQL functionality. The second is passed to the RDBMS unchanged (?) and should have the functionality of the RDBMS SQL. So (not knowing Oracle SQL) if WITH is valid in Oracle then you should be able to use it.

The main difference between the two SQL approaches are that the RDBMS one has to have the ENGINE settings prior to its use.

For an example of the .mas route, try this in a fex on its own -

SQL
  SELECT COUNTRY
       , CAR
       , MODEL
       , SUM(DEALER_COST) AS DCOST
       , SUM(RETAIL_COST) AS RCOST
    FROM CAR
GROUP BY COUNTRY
       , CAR
       , MODEL
ORDER BY COUNTRY
       , CAR
       , MODEL
;
TABLE ON TABLE HOLD
END
-RUN

TABLE FILE HOLD
PRINT *
END

T

I do wish they would make a view for me.
They are saying that if they make one, they would make a "realized" view - but that would take more space than they want to use.

I will see if we can negotiate.

The DBAs decided to do some optimization on the database. I think we also got some SQL to run that did not use the 'WITH' syntax.