Re: Using the contents of a filed for selection of the file

Defining "do something with that" can be interesting.
Let's take your first query (somewhat modified):
Select DBILIB, DBIFIL, DBIFLD, DBITXT
from QADBIFLD
where dbitxt like '%SSN%'

Consider the fact that if you have 27 logicals over a physical file with
SSN in it then you'll see all 28 hits.

Also consider that some of the hits may be numeric, some character - like
what I discovered on my system. So consider adding these fields: DBIITP,
DBITYP, DBINLN, DBINSC, DBICLN

Let's imbed it into a HLL (like rpg perhaps).
This will allow you to 'prepare' a sql statement on the fly to process
these requests
stmt1='declare c1 cursor for ' +
'select ' + dbifld + ' , rrn(' + dbifil + '), ' +
'from ' + %trim(dbilib) + '/' + %trim(dbifil) + ' ' +
'where ' + dbifld + ' = ';
// then figure out whether to append a numeric or character SSN.
exec sql prepare mysqlstmt from :stmt1;
exec sql execute mysqlstmt;
exec sql Open c1;

You could even get the key fields from QSYS2/SYSKEYS if you wanted to.


Rob Berendt