Toad, Teradata, error question

Toad Data Point
1

Hi, I am receiving this error message:

Teradata exception: [Teradata Database] [2801] Duplicate unique prime key error in ar_asrh_dm_etl_beacon.target table.

Not sure why?

I made a simple modification to this script below by adding two additional columns:

,fca.MSDRG_id AS MSDRG_id
,fca.Stay_id AS Stay_id

Prior to that it worked fine?

Any thoughts?

Thanks

WHP

CREATE TABLE ar_asrh_dm_etl_beacon.Flg_ReAdAlgo1 AS (
SELECT DISTINCT RANK() OVER( PARTITION BY fca.Mem_id
ORDER BY fca.Dte_Admit_id ASC, fca.Dte_Disch_id ASC, fca.Claim_id ASC, spr.ProvRend_id ASC ) AS line_rank
, fca.Mem_id AS AdmitMem_id
, fca.Claim_id AS AdmitClaim_id
, lps.PatStat_dl AS AdmitPatStat_dl
, fca.PatStat_id AS AdmitPatStat_id
, spr.ProvRend_id AS AdmitProvRend_id
, spr.ProvRend_LastName AS AdmitProvRend_LastName
, fca.Dte_Admit_id AS AdmitDte_Admit_id
, fca.Dte_Disch_id AS AdmitDte_Disch_id
, fca.MSDRG_id AS MSDRG_id
, fca.Stay_id AS Stay_id

FROM ar_asrh_ap_etl_beacon.Fv_Claimline_Apport       fca
     INNER JOIN ar_asrh_ap_etl_beacon.Lv_ProvRendVer lpr ON fca.ProvRendVerIdent_id = lpr.ProvRendVerIdent_id
     INNER JOIN ( SELECT lpv.ProvRendVerIdent_id
                       , lpv.ProvRend_id
                       , lpv.ProvRend_LastName
                    FROM ar_asrh_ap_etl_beacon.Lv_ProvRendVer lpv
                         INNER JOIN ( SELECT ProvRend_id
                                           , MAX( ProvRendVerIdent_id ) AS ProvRendVerIdent_id
                                        FROM ar_asrh_ap_etl_beacon.Lv_ProvRendVer
                                        GROUP BY 1
                                    ) AS                      mxp ON lpv.ProvRend_id = mxp.ProvRend_id AND
                                                                     lpv.ProvRendVerIdent_id = mxp.ProvRendVerIdent_id
                ) AS                                 spr ON lpr.ProvRend_id = spr.ProvRend_id
     INNER JOIN ar_asrh_ap_etl_beacon.Lv_PatStat     lps ON fca.PatStat_id = lps.PatStat_id
    
  
WHERE fca.Dte_SvcFrom_id BETWEEN '2008-07-01' AND '2011-06-30'
  AND fca.SvcPlace_id = 36 /*Inpatient Hospital*/
  AND fca.ClaimCategory_id = 4 /*Inpatient*/
  AND fca.AdjustIndic_id = 1
  AND fca.ClaimPayStat_ClaimLine_id = 5
  AND fca.F_PAID_AM > 0
  AND fca.Dte_Disch_id <> '0001-01-01'
  AND fca.Dte_Admit_id <> '0001-01-01'
  AND fca.Stay_id <> -2

) WITH DATA UNIQUE PRIMARY INDEX ( AdmitMem_id, AdmitClaim_id, AdmitProvRend_id, line_rank );

2

You get duplicate values for the primary key.
Base on you business need you could create the table without the primary key or make it non unique.
Also you could try to replace Rank() function with Row_Number().

Aleksey

3

Without seeing your data, I would imagine at least one of the new columns created a duplicate based on the current UPI. Personally, I would suggest evaluating what combination of columns (focusing on the two you just added) create the primary key.

Preserving the unique primary index is a good idea (along with recollecting stats based on the UPI) to reducing skew and improving performance for that table. Removing the PI, especially a UPI, is typically not a good idea in Teradata.

4

Thank you