Posted: Sun Aug 24, 2003 7:09 am
Here's the subroutine for converting a reverse sorted and grouped comma separated sequential file of natural to surrogate key columns described in the previous post. You could paste this logic into a subroutine. The InputArg value is the fully qualified sequential file name with a ".???" extension. The logic removes the current extension and adds ".rlp".
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!
Kenneth Bland
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