CODEWITHSUNDEEP
sqlsql-server
Kamran Malik
Kamran Malik

Reputation: 9

REPLACE with INNER JOIN SQL Server

FROM   
    DW_Stage.PreStage.FilteredCoupon  c
INNER JOIN 
    DW.Linkage l WITH (nolock)
          REPLACE([l.CouponNumber], '/', '') ON (c.CouponSeqNbr = l.CouponNumber 
                                             AND c.PNRCreateDate = l.PNRCreateDate 
                                             AND c.PNRLocator = l.PNRLocator 
                                             AND c.PrimaryDocNbr = l.PrimaryDocNbr)

So there are two records that have a '/' in CouponNumber which is causing my SSIS package to fail. If i use this REPLACE function would this be the best use of it? Or should the REPLACE function be used in the ON part? Thanks

Upvotes: 0

Views: 126

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You want the replace() in the on part. Your syntax makes no sense. But you also want to remove the square braces:

FROM DW_Stage.PreStage.FilteredCoupon c INNER JOIN
     DW.Linkage l
     ON c.CouponSeqNbr = REPLACE(l.CouponNumber, '/', '') AND
        c.PNRCreateDate = l.PNRCreateDate AND
        c.PNRLocator = l.PNRLocator AND
        c.PrimaryDocNbr = l.PrimaryDocNbr

The parentheses arond the ON conditions also add no value.

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

FROM   
          DW_Stage.PreStage.FilteredCoupon  c
          INNER JOIN 
          DW.Linkage l WITH (nolock)


              ON 
          (     
         c.CouponSeqNbr = REPLACE([l.CouponNumber], '/', '')
        AND c.PNRCreateDate = l.PNRCreateDate 
        AND c.PNRLocator = l.PNRLocator 
        AND c.PrimaryDocNbr = l.PrimaryDocNbr )

Upvotes: 0

Related Questions