Just write your own function.
The following function will return all columns of the passed table or view
within the specified schema in a string separated by commas:
CREATE OR REPLACE FUNCTION HSCOMMON10/LISTCOL (
PARTABLE VARCHAR(128) ,
PARSCHEMA VARCHAR(128) )
RETURNS VARCHAR(4096)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
BEGIN
DECLARE RETURNVAL VARCHAR ( 4096 ) NOT NULL DEFAULT ' ' ;
SET ( PARTABLE , PARSCHEMA ) = ( UPPER ( PARTABLE ) , UPPER ( PARSCHEMA )
) ;
FOR CSRC01 AS C1 CURSOR
FOR SELECT COLUMN_NAME
FROM SYSCOLUMNS
WHERE TABLE_NAME = PARTABLE
AND TABLE_SCHEMA = PARSCHEMA
DO SET RETURNVAL = RETURNVAL CONCAT ', ' CONCAT CSRC01.COLUMN_NAME ;
END FOR ;
RETURN TRIM ( TRIM(LEADING ',' FROM TRIM (RETURNVAL) ) );
END ;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Montag, 23. Januar 2017 21:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL auto-generates select list of fields for primary select
As it happens, I am already generating the first part of the SQL statement
in a variable in a CLLE program, for which the RUNSQL statement will use to
execute. I need to get the output of the "system_column_name from
syscolumns", normally appearing as
FLD3
FLD4
to appear as
FLD3, FLD4
I know there's a way to do that, just need to find the right function or
keyword.
- Dan
On Mon, Jan 23, 2017 at 2:59 PM, Rob Berendt <rob@xxxxxxxxx> wrote:
Keeping in mind that you can do
select system_column_name
from syscolumns
where system_table_name='MYFILE' and system_table_scheme='MYLIB' and
system_column_name not in('PAYRATE', 'SSN')
perhaps you can figure out some way to concatenate that list. Someone
recently did something with XML in SQL that blew my mind.
But the long slow painful way in RPG could be done and then you could
store your sql command string in a variable, use PREPARE and EXECUTE
(or EXECUTE IMMEDIATE) and get your desired statement.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
