This subroutine could be called in the after routine of the job that selects the rows and outputs to the sequential file. You could then load this data into a hash file for reference purposes. IMPORTANT! Dates should be in YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS format!
Code: Select all
FunctionName = "KBASkeyRollup"
RollupFileDtl = InputArg
RollupFile = RollupFileDtl[1,LEN(RollupFileDtl)-3]:"rlp"
Call DSLogInfo("Opening detail file [":RollupFileDtl:"]", FunctionName)
OPENSEQ RollupFileDtl TO S.Detail Else
Call DSLogFatal("Unable to open detail file: ":RollupFileDtl, FunctionName)
End
Call DSLogInfo("Opened detail file [":RollupFileDtl:"]", FunctionName)
Call DSLogInfo("Opening rollup file [":RollupFile:"]", FunctionName)
ValidOpen = @FALSE
OPENSEQ RollupFile TO S.Rollup Then
ValidOpen = @TRUE
End Else
CREATE S.Rollup Then ValidOpen = @TRUE
End
If NOT(ValidOpen) Then
Call DSLogFatal("Could not create the file: ":RollupFile, FunctionName)
End
Call DSLogInfo("Opened rollup file [":RollupFile:"]", FunctionName)
RowCount = 0
OutputRowCount = 0
RollupRow = ""
LastRollupID = "LAST"
Done = @FALSE
Loop
READSEQ row FROM S.Detail Else Done = @TRUE
Until Done Do
RowCount += 1
source_system_code = field(row,',',1)
source_system_key = field(row,',',2)
surrogate_key = field(row,',',3)
time_variant_start_date = field(row,',',4)
time_variant_version_nbr = field(row,',',5)
RollupID = field(row,',',1,2)
If LastRollupID = "LAST" Then LastRollupID = RollupID
If RollupID # LastRollupID Then
OutputRowCount += 1
CONVERT @AM TO "," IN RollupRow
WRITESEQ RollupRow ON S.Rollup Else
Call DSLogFatal("Unable to write to file ":RollupFile, FunctionName)
END
RollupRow = ""
LastRollupID = RollupID
End
If RollupRow = "" Then Delimiter = "" Else Delimiter = "|"
RollupRow = source_system_code
RollupRow = source_system_key
RollupRow = RollupRow:Delimiter:surrogate_key
RollupRow = RollupRow:Delimiter:time_variant_start_date
RollupRow = RollupRow:Delimiter:time_variant_version_nbr
Repeat
If RollupRow # "" Then
OutputRowCount += 1
CONVERT @AM TO "," IN RollupRow
WRITESEQ RollupRow ON S.Rollup Else
Call DSLogFatal("Unable to write to file ":RollupFile, FunctionName)
END
End
WEOFSEQ S.Rollup
CLOSESEQ S.Rollup
CLOSESEQ S.Detail
Call DSLogInfo("Finished creating rollup file: [":RollupFile:"]", FunctionName)
Call DSLogInfo("Input Rows Processed: [":RowCount:"]", FunctionName)
Call DSLogInfo("Output Rows Written: [":OutputRowCount:"]", FunctionName)
ErrorCode = 0 ; * set this to non-zero to stop the stage/job