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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
I tried WITH clause for implicit temp table, it is nice feature when the result of the called function is data row for the temp table.
In this case, the result of the function is temp table name and the schema(columns and types) can be various depend on the input arguments. So, the called functions can not be defined with explicit result set schema(columns, types).
Now, I implemented a common wrapper function for returning anonymous type with RECORD and should specify schema at the calling SQL as below. A drawback of this approach is overhead to specify result data schema, and it should be exactly same columns and compatible data types with the result data.
With anonymous sql block (do block in Postgresql) might be helpful, but it is future story.
ENGINE SQLGPDB SET DEFAULT_CONNECTION isogpdbp1 SQL SQLGPDB PREPARE SQLOUT FOR
-* with wrapper function.
SELECT * from sb_devl_1.modcall_row( ... ) as temp_tab ( a char(4), b char(2), c char(4), d text, e int, h_claim int, i text, j text ) ;
TABLE FILE SQLOUT PRINT * END
It will be very appreciate if anybody can give me more easy ways.
You will to break your statement into two SQL calls:
ENGINE SQLGPDB SET DEFAULT_CONNECTION isogpdbp1_prod
SQL SQLGPDB -* first sql: call a function to prepare temp table select * from schma.test_func('arg1'); END SQL SQLGPDB PREPARE SQLOUT FOR -* second sql: get data from temp table select * from tmptab; END
-RUN TABLE FILE SQLOUT PRINT * END
The first one isn't returning any data so you don't need the "PREPARE SQLOUT FOR" section.
Basically eveytime you need to use a ";" in SQL you need to wrap it with SQL ... END tags. When you want output then use PREPARE SQLOUT FOR
There is a JSON adapter in WF8 - can't remember if it is in earlire versions.
StuThis message has been edited. Last edited by: StuBouyer,
WebFOCUS 8.2.03 (8.2.06 in testing)
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007
When we use two SQL commands and two '-RUN' commands, that mean two requests can be assigned to two separated database sessions. So, database session specific features such as temp table/view can not be available, shared between two sessions.
In my case, first sql create a temp table or view and the second sql need to query the table.
I think DO block(anonymous block of PostgreSQL) might be suitable for multiple SQL, but it is not available at current GP version.
I can create a database stored function to return one text column value(delimited text, or JSON text) which is combined with multiple columns values of the any temp table, and this function can be used as a wrapping function for temp table returning function.
Is there any reference how to convert a text(string) column value to multiple columns for just data dumping(HTMFORM, or CSV) ?
I have played around with temp tables in SQL, but it has been awhile. However, I did find this searching the IBI Tech Support site that might help you with the syntax.
The use of the PREPARE is not appropriate for non parameterized SQL. The use of TEMP Tables does not require the select be in the same SQL. It must be part of the same connection and in the absence of a commit the Temp Table is available for any subsequent SQL statements.
The following is an example of the creation of Temp Tables and the reporting of that table: SET SQLENGINE=SQLODBC SQL CREATE TEMP TABLE TEMP_FIRMS AS (SELECT * FROM BBAI.TNUM01); END -RUN SQL SELECT * FROM TEMP_FIRMS ; TABLE ON TABLE HOLD AS SALESMI_FIRMS END TABLE FILE SALESMI_FIRMS PRINT * END