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. 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 myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Multiple SQL in SQL Passthru

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Multiple SQL in SQL Passthru
 Login/Join
 
Member
posted
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>,


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report This Post
Virtuoso
posted Hide Post
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.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Gold member
posted Hide Post
try this

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


7.7.02 (Html, Excel, Ahtml, Pdf,Graph,.....)
OS400 V5R3, V5R4
Windows 2000/2003/2007/2010
 
Posts: 68 | Location: France | Registered: February 27, 2008Report This Post
Member
posted Hide Post
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!


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report This Post
Expert
posted Hide Post
Create a stored procedure?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Yes. Create a plpgsql stored procedure to provide data row with returns RECORD.


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report This Post
Member
posted Hide Post
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


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report This Post
Guru
posted Hide Post
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)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Guru
posted Hide Post
 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...


Thanks,

Ramkumar.
WebFOCUS/Tableau
Webfocus 8 / 7.7.02
Unix, Windows
HTML/PDF/EXCEL/AHTML/XML/HTML5
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Report This Post
Member
posted Hide Post
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)


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report This Post
Gold member
posted Hide Post
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


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Member
posted Hide Post
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


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report This Post
Member
posted Hide Post
How to read the content of HOLD or SAVE file for assigning to variable?


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report This Post
Gold member
posted Hide Post
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.


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Report This Post
Member
posted Hide Post
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


WebFOCUS 8007, Linux
 
Posts: 8 | Registered: May 13, 2014Report 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     [SOLVED] Multiple SQL in SQL Passthru

Copyright © 1996-2020 Information Builders