Re: SQL Search Source by Line Changed Date

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

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
> ;