Re: Is there a better way to run SQL in a CL pgm?

A User Defined Function (UDF) created by CREATE FUNCTION GenHdgsCSV which RETURNS VARCHAR(6000) [or some similar large string] to replace the large string from the example: '"Cust0001-Name","...'.

The function could use a recursive query like the one I mentioned in a prior response to this message thread:
Date: Mon, 02 Apr 2012 13:40:25 -0700
http://archive.midrange.com/midrange-l/201204/msg00042.html

Or the function could use a CURSOR and FETCH the values to generate the CSV data for the "headings" [row one of the CSV exported data]. Birgitta has given examples on this list for similar "row-data into a concatenated string" UDFs; examples generating a list of values as a delimited string, extracted from column data fetched from rows matching a particular item [in this case, the item is the Library/FileName, and the rows are of column metadata stored in the catalogs].

In either case, the query utilized in the function would extract the row data from the SQL catalogs to obtain the information that describes the column; e.g. a query of data from SYSCOLUMNS for WHERE SYS_TNAME=FileName AND SYS_DNAME=LibName, for which LibName and FileName are the variables defined as input parameters to the UDF.

Regards, Chuck

On 04-Apr-2012 12:41 , Stone, Joel wrote:

Please explain "GenHdgsCSV" - is that part of SQL? Or how to build??

On Tuesday, April 03, 2012 12:03 PM CRPence wrote:

On 03-Apr-2012 08:41 , Stone, Joel wrote:

When building a list of field names, SQL stmts can get lengthy.
The longer each field name, the longer the SQL stmt.

Message . . . . : 11600 - RJSIMAGE/DOCSQL SQLSTM('insert into
qtemp/WORKHEADER values(''"Cust0001-Name","Cust0001-address-line1",
"Cust0001-address-line2","Cust0001-address-line3","CUST0001-CITY",
"...

Yuck. All the more reason to generate them directly from the
catalogs. Encapsulated in a function [as a FETCH cursor instead of
recursive query even], the above request could become [and once
again, eliminating some escaped apostrophes]:

SQLSTM('insert into qtemp/WORKHEADER
values(GenHdgsCSV(FileName,LibName))')