topic Re: how to use oracle sequences in 4.2 DI Studio in SAS Enterprise Guide

how to use oracle sequences in 4.2 DI Studio

deleted_user — Tue, 14 Apr 2009 14:14:52 GMT

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

Re: how to use oracle sequences in 4.2 DI Studio

deleted_user — Wed, 15 Apr 2009 05:38:02 GMT

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!

Re: how to use oracle sequences in 4.2 DI Studio

Halaku — Sun, 19 Dec 2010 19:32:09 GMT

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.

Re: how to use oracle sequences in 4.2 DI Studio

etl_tool — Sun, 26 Apr 2015 02:19:35 GMT

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