Checking for duplicates in SQR

Question

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

@cardmagik - I tried running the code I updated as above based on your example. It runs to 'Success' but I am not getting an addition to the Error log table as I would like. I should also mention that my developer is out of the country, so I am definitely a newbie when it comes to SQR. — Daniel J, Mar 21 '16 at 14:49
DupFound is reset after each record - when you check dupfound and write the error, you're outside the data - basically only checking the last record in the set. What you should do is call a procedure. I can add the code if you like below but it will have to be in a few hours from now — cardmagik, Mar 21 '16 at 19:26
No luck as of yet. All of my SQL is correct, and I can run the SQR without any errors occurring, but I still do not get the insert to occur on the PS_MICA_ERROR_LOG table. — Daniel J, Mar 28 '16 at 13:35
Sorry about the lag in responding - just thought to check. I noticed that the variables from the table did not have an ampersand in front of them - I added them, but probably your code has them. I've put a show statement where the code should catch duplicates. Also, get rid of the $Dup_Found variable both in the SQL and below in procedure Insert-Error. and though you probably have duplicate data, double-check that you have duplicates perhaps with the other statements - see my code below for examples — cardmagik, Apr 01 '16 at 19:11
Not a problem, thanks for all of your help. My developer is back from vacation and is working on it now. I will post the final copy once we have gone through analysis and testing. — Daniel J, Apr 07 '16 at 13:39

cardmagik · Answer 1 · 2016-04-01T19:14:25.080

This uses your code and calls the insert error routine for each record with a duplicate. This flags EVERY record with dups (but only one per record). If you only want one error for all the records, let me know.

begin-procedure check-duplicates

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

BEGIN-SQL On-Error=give_warning

SELECT
School2_lookup_ceebcode
School3_lookup_ceebcode
School4_lookup_ceebcode
College1_lookup_ceebcode
College2_lookup_ceebcode
College3_lookup_ceebcode
TR_school_lookup_ceebcode
TR_college1_lookup_ceebcode
TR_college2_lookup_ceebcode
TR_college3_lookup_ceebcode
TR_2nd_school1_lookup_ceebcode
TR_2nd_school2_lookup_ceebcode 
TR_2nd_school3_lookup_ceebcode

    show 'School1_Lookup: ' &School1_Lookup_ceebcode ' School2_Lookup: ' &School2_Lookup_ceebcode

        put &School1_lookup_ceebcode into SchlDuplicates(1) Val
        put &School2_lookup_ceebcode into SchlDuplicates(2) Val
        put &School3_lookup_ceebcode into SchlDuplicates(3) Val
        put &School4_lookup_ceebcode into SchlDuplicates(4) Val
        put &College1_lookup_ceebcode into SchlDuplicates(5) Val
        put &College2_lookup_ceebcode into SchlDuplicates(6) Val
        put &College3_lookup_ceebcode into SchlDuplicates(7) Val
        put &TR_school_lookup_ceebcode into SchlDuplicates(8) Val
        put &TR_college1_lookup_ceebcode into SchlDuplicates(9) Val
        put &TR_college2_lookup_ceebcode into SchlDuplicates(10) Val
        put &TR_college3_lookup_ceebcode into SchlDuplicates(11) Val
        put &TR_2nd_school1_lookup_ceebcode into SchlDuplicates(12) Val
        put &TR_2nd_school2_lookup_ceebcode into SchlDuplicates(13) Val
        put &TR_2nd_school3_lookup_ceebcode 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 
                 Show 'Found duplicates ' $Val1 ' ' $Val2
                 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
END-SQL

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

score 0 · Accepted Answer · answered Apr 08 '16 at 18:09

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

Checking for duplicates in SQR

2 Answers2