Checking for duplicates in SQR

I am currently trying to update an SQR to check for duplicate values across columns within a given record. We are allowing duplicates to be entered into the table, but we want an error flag to be checked as it breaks the next program.

ID - 1 Var1 - 3809 Var2 - 3809
ID - 2 Var1 - NULL Var2 - NULL
ID - 3 Var1 - 5204 Var2 - 3809

In this case, ID 1 would have an error flag checked, and the other two would not. I cannot simply check across values, as I will need to check across 12 different columns. Any help would be much appreciated.

Thanks! Dan

I tried the code below, which compiles, but does not record duplicates

begin-procedure check-duplicates

create-array name=SchlDuplicates
    size=15
Field=Val:Char

BEGIN-SQL 

SELECT
MICA_SCH1_CEEBCD
MICA_SCH2_CEEBCD
MICA_SCH3_CEEBCD
MICA_SCH4_CEEBCD
MICA_COL1_CEEBCD
MICA_COL2_CEEBCD
MICA_COL3_CEEBCD
MICA_TRSCH1_CEEBCD
MICA_TRCOL1_CEEBCD
MICA_TRCOL2_CEEBCD
MICA_TRCOL3_CEEBCD
MICA_TSSCH1_CEEBCD
MICA_TSSCH2_CEEBCD 
MICA_TSSCH3_CEEBCD


    put  MICA_SCH1_CEEBCD into SchlDuplicates(1) Val
    put  MICA_SCH2_CEEBCD into SchlDuplicates(2) Val
    put  MICA_SCH3_CEEBCD into SchlDuplicates(3) Val
    put  MICA_SCH4_CEEBCD into SchlDuplicates(4) Val
    put  MICA_COL1_CEEBCD into SchlDuplicates(5) Val
    put  MICA_COL2_CEEBCD into SchlDuplicates(6) Val
    put  MICA_COL3_CEEBCD into SchlDuplicates(7) Val
    put  MICA_TRSCH1_CEEBCD into SchlDuplicates(8) Val
    put  MICA_TRCOL1_CEEBCD into SchlDuplicates(9) Val
    put  MICA_TRCOL2_CEEBCD into SchlDuplicates(10) Val
    put  MICA_TRCOL3_CEEBCD into SchlDuplicates(11) Val
    put  MICA_TSSCH1_CEEBCD into SchlDuplicates(12) Val
    put  MICA_TSSCH2_CEEBCD into SchlDuplicates(13) Val
    put  MICA_TSSCH3_CEEBCD into SchlDuplicates(14) Val

    Let #I = 1
    Let $DupFound = 'N'
    While #I <= 13 and $DupFound = 'N'
       Let #J = #I + 1
       While #J <= 14
          Get $Val1 from SchlDuplicates(#I) Val
          Get $Val2 from SchlDuplicates(#J) Val
          If $Val1 = $Val2
     let $DupFound = 'Y' 
             do Insert-Error
          End-If

          add 1 to #J
       End-While

       Add 1 to #I
    End-While

FROM PS_MICA_STSCHL_STG
WHERE MICA_COMMON_APP_ID = $Application_ID || $Val1
END-SQL

End-Procedure

Begin-Procedure Insert-Error

If $DupFound = 'Y' 
Let $Error_table = 'Duplicate Schools' 
End-If

BEGIN-SQL
        INSERT INTO PS_MICA_ERROR_LOG
        VALUES($Application_ID,
            $App_export_dt,
            $Error_table,
            '') 

END-SQL

end-procedure

Solution

Here is the corrected code which compiles:

begin-procedure check-duplicates

create-array name=SchlDuplicates
    size=15
Field=Val:Char

BEGIN-SELECT
MICA_SCH1_CEEBCD               
MICA_SCH2_CEEBCD               
MICA_SCH3_CEEBCD               
MICA_SCH4_CEEBCD               
MICA_COL1_CEEBCD               
MICA_COL2_CEEBCD               
MICA_COL3_CEEBCD               
MICA_TRSCH1_CEEBCD             
MICA_TRCOL1_CEEBCD             
MICA_TRCOL2_CEEBCD             
MICA_TRCOL3_CEEBCD             
MICA_TSSCH1_CEEBCD             
MICA_TSSCH2_CEEBCD             
MICA_TSSCH3_CEEBCD             

FROM PS_MICA_STSCHL_STG
WHERE MICA_COMMON_APP_ID = $Application_ID
END-SELECT

    put  $MICA_SCH1_CEEBCD into SchlDuplicates(1) Val
    put  $MICA_SCH2_CEEBCD into SchlDuplicates(2) Val
    put  $MICA_SCH3_CEEBCD into SchlDuplicates(3) Val
    put  $MICA_SCH4_CEEBCD into SchlDuplicates(4) Val
    put  $MICA_COL1_CEEBCD into SchlDuplicates(5) Val
    put  $MICA_COL2_CEEBCD into SchlDuplicates(6) Val
    put  $MICA_COL3_CEEBCD into SchlDuplicates(7) Val
    put  $MICA_TRSCH1_CEEBCD into SchlDuplicates(8) Val
    put  $MICA_TRCOL1_CEEBCD into SchlDuplicates(9) Val
    put  $MICA_TRCOL2_CEEBCD into SchlDuplicates(10) Val
    put  $MICA_TRCOL3_CEEBCD into SchlDuplicates(11) Val
    put  $MICA_TSSCH1_CEEBCD into SchlDuplicates(12) Val
    put  $MICA_TSSCH2_CEEBCD into SchlDuplicates(13) Val
    put  $MICA_TSSCH3_CEEBCD into SchlDuplicates(14) Val

!show 'MICA_SCH1_CEEBCD: ' $MICA_SCH1_CEEBCD ' MICA_SCH2_CEEBCD: '     $MICA_SCH2_CEEBCD

    Let #I = 1
    Let $DupFound = 'N'
    While #I <= 13 and $DupFound = 'N'
       Let #J = #I + 1
       While #J <= 14 and $DupFound = 'N'
          Get $Val1 from SchlDuplicates(#I) Val
          Get $Val2 from SchlDuplicates(#J) Val
          If $Val1 = $Val2 
             Show 'Found duplicates ' $Val1 ' ' $Val2
             Let $DupFound = 'Y'
             do Insert-Error
          End-If

          add 1 to #J
       End-While

       Add 1 to #I
    End-While



End-Procedure

Begin-Procedure Insert-Error

Let $Error_table = 'Duplicate Schools ' || $Val1

BEGIN-SQL
        INSERT INTO PS_MICA_ERROR_LOG
        VALUES($Application_ID,
            $App_export_dt,
            $Error_table,
            '') 

END-SQL
End-procedure