Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Using database sequences (DB2)

Highlighted
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]

 

Highlighted
5 - Atom

 

 

Highlighted
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.

Highlighted
5 - Atom

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.

Highlighted
5 - Atom

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

Highlighted
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.

Labels