斯诺DUP合并DUP插入问题

Question

嗨，所以Community有一个从tsql迁移到斯诺tsql的Proc。我使用全表截断和加载数据进行测试，但现在我们将代码推入测试阶段，似乎我的合并在某个地方出现了错误。

非技术方面:一个人可以在一个项目上有多行相同类型的折扣.他们被rtrans_lineitm_seq抓住了。例如，您可以购买20个汽车过滤器，每个过滤器显示为打印收据上的新行号。如果是每周的特价，每件商品都有折扣。也就是说让我们跳到技术方面..。

下面是斯诺So的proc代码。造成问题的虚拟记录将在下面的代码之后发布。

MERGE INTO DISCOUNT_2 tgt
USING ( 
select to_varchar(concat(organization_id,rtl_loc_id,to_varchar(replace(to_date(business_date),'-','')),trans_seq,wkstn_id,IFNULL(rtrans_lineitm_seq,0),IFNULL(rtl_price_mod_seq_nbr,0),replace(IFNULL(rtl_price_mod_reasoncode,''),' ',''),replace(IFNULL(discount_code,''),' ',''),replace(IFNULL(deal_id,''),' ','') ) ) as Unique_id
,*
,'PROC_1' AS PROC_NUMBER
,'LOYALTY_2' AS PROC_NAME
,1 as EXECUTION_NUMBER
,current_timestamp(2) as LAST_PROC_EXECUTION_DATE
from (
select 2200 as organization_id
,TH.STORE_NO as  rtl_loc_id
,TH.Date as business_date
,TH.TRANSACTION_NO as trans_seq
,case when left(TH.POS_TERMINAL_NO,2) = 'NP' then 1 
when left(TH.POS_TERMINAL_NO,2) = 'NS' then 2
when left(TH.POS_TERMINAL_NO,2) = 'NT' then 3 else 0 end as wkstn_id
,TSE.LINE_NO as rtrans_lineitm_seq
,ROW_NUMBER() over (partition by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO order by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO,TSE.DISCOUNT_AMOUNT ) AS rtl_price_mod_seq_nbr
,CAST(CONCAT(trim(substring(TH.DATE,0,charindex(':',TH.DATE)-4)), ' ' ,substring(TH.Time,charindex(':',TH.Time)-2,length(TH.Time)))AS TIMESTAMP_NTZ(9)) as create_date
,trim(CONCAT('LOYALTY',' ',ifnull(TIE.Information,''))) AS rtl_price_mod_reasoncode
,IFNULL(PERIODIC_DISC_GROUP,TIE.Information) AS discount_code
,IFNULL(abs(TSE.DISCOUNT_AMOUNT),0) AS deal_amt
,null as deal_id
,TH.STAFF_ID as create_user
,null as sales_agt_com
,null as serial_number
from HEADER as TH
join SALES_ENTRY TSE on TSE.TRANSACTION_NO = TH.TRANSACTION_NO and TSE.STORE_NO = TH.STORE_NO and TSE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO
left join CODE_ENTRY TIE on TIE.TRANSACTION_NO = TH.TRANSACTION_NO and TIE.STORE_NO = TH.STORE_NO and TIE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO and TIE.LINE_NO = TSE.LINE_NO
where TH.TRANSACTION_TYPE = 2
                and TH.ENTRY_STATUS not in (1,3)
                and TIE.TRANSACTION_TYPE = 1                
    and TIE.INFOCODE = 'LOYALTY'
                and TIE.INFORMATION not in ('PPP EXCLUSIVE','PPP Points Discount')
     and TH.TRANSACTION_NO >= 20000000

) as a where to_varchar(concat(organization_id,rtl_loc_id,to_varchar(replace(to_date(business_date),'-','')),trans_seq,wkstn_id,IFNULL(rtrans_lineitm_seq,0),IFNULL(rtl_price_mod_seq_nbr,0),replace(IFNULL(rtl_price_mod_reasoncode,''),' ',''),replace(IFNULL(discount_code,''),' ',''),replace(IFNULL(deal_id,''),' ','') ) )
  = '2200710320210826200121721126LOYALTYPPPSENIORDISCPPPSENIORDISC'
) AS src
ON (//tgt.Unique_id = src.Unique_id
to_varchar(concat(tgt.organization_id,tgt.rtl_loc_id,to_varchar(replace(to_date(tgt.business_date),'-','')),tgt.trans_seq,tgt.wkstn_id,IFNULL(tgt.rtrans_lineitm_seq,0),IFNULL(tgt.rtl_price_mod_seq_nbr,0),replace(IFNULL(tgt.rtl_price_mod_reasoncode,''),' ',''),replace(IFNULL(tgt.discount_code,''),' ',''),replace(IFNULL(tgt.deal_id,''),' ','') ) ) = src.Unique_id
   )
WHEN NOT MATCHED THEN INSERT ( tgt.Unique_id
,tgt.organization_id
,tgt.rtl_loc_id
,tgt.business_date
,tgt.trans_seq
,tgt.wkstn_id
,tgt.rtrans_lineitm_seq
,tgt.rtl_price_mod_seq_nbr
,tgt.create_date
,tgt.rtl_price_mod_reasoncode
,tgt.discount_code
,tgt.deal_amt
,tgt.deal_id
,tgt.create_user
,tgt.sales_agt_com
,tgt.serial_number 
,tgt.PROC_NUMBER
,tgt.PROC_NAME
,tgt.EXECUTION_NUMBER
,tgt.LAST_PROC_EXECUTION_DATE
)
values ( src.Unique_id
,src.organization_id
,src.rtl_loc_id
,src.business_date
,src.trans_seq
,src.wkstn_id
,src.rtrans_lineitm_seq
,src.rtl_price_mod_seq_nbr
,src.create_date
,src.rtl_price_mod_reasoncode
,src.discount_code
,src.deal_amt
,src.deal_id
,src.create_user
,src.sales_agt_com
,src.serial_number
,src.PROC_NUMBER
,src.PROC_NAME
,src.EXECUTION_NUMBER
,src.LAST_PROC_EXECUTION_DATE )
WHEN MATCHED THEN UPDATE SET
/* tgt.organization_id = SRC.organization_id
,tgt.rtl_loc_id = SRC.rtl_loc_id
,tgt.business_date = SRC.business_date
,tgt.trans_seq = SRC.trans_seq
,tgt.wkstn_id = SRC.wkstn_id
,tgt.rtrans_lineitm_seq = SRC.rtrans_lineitm_seq
,tgt.rtl_price_mod_seq_nbr = SRC.rtl_price_mod_seq_nbr
,tgt.create_date = SRC.create_date
,tgt.rtl_price_mod_reasoncode = SRC.rtl_price_mod_reasoncode
,tgt.discount_code = SRC.discount_code
,tgt.deal_amt = SRC.deal_amt
,tgt.deal_id = SRC.deal_id
,tgt.create_user = SRC.create_user
,tgt.sales_agt_com = SRC.sales_agt_com
,tgt.serial_number  = SRC.serial_number
,tgt.PROC_NUMBER = SRC.PROC_NUMBER
,tgt.PROC_NAME = SRC.PROC_NAME ,*/
tgt.EXECUTION_NUMBER = (SRC.EXECUTION_NUMBER + 1)
,tgt.LAST_PROC_EXECUTION_DATE = current_timestamp(2) 

下面是我正在测试的示例行

2200710320210826200121721126LOYALTYPPPSENIORDISCPPPSENIORDISC，UNIQUE_ID ORGANIZATION_ID RTL_LOC_ID BUSINESS_DATE TRANS_SEQ WKSTN_ID RTRANS_LINEITM_SEQ RTL_PRICE_MOD_SEQ_NBR CREATE_DATE RTL_PRICE_MOD_REASONCODE DISCOUNT_CODE DEAL_AMT DEAL_ID CREATE_USER SALES_AGT_COM CREATE_USER CREATE_USER SERIAL_NUMBER DEAL_AMT en0 20 2200,7103,2021-08-26,20012172，1，1，26，29:12.4，忠诚度PPP高级光盘，0.22，ST7103，00，PROC_1，LOYALTY_2，4，21:53.9，

我犯了错误

Duplicate row detected during DML action Row Values: ["2200710320210826200121721126LOYALTYPPPSENIORDISCPPPSENIORDISC", 2200, "7103", 18865, 20012172, 1, 1, 26, 1630009752450000000, "LOYALTY PPP SENIOR DISC", "PPP SENIOR DISC", 2200, NULL, "ST7103 00", NULL, NULL, "PROC_1", "LOYALTY_2", 1, 1642853936960000000]

我的问题是:为什么这10次中有9次会出现在“不匹配插入”部分中，而不是移动到“何时匹配更新”部分？正如您在上面的示例行中看到的那样。我成功地运行了4次代码，但是当我第五次尝试运行它时，它由于错误而失败了……

如果需要的话，还有其他我可以分享的案例。任何帮助都会很好。谢谢。

Answer 1

复制存在于(源端)上，使其不确定。这种行为在文件中有描述：

重复加入行为

当合并将目标表中的一行与源中的多行连接时，以下联接条件会产生不确定的结果(即在这种情况下，系统无法确定用于更新或删除目标行的源值)，合并的结果取决于为ERROR_ON_NONDETERMINISTIC_MERGE会话参数指定的值：

• 如果为TRUE (默认值)，则合并将返回一个错误。
• 如果为FALSE，则从重复项中选择一行以执行更新或删除；未定义所选行。

..。若要避免数据源中的多行(即源表或子查询)基于on条件匹配目标表时发生错误，请在源子句中使用组BY以确保每个目标行与源.中的一行(最多为一行)联接。

选项1:使用会话参数(快速修复将掩盖重复错误，但以未定义的方式选择源行)：

ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = FALSE;

备选案文2：

确定源中复制它们的原因，并更改USING part。找到重复的QUALIFY COUNT(*) OVER(PARTITION BY Unique_id) > 1;是最快的选择：

select to_varchar(concat(organization_id,rtl_loc_id,to_varchar(replace(to_date(business_date),'-','')),trans_seq,wkstn_id,IFNULL(rtrans_lineitm_seq,0),IFNULL(rtl_price_mod_seq_nbr,0),replace(IFNULL(rtl_price_mod_reasoncode,''),' ',''),replace(IFNULL(discount_code,''),' ',''),replace(IFNULL(deal_id,''),' ','') ) ) as Unique_id
,*
,'PROC_1' AS PROC_NUMBER
,'LOYALTY_2' AS PROC_NAME
,1 as EXECUTION_NUMBER
,current_timestamp(2) as LAST_PROC_EXECUTION_DATE
from (
select 2200 as organization_id
,TH.STORE_NO as  rtl_loc_id
,TH.Date as business_date
,TH.TRANSACTION_NO as trans_seq
,case when left(TH.POS_TERMINAL_NO,2) = 'NP' then 1 
when left(TH.POS_TERMINAL_NO,2) = 'NS' then 2
when left(TH.POS_TERMINAL_NO,2) = 'NT' then 3 else 0 end as wkstn_id
,TSE.LINE_NO as rtrans_lineitm_seq
,ROW_NUMBER() over (partition by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO order by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO,TSE.DISCOUNT_AMOUNT ) AS rtl_price_mod_seq_nbr
,CAST(CONCAT(trim(substring(TH.DATE,0,charindex(':',TH.DATE)-4)), ' ' ,substring(TH.Time,charindex(':',TH.Time)-2,length(TH.Time)))AS TIMESTAMP_NTZ(9)) as create_date
,trim(CONCAT('LOYALTY',' ',ifnull(TIE.Information,''))) AS rtl_price_mod_reasoncode
,IFNULL(PERIODIC_DISC_GROUP,TIE.Information) AS discount_code
,IFNULL(abs(TSE.DISCOUNT_AMOUNT),0) AS deal_amt
,null as deal_id
,TH.STAFF_ID as create_user
,null as sales_agt_com
,null as serial_number
from HEADER as TH
join SALES_ENTRY TSE on TSE.TRANSACTION_NO = TH.TRANSACTION_NO and TSE.STORE_NO = TH.STORE_NO and TSE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO
left join CODE_ENTRY TIE on TIE.TRANSACTION_NO = TH.TRANSACTION_NO and TIE.STORE_NO = TH.STORE_NO and TIE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO and TIE.LINE_NO = TSE.LINE_NO
where TH.TRANSACTION_TYPE = 2
                and TH.ENTRY_STATUS not in (1,3)
                and TIE.TRANSACTION_TYPE = 1                
    and TIE.INFOCODE = 'LOYALTY'
                and TIE.INFORMATION not in ('PPP EXCLUSIVE','PPP Points Discount')
     and TH.TRANSACTION_NO >= 20000000

) as a where to_varchar(concat(organization_id,rtl_loc_id,to_varchar(replace(to_date(business_date),'-','')),trans_seq,wkstn_id,IFNULL(rtrans_lineitm_seq,0),IFNULL(rtl_price_mod_seq_nbr,0),replace(IFNULL(rtl_price_mod_reasoncode,''),' ',''),replace(IFNULL(discount_code,''),' ',''),replace(IFNULL(deal_id,''),' ','') ) )
  = '2200710320210826200121721126LOYALTYPPPSENIORDISCPPPSENIORDISC'
 QUALIFY COUNT(*) OVER(PARTITION BY Unique_id) > 1;

如果查询返回多行，则意味着源查询不会生成unique_id，需要重新设计。