0

I'm converting ETL queries written for Netezza to RedShift. I'm facing some issues with ROWID, because it's not supported in RedShift. I have tried using the key columns in the predicates, based on which ROWID is being generated to actually do a workaround. But i'm confused which columns would be used if there are multiple join operations. So is there anyone who can help me convert the query. I even tried to use ROW_NUMBER() over () function, but it also doesn't work because row ids won't be unique for all rows.

Here are the queries from netezza:

Query #1

CREATE TEMP TABLE TMPRY_DELTA_UPD_1000 AS
        SELECT
        nvl(PT.HOST_CRRNCY_SRRGT_KEY,-1) as HOST_CRRNCY_SRRGT_KEY,
        delta1.ROWID ROW_ID
        FROM TMPRY_POS_TX_1000 PT
        LEFT JOIN TMPRY_TX_CSTMR_1000 TC ON  PT.TX_SRRGT_KEY = TC.TX_SRRGT_KEY AND PT.UPDT_TMSTMP > '2017-01-01' 
                                    AND PT.INS_TMSTMP < '2017-01-01' AND PT.DVSN_NBR = 70

        JOIN INS_EDW_CP.DM_TX_LINE_FCT delta1 ON PT.TX_SRRGT_KEY = delta1.TX_SRRGT_KEY 
        WHERE
        (
        delta1.HOST_CRRNCY_SRRGT_KEY   <>  PT.HOST_CRRNCY_SRRGT_KEY     OR
        )
        AND PT.DVSN_NBR = 70;

Query #2

 UPDATE INS_EDW_CP..DM_TX_LINE_FCT base
   SET
   base.HOST_CRRNCY_SRRGT_KEY   =  delta1.HOST_CRRNCY_SRRGT_KEY,
  )
FROM TMPRY_DELTA_UPD_1000 delta1
WHERE base.ROWID = delta1.ROW_ID;

How can i convert query # 2?

Aneela Saleem Ramzan
  • 127
  • 1
  • 8
  • 15

1 Answers1

0

Well, most of the time I have seen joins on rowid it is due to performance optimizations, but in some cases there ARE no unique combination of columns in the table. Please talk to the people owning these data & run your own analysis of different key combinations and then get back to us.

Lars G Olsen
  • 1,093
  • 8
  • 11