Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Using database sequences (DB2)

jeffbusch
5 - Atom

We need to use database sequences for unique IDs since they need to be shared between multiple processes, including database stored procedures.

I can select a single NEXT VALUE from the sequence in an Input tool, but it only returns one, not one per row in my data stream.

I think I need to add the correct syntax* to my Output tool, but I can't figure out how.

I investigated Dynamic Replace, but I think it will put the command in as a text string, not in the SQL itself.

 

SELECT

    *NEXT VALUE FOR [SCHEMA].[SEQUENCE NAME],

   COLUMN2,

   COLUMN3,

   etc

FROM [SCHEMA].[TABLE]

 

7 REPLIES 7
Bharat_andalam
6 - Meteoroid

 

 

jeffbusch
5 - Atom

The goal is to be able to use the sequence in the INSERT to the target. 

I may have not have made it clear, this is for building primary keys in the target table.

However, as I said, there are other processes in the database that use the same sequence to build primary keys in other tables so it needs to be shared across processes.

Bharat_andalam
6 - Meteoroid

Please convert your workflow into INdb and then before target use the formula tool and add a column for the primary key and  populate with the next value.

jeffbusch
5 - Atom

I agree that normally this would be the solution, but since I'm working in DB2 I don't have this option.

jeffbusch
5 - Atom

To update on my progress and help anyone else who has the same issue:

I have successfully tested inserting a -1 as my unique ID and then adding a Post SQL statement that updates the -1s with the NEXT VALUE for the sequence.

This is a OK workaround, but for a large number of rows this will be slow, so I would still like to implement a true solution if possible.

NicolasFabre81
8 - Asteroid

I have the same issue while trying to insert in my postgreSQL table using indb tools :

NicolasFabre81_1-1665584925445.png

 

 

My Id_rupture has a default value made with a sequence to generate the unique IDs.

 

Is there any way to have the sequence play his role ?

Branka
5 - Atom

I used formula in DB to add sequence. 

Labels