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

There are so many "special cases" that you'll find this difficult to effect
in real world environment.

As Charles points out, technically it is possible. Using dynamic SQL and
programming around special cases.

If all of the files that match your search have the same layout AND there
aren't exorbitant amount of hits on SSN wildcard, something like this will
work:

/* start of SQL script */

CREATE PROCEDURE getSsnContents (library varchar(10), ssn varchar(9))
RESULT SETS 1
LANGUAGE SQL
SET OPTION DBGVIEW=*SOURCE
BEGIN
DECLARE DYN_SQL VARCHAR(32739) DEFAULT '';
DECLARE unionAllCursor CURSOR FOR SQL_STATEMENT;
FOR cur_rec AS C1 CURSOR FOR
SELECT DBIFIL, DBIFLD
FROM QADBIFLD
WHERE DBIFLD LIKE '%SSN%' AND DBILIB = library
DO
SET DYN_SQL = DYN_SQL || ' UNION ALL ' || ' SELECT * FROM ' || LIBRARY
|| '/' || cur_rec.DBIFIL || ' WHERE ' || cur_rec.DBIFLD || ' = ' || SSN;
END FOR;
SET DYN_SQL = SUBSTR(DYN_SQL,12);
PREPARE SQL_STATEMENT FROM DYN_SQL;
OPEN unionAllCursor;
END;

CALL getSsnContents('MYLIBNAME','123456789');

/* end of SQL script */

Copy/paste the above script into iSeries Navigator's Run Sql Scripts
facility and click on Run->All (Ctrl->R keyboard shortcut).

If all stars are aligned, you'll get something back. Most likely though,
you'll get an error.

Elvis

Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp

-----Original Message-----
Subject: Using the contents of a filed for selection of the file

My question of today is, I know that in my library I have lots of files with
key field SSN in the text. I know from another thread that I can query the
QADBIFLD file and find all the files in that library that have "SSN" in the
text description.

I want to build a query that will do a select for each file found in
QADBIFLD and search for a SSN = 123456789, and do something with that. So
the field that contains the data for file name (dbifil) is what I want to
use. Something like;

With t1 as
(Select DBIFIL, DBIFLD
from QADBIFLD
where dbitxt like '%SSN%'
and DBILIB = 'MYLIBNAME')

Select * from (contents of dbifil) where (contents of dbifld) = 123456789

Is this do-able??

Jim