Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Using WITH syntax in Oracle SQL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Using WITH syntax in Oracle SQL
 Login/Join
 
Joan Williamson-Kelly
Gold member
posted
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)
 
Posts: 64 | Location: Denver | Registered: July 20, 2005Report This Post
Tony A
Expert
posted Hide Post
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


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Joan Williamson-Kelly
Gold member
posted Hide Post
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.
 
Posts: 64 | Location: Denver | Registered: July 20, 2005Report This Post
Joan Williamson-Kelly
Gold member
posted Hide Post
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.
 
Posts: 64 | Location: Denver | Registered: July 20, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Using WITH syntax in Oracle SQL

Copyright © 1996-2020 Information Builders