Hi,

Is there any way to use Multiple SQL in SQL Passthru ?
I need to create a temp table by using a stored function and get data from the temp table as below.

ENGINE SQLGPDB SET DEFAULT_CONNECTION isogpdbp1_prod

SQL SQLGPDB PREPARE SQLOUT FOR

-* first sql: call a function to prepare temp table
select * from schma.test_func('arg1');

-* second sql: get data from temp table
select * from tmptab;

-RUN
TABLE FILE SQLOUT
PRINT *
END

Thanks,
Taewon

This message has been edited. Last edited by: <Kathryn Henning>,

Wild guess here: Create a synonym for the tmptab table ... you may have to run the proc first to establish it. Then, add a MFD_PROFILE to the tmptab synonym that runs the stored proc or whatever.

try this

with temptab as (select * from schma.test_func('arg1'))
select * from temptab

Hi Cati, Thanks a lot.

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

-RUN

TABLE FILE SQLOUT
PRINT *
END

It will be very appreciate if anybody can give me more easy ways.

Happy Friday!

Create a stored procedure?

Yes. Create a plpgsql stored procedure to provide data row with returns RECORD.

A wrapper stored function can return a text column data in JSON format. So, result row is a JSON text.

Is there any idea to this JSON data for reporting?

Taewon

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.

Cheers

Stu

This message has been edited. Last edited by: StuBouyer,

WebFOCUS 8.2.03 (8.2.06 in testing)

 SET SQLENGINE=SQLORA

ENGINE SQLORA SET DEFAULT_CONNECTION CON01
Query 1
END

-RUN

SQL SQLORA
Query 2
END
-RUN 

Will this do the trick? 2 SQL calls for 2 queries...

HI Ramkumar,

Thank you for your reply.

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

Regards,
Taewon (WF Beginner)

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

Hi JRLewis,

Thank you so much for helpful advice.

I solved this case as below sample code.

-* set default SQLENGINE.
SET SQLENGINE=SQLGPDB

-* set default connection of the sqlengine.
ENGINE SQLGPDB SET DEFAULT_CONNECTION CON1

-* set default value of local amper variable, may be set by caller.

-DEFAULT &PARM1 = 'param1'
-DEFAULT &PARM2 = 2

SQL
-* return a temp table name
SELECT afunc as temp_table_name FROM aschema.afunc( "&PARAM1", &PARAM2);
TABLE
ON TABLE SAVE AS SQLOUTTMP
END

-* How to get the content of SQLOUTTMP ?
-SET &TEMP_TABLE_NAME = 'temp_tab'

SQL
SELECT * FROM &TEMP_TABLE_NAME;
TABLE
ON TABLE HOLD AS SQLOUT
END

SQL
DROP TABLE IF EXISTS &TEMP_TABLE_NAME;
END

SET PAGE-NUM = OFF
TABLE FILE SQLOUT
PRINT *
END

How to read the content of HOLD or SAVE file for assigning to variable?

If you are on WF 7.7.x or higher, you can use -READFILE, or in any version you can use -READ. You can look them up in the documentation, and there are plenty of examples on Focal Point.

Thank you, JRLewis,

Referenced below link for reading hold file.

http://forums.informationbuild...957077036#5957077036


Thanks everybody who help me to solve this case.
Taewon