Hi,
This post is related to viewtopic.php?t=126507. Scroll down to my second post in that thread.
First of all, apologies for the long post. I'd considered putting the code on Google docs, but due to the nature of the problem, decided to post it here.
I've coded this validation routine for my incoming data. The data is passed to the routine as one long delimited string, i.e. "as is" from the source file without parsing by the seq file stage. The output from the routine is meant to be all the columns that failed validation, in a concatenated string.
OK, here's the code.
Code: Select all
* Compile options
$DEFINE DEBUG
* Declare function
DEFFUN Input(pInputValue, pInputPattern) Calling "DSU.DEV.SB.Input"; * DEV
DEFFUN IsCharInRange(pString, pRange) Calling "DSU.DEV.SB.IsCharInRange"; * DEV
DEFFUN Put(pInputValue, pInputPattern) Calling "DSU.DEV.SB.Put"; * DEV
* DEFFUN Input(pInputValue, pInputPattern) Calling "DSU.Input"; * PRD
* DEFFUN IsCharInRange(pString, pRange) Calling "DSU.IsCharInRange"; * PRD
* DEFFUN Put(pInputValue, pInputPattern) Calling "DSU.Put"; * PRD
* Declare constants
Equate cRoutineName To "ValidateSourceSystemData"
* Check that the input arguments are both assigned
* Otherwise return immediately.
If UnAssigned(pDataLine) Or IsNull(pDataLine) Or UnAssigned(pDelimiter) Or IsNull(pDelimiter)
Then
GoSub ErrorInvalidArguments
RETURN(@NULL)
End
* Copy arguments to local variables
DataLine = pDataLine
Delimiter = pDelimiter
* Build list of source input columns. This needs to be in sync (positionally) with the columns in the source data.
* Build list in COMMON variable so that it is only built once.
COMMON /ValidateSourceSystemData/ DataColumns
If DataColumns = 0
Then
DataColumns = \\
GoSub BuildDataColumnsList
End
$IFDEF DEBUG
DataColumns = \\
GoSub BuildDataColumnsList
call DSLogInfo("DataColumns=":@FM:DataColumns,cRoutineName)
$ENDIF
* Build list of columns to be validated
COMMON /ValidateSourceSystemData/ ValidateColumns
If ValidateColumns = 0
Then
ValidateColumns = \\
GoSub BuildValidateColumnsList
End
$IFDEF DEBUG
ValidateColumns = \\
GoSub BuildValidateColumnsList
call DSLogInfo("ValidateColumns=":@FM:ValidateColumns,cRoutineName)
$ENDIF
* Now validate desired columns. The return value is a list of all invalid columns as a text string.
Audit = \\
For I=1 To DCount(ValidateColumns,@AM)
Column = ValidateColumns<I>
FindStr Column In DataColumns Setting Pos
Then
Temp = Field(DataLine,Delimiter,Pos)
* Convert StringIsSpace to Null since all the subroutines use IsNull check
If Not(IsNull(Temp)) and (len(Trim(Temp, " ", "A")) = 0) Then Temp = @NULL
$IFDEF DEBUG
call DSLogInfo("Column=":Column:" Pos=":Pos:" Temp=":If IsNull(Temp) Then "Null" Else "Not Null", cRoutineName)
$ENDIF
On I GoSub SourceSystem, EffectiveDate, ExtractDateTime, BackdatedBalanceDate, CustomerNumber, CustomerNumberIsCentralCustomerNumber, CustomerIsHuman, CustomerABN, CustomerACN, CustomerDateOfBirth, CustomerPrimaryName, CustomerSex, CustomerAPRACode, CustomerAccountOwnershipPercent, CustomerAccountIsJoint, GL4Account, GL4BusinessUnit, GL4ReportingEntity, GL4Currency, AccountNumber, AccountName, AccountProductEligible, AccountBusinessArea, AccountBookName, AccountIsInternal, AccountDealType, AccountOpeningDate, AccountOptedInOut, AccountOptedDate, AccountGGLClass, AccountGGLSubclass, AccountEODBalanceNaturalCcy, IssuerLegalName, IssuerCountry, IssuerABN, IssuerACN, TermFundingDepositoryName, TermFundingIssuanceProgramName, TermFundingIssuanceSize, TermFundingIssuedSecurityID, TermFundingIssuanceDate, TermFundingMaturityDate, TermFundingCouponRate, TermFundingCouponType, TermFundingCouponFrequency, TermFundingCouponReference, TermFundingInterestRate, TermFundingISIN, TermFundingGGCertificate, GGSTreatment, MatchedCustomerNumber, Misc1
$IFDEF DEBUG
call DSLogInfo("Audit=":If IsNull(Audit) Then "Null" Else Audit, cRoutineName)
$ENDIF
End
Else
GoSub ErrorColumnName
RETURN(@NULL)
End
Next I
If Len(Audit) > 0 Then Ans = "Failed Constraints: ":Audit
$IFDEF DEBUG
call DSLogInfo("Final Ans=":If IsNull(Ans) Then "Null" Else Ans, cRoutineName)
$ENDIF
RETURN(Ans)
* ========== MAIN SUBROUTINES ===================
SourceSystem: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
EffectiveDate: ; * Mandatory
If IsNull(Put(Input(Temp,"YYYYMMDD"),"YYYYMMDD")) Then Audit := DataColumns<Pos>:"; "
EffectiveDate = Temp; * used later
RETURN(Audit)
ExtractDateTime: ; * Mandatory
If IsNull(Put(Input(Temp,"YYYYMMDDHHMMSS"),"YYYYDDMMHHMMSS-:")) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
BackdatedBalanceDate: ; * Mandatory
If IsNull(Temp) or Temp > EffectiveDate Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
CustomerNumber: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
CustomerNumberIsCentralCustomerNumber: ; * Mandatory
If IsNull(Temp) or Not(Temp Matches "T":@VM:"F") Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
CustomerIsHuman:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "T":@VM:"F") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
CustomerABN:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "11N") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
CustomerACN:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "9N") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
CustomerDateOfBirth:
If Not(IsNull(Temp))
Then
If IsNull(Put(Input(Temp,"YYYYMMDD"),"YYYYMMDD")) Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
CustomerPrimaryName: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
CustomerSex:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "M":@VM:"F") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
CustomerAPRACode:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "4N") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
CustomerAccountOwnershipPercent: ; * Mandatory
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
Temp = iconv(Temp,"MD2") / 100
If Temp < 0 or Temp > 1 Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
CustomerAccountIsJoint: ; * Mandatory
If IsNull(Temp) or Not(Temp Matches "T":@VM:"F") Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
GL4Account: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
GL4BusinessUnit: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
GL4ReportingEntity: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
GL4Product:
* No validation but need to save value of GL4Product for later use ;
GL4Product = Temp;
RETURN(0)
GL4Currency: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountNumber: ; * Mandatory
If IsNull(Temp) or Not(IsCharInRange(Temp, "a-z, A-Z, 0-9")) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountName: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountProduct:
* No validation but need to save value of AccountProduct for later use ;
AccountProduct = Temp;
RETURN(0)
AccountProductEligible: ; * Mandatory
If Not(IsNull(Temp))
Then
If Not(Temp Matches "T":@VM:"F") Then Audit := DataColumns<Pos>:"; "
End
AccountEligible = (Temp eq "T"); * used later
RETURN(Audit)
AccountBusinessArea: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountBookName: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountIsInternal:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "T":@VM:"F") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
AccountDealType: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountOpeningDate: ; * Mandatory
If IsNull(Put(Input(Temp,"YYYYMMDD"),"YYYYMMDD")) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountOptedInOut:
* Mandatory if AccountEligible = "T"
If AccountEligible
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
* And must be "I" or "O"
If Not(IsNull(Temp))
Then
If Not(Temp Matches "I":@VM:"O") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
AccountOptedDate:
* Mandatory if AccountEligible = "T"
If AccountEligible
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
* And must be YYYYMMDD
If Not(IsNull(Temp))
Then
If IsNull(Put(Input(Temp,"YYYYMMDD"),"YYYYMMDD")) Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
AccountGGLClass: ; * Mandatory
If IsNull(Temp) or Not(Temp Matches "Deposit":@VM:"Other Short-term":@VM:"Term") Then Audit := DataColumns<Pos>:"; "
TermFunding = (Temp Matches "Term":@VM:"Other Short-term"); * used later
LongTermFunding = (Temp Matches "Term"); * used later
RETURN(Audit)
AccountGGLSubclass: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
AccountEODBalanceNaturalCcy: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
IssuerLegalName: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
IssuerCountry: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
IssuerABN:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "11N") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
IssuerACN:
If Not(IsNull(Temp))
Then
If Not(Temp Matches "9N") Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
TermFundingDepositoryName:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingIssuanceProgramName:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingIssuanceSize:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingIssuedSecurityID:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingIssuanceDate:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
* And must be YYYYMMDD
If Not(IsNull(Temp))
Then
If IsNull(Put(Input(Temp,"YYYYMMDD"),"YYYYMMDD")) Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
TermFundingMaturityDate:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
* And must be YYYYMMDD
If Not(IsNull(Temp))
Then
If IsNull(Put(Input(Temp,"YYYYMMDD"),"YYYYMMDD")) Then Audit := DataColumns<Pos>:"; "
End
RETURN(Audit)
TermFundingCouponRate:
* Mandatory if LongTermFunding
If LongTermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingCouponType:
* Mandatory if LongTermFunding
If LongTermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingCouponFrequency:
* Mandatory if LongTermFunding
If LongTermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingCouponReference:
* Mandatory if LongTermFunding
If LongTermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingInterestRate:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingISIN:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
TermFundingGGCertificate:
* Mandatory if TermFunding
If TermFunding
Then
If IsNull(Temp)
Then
Audit := DataColumns<Pos>:"; "
RETURN(Audit)
End
End
RETURN(Audit)
GGSTreatment:
If IsNull(Temp) Then Temp = 0; * NullToZero
If Not(Temp Matches "0":@VM:"1") Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
MatchedCustomerNumber: ; * Mandatory
If IsNull(Temp) Then Audit := DataColumns<Pos>:"; "
RETURN(Audit)
Misc1:
* Either GL4Product or AccountProduct must be specified
If IsNull(GL4Product) and IsNull(AccountProduct) Then Audit := "Either GL4Product or AccountProduct must be specified; "
RETURN(Audit)
* ========== END MAIN SUBROUTINES ===================
* ========== UTILITY SUBROUTINES ===================
* List of columns in the source data
BuildDataColumnsList:
DataColumns<-1> = "SourceSystem"
DataColumns<-1> = "EffectiveDate"
DataColumns<-1> = "ExtractDateTime"
DataColumns<-1> = "BackdatedBalanceDate"
DataColumns<-1> = "CustomerNumber"
DataColumns<-1> = "CustomerNumberIsCentralCustomerNumber"
DataColumns<-1> = "CustomerIsHuman"
DataColumns<-1> = "CustomerABN"
DataColumns<-1> = "CustomerACN"
DataColumns<-1> = "CustomerDateOfBirth"
DataColumns<-1> = "CustomerPrimaryName"
DataColumns<-1> = "CustomerFirstName"
DataColumns<-1> = "CustomerOtherNames"
DataColumns<-1> = "CustomerSex"
DataColumns<-1> = "CustomerCountryOfDomicile"
DataColumns<-1> = "CustomerUnitNumber"
DataColumns<-1> = "CustomerStreetNumber"
DataColumns<-1> = "CustomerStreet"
DataColumns<-1> = "CustomerSuburb"
DataColumns<-1> = "CustomerCity"
DataColumns<-1> = "CustomerState"
DataColumns<-1> = "CustomerPostcode"
DataColumns<-1> = "CustomerHomePhone"
DataColumns<-1> = "CustomerWorkPhone"
DataColumns<-1> = "CustomerMobilePhone"
DataColumns<-1> = "CustomerFax"
DataColumns<-1> = "CustomerEmail"
DataColumns<-1> = "CustomerAPRACode"
DataColumns<-1> = "CustomerAccountOwnershipPercent"
DataColumns<-1> = "CustomerAccountIsJoint"
DataColumns<-1> = "GL4Account"
DataColumns<-1> = "GL4BusinessUnit"
DataColumns<-1> = "GL4ReportingEntity"
DataColumns<-1> = "GL4Dept"
DataColumns<-1> = "GL4Location"
DataColumns<-1> = "GL4Product"
DataColumns<-1> = "GL4Project"
DataColumns<-1> = "GL4AffiliateBusinessUnit"
DataColumns<-1> = "GL4AffiliateReportingEntity"
DataColumns<-1> = "GL4Currency"
DataColumns<-1> = "AccountNumber"
DataColumns<-1> = "AccountName"
DataColumns<-1> = "AccountProduct"
DataColumns<-1> = "AccountProductEligible"
DataColumns<-1> = "AccountBusinessArea"
DataColumns<-1> = "AccountBookName"
DataColumns<-1> = "AccountIsInternal"
DataColumns<-1> = "AccountDealType"
DataColumns<-1> = "AccountOpeningDate"
DataColumns<-1> = "AccountOptedInOut"
DataColumns<-1> = "AccountOptedDate"
DataColumns<-1> = "AccountGGLClass"
DataColumns<-1> = "AccountGGLSubclass"
DataColumns<-1> = "AccountEODBalanceNaturalCcy"
DataColumns<-1> = "IssuerLegalName"
DataColumns<-1> = "IssuerCountry"
DataColumns<-1> = "IssuerABN"
DataColumns<-1> = "IssuerACN"
DataColumns<-1> = "TermFundingDepositoryName"
DataColumns<-1> = "TermFundingIssuanceProgramName"
DataColumns<-1> = "TermFundingIssuanceSize"
DataColumns<-1> = "TermFundingIssuedSecurityID"
DataColumns<-1> = "TermFundingIssuanceDate"
DataColumns<-1> = "TermFundingMaturityDate"
DataColumns<-1> = "TermFundingCouponRate"
DataColumns<-1> = "TermFundingCouponType"
DataColumns<-1> = "TermFundingCouponFrequency"
DataColumns<-1> = "TermFundingCouponReference"
DataColumns<-1> = "TermFundingInterestRate"
DataColumns<-1> = "TermFundingISIN"
DataColumns<-1> = "TermFundingGGCertificate"
DataColumns<-1> = "GGSTreatment "
DataColumns<-1> = "MatchedCustomerNumber"
DataColumns<-1> = "Misc1"
RETURN(0)
* List of columns to be validated in the source data
BuildValidateColumnsList:
ValidateColumns<-1> = "SourceSystem"
ValidateColumns<-1> = "EffectiveDate"
ValidateColumns<-1> = "ExtractDateTime"
ValidateColumns<-1> = "BackdatedBalanceDate"
ValidateColumns<-1> = "CustomerNumber"
ValidateColumns<-1> = "CustomerNumberIsCentralCustomerNumber"
ValidateColumns<-1> = "CustomerIsHuman"
ValidateColumns<-1> = "CustomerABN"
ValidateColumns<-1> = "CustomerACN"
ValidateColumns<-1> = "CustomerDateOfBirth"
ValidateColumns<-1> = "CustomerPrimaryName"
ValidateColumns<-1> = "CustomerSex"
ValidateColumns<-1> = "CustomerAPRACode"
ValidateColumns<-1> = "CustomerAccountOwnershipPercent"
ValidateColumns<-1> = "CustomerAccountIsJoint"
ValidateColumns<-1> = "GL4Account"
ValidateColumns<-1> = "GL4BusinessUnit"
ValidateColumns<-1> = "GL4ReportingEntity"
ValidateColumns<-1> = "GL4Product"
ValidateColumns<-1> = "GL4Currency"
ValidateColumns<-1> = "AccountNumber"
ValidateColumns<-1> = "AccountName"
ValidateColumns<-1> = "AccountProduct"
ValidateColumns<-1> = "AccountProductEligible"
ValidateColumns<-1> = "AccountBusinessArea"
ValidateColumns<-1> = "AccountBookName"
ValidateColumns<-1> = "AccountIsInternal"
ValidateColumns<-1> = "AccountDealType"
ValidateColumns<-1> = "AccountOpeningDate"
ValidateColumns<-1> = "AccountOptedInOut"
ValidateColumns<-1> = "AccountOptedDate"
ValidateColumns<-1> = "AccountGGLClass"
ValidateColumns<-1> = "AccountGGLSubclass"
ValidateColumns<-1> = "AccountEODBalanceNaturalCcy"
ValidateColumns<-1> = "IssuerLegalName"
ValidateColumns<-1> = "IssuerCountry"
ValidateColumns<-1> = "IssuerABN"
ValidateColumns<-1> = "IssuerACN"
ValidateColumns<-1> = "TermFundingDepositoryName"
ValidateColumns<-1> = "TermFundingIssuanceProgramName"
ValidateColumns<-1> = "TermFundingIssuanceSize"
ValidateColumns<-1> = "TermFundingIssuedSecurityID"
ValidateColumns<-1> = "TermFundingIssuanceDate"
ValidateColumns<-1> = "TermFundingMaturityDate"
ValidateColumns<-1> = "TermFundingCouponRate"
ValidateColumns<-1> = "TermFundingCouponType"
ValidateColumns<-1> = "TermFundingCouponFrequency"
ValidateColumns<-1> = "TermFundingCouponReference"
ValidateColumns<-1> = "TermFundingInterestRate"
ValidateColumns<-1> = "TermFundingISIN"
ValidateColumns<-1> = "TermFundingGGCertificate"
ValidateColumns<-1> = "GGSTreatment"
ValidateColumns<-1> = "MatchedCustomerNumber"
ValidateColumns<-1> = "Misc1"
RETURN(0)
* ========== END UTILITY SUBROUTINES ================
* ========== ERROR SUBROUTINES ======================
ErrorInvalidArguments:
Call DSTransformError("Invalid argument(s).", cRoutineName)
Ans = @NULL
RETURN(Ans)
ErrorColumnName:
Call DSTransformError(Column:" is not a defined column in this routine.", cRoutineName)
Ans = @NULL
RETURN(Ans)
* ========== END ERROR SUBROUTINES ==================
* Declare any other routines used
* Parse arguments and abort if not correct
* Load two dynamic arrays in COMMON storage:
- DataColums is a list of all columns in the incoming data. This array serves as a "pointer" to the field element in the incoming data. Although long, it's actually quite easy to maintain - just cut and paste the columns from the source data into the code.
- ValidateColumns is a list of all columns that need validation. This array allows looping constructs in the code execution and actually makes the code shorter and more modular (the "guts" of the code is in the individual subroutines).
* Loop over the columns to be validated. Lookup the column to be processed. Get the index "Pos" (field element number) from the lookup.
* Parse the incoming data, copying the data element to Temp. Change empty string to NULL (simulate "Pad with SQL NULL" from the sequential file stage).
* Call the subroutine based on the current element in the column to be validated.
* If the column fails validation, append the column name to the Audit field. The column name is the current dynamic array element from the previous lookup.
The subroutines do include repeated code (cut and paste job), something I usually hate. But I decided to treat each validation as a discrete unit in case the validation rules changed in the future.
So, after all that is in place, and you're (I'm) comfortable that the looping constructs are working correctly, you just concentrate on the validation code in the subroutines. Ok, an oversimplification, but generally correct.
Here is the log for the active stage calling the validation routine. I've grepped out "User" and "Time" to make it a bit shorter. I also changed the debugging statement to only show "Null" or "Not Null" for "Temp", which is the data value from the parsed input line. The parsing of the input line is working fine.
Code: Select all
Event Id: 1
Type : STARTED
Message :
Starting Job ValidateSourceSystemData_NEW.
pColumnCount = 73
Event Id: 3
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints: DSD.StageRun Active stage starting, tracemode = 0.
Event Id: 4
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): DataColumns=
SourceSystem
EffectiveDate
BackdatedBalanceDate
CustomerNumber
CustomerNumberIsCentralCustomerNumber
CustomerIsHuman
CustomerABN
CustomerACN
CustomerDateOfBirth
CustomerPrimaryName
CustomerFirstName
CustomerOtherNames
CustomerSex
CustomerCountryOfDomicile
CustomerUnitNumber
CustomerStreetNumber
CustomerStreet
CustomerSuburb
CustomerCity
CustomerState
CustomerPostcode
CustomerHomePhone
CustomerWorkPhone
CustomerMobilePhone
CustomerFax
CustomerEmail
CustomerAPRACode
CustomerAccountOwnershipPercent
CustomerAccountIsJoint
GL4Account
GL4BusinessUnit
GL4ReportingEntity
GL4Dept
GL4Location
GL4Product
GL4Project
GL4AffiliateBusinessUnit
GL4AffiliateReportingEntity
GL4Currency
AccountNumber
AccountName
AccountProduct
AccountProductEligible
AccountBusinessArea
AccountBookName
AccountIsInternal
AccountDealType
AccountOpeningDate
AccountOptedInOut
AccountOptedDate
AccountGGLClass
AccountGGLSubclass
AccountEODBalanceNaturalCcy
IssuerLegalName
IssuerCountry
IssuerABN
IssuerACN
TermFundingDepositoryName
TermFundingIssuanceProgramName
TermFundingIssuanceSize
TermFundingIssuedSecurityID
TermFundingIssuanceDate
TermFundingMaturityDate
TermFundingCouponRate
TermFundingCouponType
TermFundingCouponFrequency
TermFundingCouponReference
TermFundingInterestRate
TermFundingISIN
TermFundingGGCertificate
GGSTreatment
MatchedCustomerNumber
Misc1
Event Id: 5
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): ValidateColumns=
SourceSystem
EffectiveDate
BackdatedBalanceDate
CustomerNumber
CustomerNumberIsCentralCustomerNumber
CustomerIsHuman
CustomerABN
CustomerACN
CustomerDateOfBirth
CustomerPrimaryName
CustomerSex
CustomerAPRACode
CustomerAccountOwnershipPercent
CustomerAccountIsJoint
GL4Account
GL4BusinessUnit
GL4ReportingEntity
GL4Product
GL4Currency
AccountNumber
AccountName
AccountProduct
AccountProductEligible
AccountBusinessArea
AccountBookName
AccountIsInternal
AccountDealType
AccountOpeningDate
AccountOptedInOut
AccountOptedDate
AccountGGLClass
AccountGGLSubclass
AccountEODBalanceNaturalCcy
IssuerLegalName
IssuerCountry
IssuerABN
IssuerACN
TermFundingDepositoryName
TermFundingIssuanceProgramName
TermFundingIssuanceSize
TermFundingIssuedSecurityID
TermFundingIssuanceDate
TermFundingMaturityDate
TermFundingCouponRate
TermFundingCouponType
TermFundingCouponFrequency
TermFundingCouponReference
TermFundingInterestRate
TermFundingISIN
TermFundingGGCertificate
GGSTreatment
MatchedCustomerNumber
Misc1
Event Id: 6
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=SourceSystem Pos=1 Temp=Not Null
Event Id: 7
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 8
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=EffectiveDate Pos=2 Temp=Not Null
Event Id: 9
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 10
Type : INFO
Message :
Event Id: 11
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 12
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=BackdatedBalanceDate Pos=4 Temp=Not Null
Event Id: 13
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 14
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerNumber Pos=5 Temp=Not Null
Event Id: 15
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 16
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerNumberIsCentralCustomerNumber Pos=6 Temp=Not Null
Event Id: 17
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 18
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerIsHuman Pos=7 Temp=Null
Event Id: 19
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 20
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerABN Pos=8 Temp=Null
Event Id: 21
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 22
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerACN Pos=9 Temp=Null
Event Id: 23
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 24
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerDateOfBirth Pos=10 Temp=Null
Event Id: 25
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 26
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerPrimaryName Pos=11 Temp=Not Null
Event Id: 27
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 28
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerSex Pos=14 Temp=Null
Event Id: 29
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 30
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerAPRACode Pos=28 Temp=Not Null
Event Id: 31
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 32
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerAccountOwnershipPercent Pos=29 Temp=Not Null
Event Id: 33
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 34
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=CustomerAccountIsJoint Pos=30 Temp=Not Null
Event Id: 35
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 36
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=GL4Account Pos=31 Temp=Not Null
Event Id: 37
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 38
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=GL4BusinessUnit Pos=32 Temp=Not Null
Event Id: 39
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 40
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=GL4ReportingEntity Pos=33 Temp=Not Null
Event Id: 41
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=
Event Id: 42
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=GL4Product Pos=36 Temp=Null
Event Id: 43
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product;
Event Id: 44
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=GL4Currency Pos=40 Temp=Not Null
Event Id: 45
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product;
Event Id: 46
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountNumber Pos=41 Temp=Not Null
Event Id: 47
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product;
Event Id: 48
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountName Pos=42 Temp=Not Null
Event Id: 49
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName;
Event Id: 50
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountProduct Pos=43 Temp=Null
Event Id: 51
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct;
Event Id: 52
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountProductEligible Pos=44 Temp=Not Null
Event Id: 53
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct;
Event Id: 54
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountBusinessArea Pos=45 Temp=Not Null
Event Id: 55
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea;
Event Id: 56
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountBookName Pos=46 Temp=Not Null
Event Id: 57
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea;
Event Id: 58
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountIsInternal Pos=47 Temp=Not Null
Event Id: 59
Type : WARNING
Message :
ValidateSourceSystemData_NEW..Constraints (Input) transform error: Input Value F does not match expected Input Pattern YYYYMMDD
ValidateSourceSystemData_NEW..Constraints.Extract: Row 1: DataLine="EDITED LINE FROM LOG"
Event Id: 60
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal;
Event Id: 61
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountDealType Pos=48 Temp=Not Null
Event Id: 62
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType;
Event Id: 63
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountOpeningDate Pos=49 Temp=Not Null
Event Id: 64
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType;
Event Id: 65
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountOptedInOut Pos=50 Temp=Not Null
Event Id: 66
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut;
Event Id: 67
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountOptedDate Pos=51 Temp=Not Null
Event Id: 68
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut;
Event Id: 69
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountGGLClass Pos=52 Temp=Not Null
Event Id: 70
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut;
Event Id: 71
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountGGLSubclass Pos=53 Temp=Not Null
Event Id: 72
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut;
Event Id: 73
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountEODBalanceNaturalCcy Pos=54 Temp=Not Null
Event Id: 74
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut;
Event Id: 75
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=IssuerLegalName Pos=55 Temp=Not Null
Event Id: 76
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName;
Event Id: 77
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=IssuerCountry Pos=56 Temp=Not Null
Event Id: 78
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 79
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=IssuerABN Pos=57 Temp=Not Null
Event Id: 80
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 81
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=IssuerACN Pos=58 Temp=Null
Event Id: 82
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 83
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingDepositoryName Pos=59 Temp=Null
Event Id: 84
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 85
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingIssuanceProgramName Pos=60 Temp=Null
Event Id: 86
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 87
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingIssuanceSize Pos=61 Temp=Null
Event Id: 88
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 89
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingIssuedSecurityID Pos=62 Temp=Null
Event Id: 90
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 91
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingIssuanceDate Pos=63 Temp=Not Null
Event Id: 92
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 93
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingMaturityDate Pos=64 Temp=Not Null
Event Id: 94
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 95
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingCouponRate Pos=65 Temp=Not Null
Event Id: 96
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 97
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingCouponType Pos=66 Temp=Not Null
Event Id: 98
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 99
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingCouponFrequency Pos=67 Temp=Not Null
Event Id: 100
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 101
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingCouponReference Pos=68 Temp=Null
Event Id: 102
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 103
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingInterestRate Pos=69 Temp=Null
Event Id: 104
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 105
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingISIN Pos=70 Temp=Null
Event Id: 106
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry;
Event Id: 107
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=TermFundingGGCertificate Pos=71 Temp=Null
Event Id: 108
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry; TermFundingGGCertificate;
Event Id: 109
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=GGSTreatment Pos=72 Temp=Null
Event Id: 110
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry; TermFundingGGCertificate;
Event Id: 111
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=MatchedCustomerNumber Pos=73 Temp=Not Null
Event Id: 112
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry; TermFundingGGCertificate;
Event Id: 113
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=Misc1 Pos=74 Temp=Null
Event Id: 114
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Audit=GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry; TermFundingGGCertificate;
Event Id: 115
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Final Ans=Failed Constraints: GL4Product; AccountName; AccountProduct; AccountBusinessArea; AccountIsInternal; AccountDealType; AccountOptedInOut; IssuerLegalName; IssuerCountry; TermFundingGGCertificate;
Event Id: 116
Type : INFO
Message :
ValidateSourceSystemData_NEW..Source_SourceSystemData.Extract: Run stopped after 1 rows
Event Id: 117
Type : INFO
Message :
ValidateSourceSystemData_NEW..Constraints: DSD.StageRun Active stage finishing.
1 rows read from Extract
1 rows written to Load
0.080 CPU seconds used, 0.000 seconds elapsed.
But the sub-routine for GL4Product isn't doing any validation. It's just saving the value of "Temp" for further use!
Same thing for AccountName
Code: Select all
ValidateSourceSystemData_NEW..Constraints (ValidateSourceSystemData): Column=AccountIsInternal Pos=47 Temp=F
ValidateSourceSystemData_NEW..Constraints (Input) transform error: Input Value F does not match expected Input Pattern YYYYMMDD
Now it's entirely possible I have logic errors in my code that are causing these false positives. However, I'm just not seeing where the problem could be, esp. Event ID's 43 and 59. I hope it's just something simple I'm doing wrong but I'm at my wits' end trying to work out why DS is doing this. I've restarted my clients, but haven't done anything on the server side. Could it have something to do with the COMMON storage for the dynamic arrays?
Thoughts? And apologies again for the long post.
Thanks,
Scott