topic Re: ELT Sequence in Talend Studio

ELT Sequence

Anonymous — Sat, 16 Nov 2024 04:54:54 GMT

Good Morning,

I am trying to generate an tELTOracleOutput which needs to generate a sequence number for a oracle table. The sequence has already been created in Oracle.

I have tried multiple options and cannot seem to get it to work.

context.SequenceNumber

Numeric.sequence("s1",1,1)

STK_SLF_CNT_TCKR_FS_v1_SEQ.NEXTVAL

I have searched for this and cannot seem to find a solution.

Kind Regards,

Shaf

Re: ELT Sequence

jeoste — Tue, 20 Aug 2019 12:55:51 GMT

Hello,

I'm not sure, but what happens if you put the component "tOracleRow" and add this query:

"select nextval('" + context.sequenceNumber + "')"

Or maybe

"select nextval('your_schema.STK_SLF_CNT_TCKR_FS_v1_SEQ')"

EDIT : tOracleRow, and not tOracleInput

Re: ELT Sequence

Anonymous — Tue, 20 Aug 2019 13:10:02 GMT

@eost thank you for your response.

Due to using ELT component, ETL component is not compatible.

The reason I need the sequence is so that I can perform SCD against the new data that gets inserted from the initial job.

I have used the SCD component without surrogate key but that does not impact the data when there is a change in "Type 2".

I have tried multiple ways of writing the sequence in to the ELT Oracle output table and cannot seem to surpass this stage.

Re: ELT Sequence

Anonymous — Tue, 20 Aug 2019 14:19:53 GMT

This is what the SQL Query looks like part of the ELT Tmap

"SELECT
"select nextval('DM.STK_SLF_CNT_TCKR_FS_v1_SEQ')" AS ROW, NVL(ORGD.ROW_ID,-1) AS ORG_ID, NVL(ORGD.ORG_SCD_ID,-1), NVL(Shelf_Ty.ROW_ID ,-1) AS SHELF_TYP_ID, Shelf.AMD_DATE AS AMD_DT, Shelf.COUNT_DA AS COUNT_DT, Shelf.SHELF_NO, Shelf.QTY
FROM
(
SELECT
Shelf.BRCH AS BRCH, Shelf.SHELF_TYPE AS SHELF_TYPE, Shelf.SHELF_NO AS SHELF_NO, Shelf.QTY AS QTY, Shelf.AMD_DATE AS AMD_DATE, Shelf.COUNT_DA AS COUNT_DA, Shelf.UPDATE_COUNT AS UPDATE_COUNT, Shelf.PICK_DATE AS PICK_DATE, Shelf.LOCATE_DATE AS LOCATE_DATE, Shelf.INSERT_DATETIME AS INSERT_DATETIME, Shelf.HASH_DIFF AS HASH_DIFF, rank() OVER (PARTITION BY Shelf.BRCH,Shelf.SHELF_TYPE,Shelf.SHELF_NO ORDER BY Shelf.insert_datetime DESC) AS RANK
FROM
" +context.ODS_Schema+"."+"ORHHSHLF"+ " Shelf
WHERE
Shelf.INSERT_DATETIME <= '" +context.BusinessDate+ "'
) Shelf LEFT OUTER JOIN " +context.DM_Schema+"."+"ORG_D"+ " ORGD ON( ORGD.ORG_CD = Shelf.BRCH AND ORGD.EFF_FRM_DT <= Shelf.COUNT_DA AND ORGD.EFF_TO_DT >= Shelf.COUNT_DA )
LEFT OUTER JOIN " +context.DM_Schema+"."+"SHELF_TYP_D"+ " Shelf_Ty ON( Shelf_Ty.SHELF_TYP_CD = Shelf.SHELF_TYPE )
WHERE
Shelf.COUNT_DA = '" +context.BusinessDate+ "'
AND Shelf.RANK = 1"

Re: ELT Sequence

Anonymous — Wed, 21 Aug 2019 11:13:42 GMT

Can someone please help.

Thank you.