Routine returning invalid results

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sbass1
Premium Member
Premium Member
Posts: 211
Joined: Wed Jan 28, 2009 9:00 pm
Location: Sydney, Australia

Routine returning invalid results

Post by sbass1 »

*** I've edited this post to include the entire routine and added the log results ***

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 ==================
While the code is somewhat long, conceptually it's (IMO) straightforward. In pseudocode:

* 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.
Everything works fine until Event ID 43. The problem Event ID's are 43, 49, 51, 57, 59, 60, 62, 66, 76, etc, etc. In other words, whenever "Audit" changes value.

But the sub-routine for GL4Product isn't doing any validation. It's just saving the value of "Temp" for further use! :shock:

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
But I'm not even making a call to the (custom) Input routine in this section of the code!!! :evil: Where in the world is DS getting this instruction from???

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
Last edited by sbass1 on Tue Apr 14, 2009 7:22 pm, edited 1 time in total.
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues :-)
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

Several comments. When all your logic is tied up in one routine then it gets more and more difficult to debug or fix anything that breaks. The concept of funneling all columns through one validation seems to be simple and powerful but becomes tedious. Why not validate each type in a separate routine. Lookup the type in some metadata driven concept. Therefore have a date validation routine and a separate one for decimal or integer.

The more complex or metadata driven your ETL the longer it takes to deliver. There maybe better reporting because of your better engineered solution but there is a trade off in development time as well as training developers your methodology. It may also slow down how fast your ETL can load. Every column has all a lookup on the type column it is and then validate that type instead of read, transform and load.
Mamu Kim
sbass1
Premium Member
Premium Member
Posts: 211
Joined: Wed Jan 28, 2009 9:00 pm
Location: Sydney, Australia

Post by sbass1 »

Thanks Kim, I appreciate the input.

Regardless of whether I walk away from this approach or not, does anyone have an idea why:

Code: Select all

GL4Product: 
   * No validation but need to save value of GL4Product for later use ; 
   GL4Product = Temp; 
RETURN(0) 
would turn:

Code: Select all

Audit=""
(as proven by debugging statements in the log)

to

Code: Select all

Audit="GL4Product;"
???
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues :-)
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Well... you are simply setting it to the value of "Temp" so whatever is in there at the time is what you end up with. Is this a leftover value from a previous step that did not get cleared perhaps? The only place I see that value is in DataColumns<36> but it is in the ValidateColumns array at another entry - <19>. Meant to be so? Perhaps concentrating around that element might help. :?

A couple of points. One is I haven't seen that syntax you're using for setting something to an "empty string", curious where you saw it. Rather than Audit = \\ I use a pair of quotes Audit = '' instead. Not saying that's an issue, just never seen it before anywhere that I recall and have no access to try it for myself.

I'm also leery of code postings where sections perceived to be "not related" to the issue at hand get snipped out. I've got nothing against long posts and have created more than my fair share here. Me, I'd much rather see everything... just in case.
-craig

"You can never have too many knives" -- Logan Nine Fingers
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Take your Routine and paste the logic into a Batch:: style job. Add DSLogInfo() lines to write output to the job log. For developing long routines or functions this can be a useful device. Once you figure out where your logic goes bad, you'll be able to fix your actual Routine.[/u]
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
sbass1
Premium Member
Premium Member
Posts: 211
Joined: Wed Jan 28, 2009 9:00 pm
Location: Sydney, Australia

Post by sbass1 »

Hi Craig,
chulett wrote:Well... you are simply setting it to the value of "Temp" so whatever is in there at the time is what you end up with. Is this a leftover value from a previous step that did not get cleared perhaps? The only place I see that value is in DataColumns<36> but it is in the ValidateColumns array at another entry - <19>. Meant to be so? Perhaps concentrating around that element might help. :?

A couple of points. One is I haven't seen that syntax you're using for setting something to an "empty string", curious where you saw it. Rather than Audit = \\ I use a pair of quotes Audit = '' instead. Not saying that's an issue, just never seen it before anywhere that I recall and have no access to try it for myself.

I'm also leery of code postings where sections perceived to be "not related" to the issue at hand get snipped out. I've got nothing against long posts and have created more than my fair share here. Me, I'd much rather see everything... just in case.
1) I've edited the orginal post along with comments. See if that helps clarify things. But, in summary, Temp is the data value from the input line, while Audit is a concatenation of the current column (not Temp) if the validation fails.

2) Array element location is irrelevant.

3) The Basic Guide states:
The empty string is a special instance of character string data. It is a character string of zero length. Two adjacent double or single quotation marks, or backslashes, specify an empty string:

'' or "" or \\
It's strictly a coding style/preference - I find \\ easier to see when I'm skimming code. That's the only reason I'm using it really. Perhaps I should get new glasses :wink:

4) You've got the whole lot now :wink: :shock:

Thanks for the help!!!

Scott
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues :-)
sbass1
Premium Member
Premium Member
Posts: 211
Joined: Wed Jan 28, 2009 9:00 pm
Location: Sydney, Australia

Post by sbass1 »

kcbland wrote:Take your Routine and paste the logic into a Batch:: style job. Add DSLogInfo() lines to write output to the job log. For developing long routines or functions this can be a useful device. Once you figure out where your logic goes bad, you'll be able to fix your actual Routine.
Thanks Ken, I'll try this. I do have a lot of debugging stubs in my current code, the log of which I've copied into the (edited) original post. But I'll see if what you suggest makes a different.

I assume a "Batch style job" is a blank job with all the Basic code in the Job Control tab. No reply needed (well, they're never needed, are they, this is an all-volunteer forum :wink: ) unless this assumption is incorrect.

Thanks for the reply...
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues :-)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

A regular server job with code posted into the Job Control tab will suffice.

A Batch style job is the same thing, but created in Director (which means it gets "Batch::" prefixed to its name and it gets compiled automatically). Batch is a way to allow operators (who don't have access to Designer) to create a sequential sequence of jobs.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Kryt0n
Participant
Posts: 584
Joined: Wed Jun 22, 2005 7:28 pm

Post by Kryt0n »

The fact GL4Product is missing from your GoSub labels isn't helping.

From what I can see is happening

Code: Select all

For I=1 To DCount(ValidateColumns,@AM) 
For each column in your ValidateColumns list, you want to execute the respective Sub procedure. As such, you need to make sure your ValidateColumns list is in the same order as your GoSub labels.

Code: Select all

      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 
So when your process hits GL4Product, I is set to 19 which is then executing the 19th label in the list, this should be GL4Product to process correctly
sbass1
Premium Member
Premium Member
Posts: 211
Joined: Wed Jan 28, 2009 9:00 pm
Location: Sydney, Australia

Post by sbass1 »

Kryt0n wrote:The fact GL4Product is missing from your GoSub labels isn't helping.
Thanks Kryt0n. Yeah, I found this about 10 mins ago. Was confirming before posting again.

I discovered this by putting additional debugging stubs in the GL4Product subroutine, then noticing they weren't echoed in the log.

Well, don't I feel like an idiot :oops: . Apologies for wasting anyone's time. A real "bite you in the bum" error. If we learn the most from the mistakes we make, then I learned a lot from this one!

If there is a way to call a subroutine by the runtime value of a variable, i.e. (pseudocode)

foo = "MyRoutine"
GoSub $foo

please let me know, although I assume no.
Last edited by sbass1 on Tue Apr 14, 2009 9:13 pm, edited 1 time in total.
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues :-)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Code: Select all

foo = "DSU.MyRoutine"
Call @foo(Result, other_args)
The technique is called "indirect call". And it's in the DataStage BASIC manual. I've used it in the past to create data-driven business rules validation.
For functions the first argument position is reserved for the return value from the function, hence the Result placeholder above.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sbass1
Premium Member
Premium Member
Posts: 211
Joined: Wed Jan 28, 2009 9:00 pm
Location: Sydney, Australia

Post by sbass1 »

ray.wurlod wrote:

Code: Select all

foo = "DSU.MyRoutine"
Call @foo(Result, other_args)
The technique is called "indirect call". And it's in the DataStage BASIC manual. I've used it in the past to create data-driven business rules validation.
For functions the first argument position is reserved for the return value from the function, hence the Result placeholder above.
Is this just for external routines?

IOW, can I replace my code (snippet):

Code: Select all

For I=1 To DCount(ValidateColumns,@AM)
   Column = ValidateColumns<I>
   On I GoSub SourceSystem, EffectiveDate, ExtractDateTime, ...
with:

Code: Select all

For I=1 To DCount(ValidateColumns,@AM)
   Column = ValidateColumns<I>
   GoSub @Column
I suppose I could just 1) try it, or 2) read the Basic manual...
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues :-)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Only for external routines.

Remember what Kim advised about modularization? This is one of the reasons why.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply