Forked from BirgittaHauser/How to read source files with SQL
Created
September 4, 2022 13:56
-
-
Save dancarlosgabriel/571db94c1b8efe467483b991a7758aad to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Here is a function whith wich you can read your source files directly with SQL: | |
-- 1.1. Parameters: | |
-- ParSrcMbr Source Member Name | |
-- ParSrcFile Source File | |
-- ParSrcLib Source Library | |
-- ---------------------------------------------------------------* | |
Create Or Replace Function YourSchema.PH_SrcMbr | |
(ParSrcMbr VarChar(10) , | |
ParSrcFile VarChar(10) , | |
ParSrcLib VarChar(10)) | |
Returns Table (SrcSeq Dec(6, 2) , | |
SrcDat Dec(6, 0) , | |
SrcDta Char(132)) | |
Language SQL | |
Specific PH_SrcMbr | |
Not Deterministic | |
Modifies SQL Data | |
Called On NULL Input | |
Disallow Parallel | |
Not Fenced | |
Set Option DBGVIEW = *SOURCE , | |
DATFMT = *ISO , | |
TIMFMT = *ISO | |
Begin | |
Declare LocSQLCmd VarChar(1024) Default ''; | |
Declare Continue Handler for SQLState '42704' Begin End; | |
Declare Continue Handler for SQLException | |
Begin | |
Declare ErrText VarChar(128) Not Null Default ''; | |
Get Diagnostics Condition 1 ErrText = MESSAGE_TEXT; | |
Signal SQLState 'PH001' Set MESSAGE_TEXT = ErrText; | |
End; | |
Set (ParSrcMbr, | |
ParSrcFile, | |
ParSrcLib) = (Upper(Trim(ParSrcMbr)), | |
Upper(Trim(ParSrcFile)), | |
Upper(Trim(ParSrcLib))); | |
Drop Alias QTEMP.TMPSRCMBR; | |
Set LocSQLCmd = 'CREATE OR REPLACE ALIAS QTEMP.TMPSRCMBR FOR ' concat | |
Trim(ParSrcLib) concat '.' concat | |
Trim(ParSrcFile) concat '(' concat | |
Trim(ParSrcMbr) concat ')'; | |
Execute Immediate LocSQLCmd; | |
Return Select SrcSeq, SrcDat, SrcDta | |
From QTEMP.TMPSRCMBR; | |
End ; | |
Label on Specific Function YourSchema.PH_SrcMbr | |
is 'Read Source Physical File Member'; | |
Comment on Specific Function YourSchema.PH_SrcMbr | |
is 'Read Source Physical File Member'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment