文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >在SQR中检查副本

问在SQR中检查副本
EN

Stack Overflow用户
提问于 2016-03-17 17:20:29
回答 2查看 434关注 0票数 1

目前，我正在尝试更新SQR，以检查给定记录中列之间的重复值。我们允许将重复项输入到表中，但是当它破坏下一个程序时，我们希望检查一个错误标志。

代码语言：javascript
复制
ID - 1 Var1 - 3809 Var2 - 3809
ID - 2 Var1 - NULL Var2 - NULL
ID - 3 Var1 - 5204 Var2 - 3809

在这种情况下，ID 1将被检查错误标志，而其他两个则不会。我不能简单地检查跨值，因为我需要检查12个不同的列。任何帮助都将不胜感激。

谢谢!丹

我尝试了下面的代码，它编译，但不记录重复的

代码语言：javascript
复制
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
sql
sqr
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-04-08 18:09:22

以下是编译以下代码的更正代码：

代码语言：javascript
复制
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
票数 0
EN

Stack Overflow用户

发布于 2016-03-17 18:42:55

这将使用您的代码，并通过重复调用每个记录的插入错误例程。这会用dups标记每一张唱片(但每个唱片只有一张)。如果你只想要一个错误的所有记录，让我知道。

代码语言：javascript
复制
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
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/36067535

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档