Re: SQL auto-generates select list of fields for primary select

El Mon, 23 Jan 2017 14:48:32 -0500
Dan <dan27649@xxxxxxxxx> escribió:

I would search for this, but I have no idea of the search terms. I vaguely
recall a relatively new SQL function that could auto-generate a list of
fields that could be used in a select statement. So, for example,

SELECT *magiclistoffields* from ABCTABLE

*magiclistoffields* is a result of another select (lateral???) that you can
exclude certain fields from the list. If ABCTABLE has fields FLD1, FLD2,
FLD3, and FLD4, I would like to select only fields FLD3 and FLD4, so that
the above select example is rendered as

SELECT FLD3, FLD4 from ABCTABLE

FWIW, I'm on v7r1 with somewhat recent TRs.

Pipe dream or reality?

- Dan

A "one liner" from QShell (STRQSH).
Using the command "db2" and the result piped to awk.

db2 "select COLUMN_NAME from QSYS2.SYSCOLUMNS where TABLE_NAME='COUNTRIES' an

d TABLE_SCHEMA='MYLIBRARY' order by ORDINAL_POSITION;" | awk '/SELECTED\./ {print
f("select %s from COUNTRIES;\n",a); exit}; /-------/ {i=1; next}; i==1 && $1!
="" {gsub(/ *$/,""); a=a==""?$1:a","$1;};'
select NAME,CODE2,CODE3,NUME from COUNTRIES;
$

Here is the DDS:

*************** Beginning of data *************************************
0001.00 * - -
0002.00 A R COUNTRIESF
0003.00 * - -
0004.00 A NAME 48
0005.00 A CODE2 8
0006.00 A CODE3 8
0007.00 A NUME 3
0008.00 * - -
0009.00 A K CODE3
****************** End of data *************************************