Re: SQL Search Source by Line Changed Date

Glad you could test it and get it to work. Maybe it will be of value to you again.
It was educational to create, but it was a fair amount of trial and error because I couldn't find much documentation or examples.
And no debugger made it interesting. I'll have to investigate that more.

Sam

On 8/13/2020 9:33 AM, K Crawford wrote:

Sam, that was me asking about this.
I have tested it and it looks good.
I created the table and UDF.
Then created in ACS Run SQL Scripts Insert example. That way I can call it
back up when I want.
I did take it a bit further and added variables.
This is what my Insert example looks like.

-- Change these three temp variables to be what you want.
CREATE OR REPLACE VARIABLE kcrawford1.TmpSQLV01 CHAR(10) DEFAULT
'KCRAWFORD1' -- Name of library you want to search
;
CREATE OR REPLACE VARIABLE kcrawford1.TmpSQLV02 numeric(6) DEFAULT '200101'
-- From Date to Search YYMMDD
;
CREATE OR REPLACE VARIABLE kcrawford1.TmpSQLV03 numeric(6) DEFAULT '200104'
-- To Date to Search YYMMDD
;
-- Clear the work file from the last time.
cl:CLRPFM FILE(KCRAWFORD1/SRCFND)
;
-- The MBRS CTE gives, all source files (in library) and
-- the call to the function has a line changed date range.
-- it will use the variables from above.
WITH MBRS AS
(SELECT SYSTEM_TABLE_SCHEMA AS LIB,
SYSTEM_TABLE_NAME AS FILE,
SYSTEM_TABLE_MEMBER AS MEMBER
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_SCHEMA = kcrawford1.TmpSQLV01 AND SOURCE_TYPE IS NOT NULL
AND SOURCE_TYPE NOT IN ('PF', 'LF')
--EVFTEMPF is IBM file and contains bad SRCDAT data
AND SYSTEM_TABLE_NAME not like('EVFTEMPF%')
-- fetch first 5 rows only
)
SELECT lib, file, member,
kcrawford1.Srch_Src_line_date(
lib, file, member, kcrawford1.TmpSQLV02, kcrawford1.TmpSQLV03)
Found_Lines
FROM MBRS
ORDER BY Found_Lines desc, lib, file, member
;
-- =========+=========+=========+=========+=========+
--You can see the actual lines in SRCFND:
select *
from kcrawford1.srcfnd
;