how to use oracle sequences in 4.2 DI Studio

[deleted_user]

i want to use oracle sequences in table loader or sql join . how can i use oracle sequences in DI studio.

[deleted_user]

Instead of using SAS DI studio 4.2's SK generator transform, I would like to use Oracle sequence using nextval function in oracle to generate teh SK. This is a common practice in DWH, hence I believe SAS has some facility for this.

How can this be acheived? From what I understand ,in the table loader transform that loads data into ORA , somehow we will need to invoke the nextval function. But table loader does not have any pass through fcaility.
Any thoughts on this will be greatly appreciated!

[Halaku]

Hi, I just learned from the support that you can query SEQUENCE using a SQL JOIN (SJ) transformation. This is how you do it:
1. Access a (control) table in the db where the SEQ resides. I created a single column single row table.
2. Drop the table before the transformation and connect to it.
3. Drop other connections in SJ and get into properties.
4. Select Pass Through in properties.
5. create a new column in target and
6. Use SEQNAME.NEXTVAL in the EXPRESSION to get the next value.

Now you can use it in the next step.

[etl_tool]

Hi Halaku,

I tried doing it but I'm getting the error when i use seqname.nextval in the expression of the column that i created

ERROR: Unresolved reference to table/correlation name seqname_SEQ