way to store sequence val temporarily & pass it to 2 tab

dataservices job design
1

Hello,

Is there a way to store a value temporarily in DI inside a DF in a variable or something similar? Wish to pass the retireved sequence.next val (obtained through database stored proedure) to 2 tables, one after another.

Here is the scenario:



                                 query1 -> Test Tbl (Transaction control - 1)
                                /
StgTbl --> Validation ------>
                   |           \
                   |            query2 -> Test Date Tbl (Transaction control - 2)
                   |
             Exception table

Data must enter TEST table first and then into TEST_DATE table. But the PK column ‘test_id’ for TEST table also forms the part of the composite PK for TEST_DATE table. Hence the next value of sequence for the PK col ‘test_id’ must be used for the new row in both the tables.

I can retrive the next val in sequence using a database stored procedure. Now after retrieving, if i can store the value in some variable, then i can pass the same value to Test Date table.

Please pass me suggestions on how i should do this.

-------------------- Thanks for the help ------------------------

invincible (BOB member since 2009-08-07)

2

Think i got my answer in the post below mine.



Stgtbl -> validation -> Query1 ->Test Table
                |          |
         exception table    ------- >Query2 ----> Test Date Table

Think this will solve my problem. Is that right?

invincible (BOB member since 2009-08-07)

3

If you retrieve test_id in Query1 then you should be fine. As you’ve spotted, this will then get passed to both targets.

finbo :uk: (BOB member since 2006-12-15)

4

There is a problem with this solution that i am facing.


validation-->query1 --> TEST TABLE
               |
               --> query2 --> TEST DATE TABLE

validation has these columns:
col1
col2
col3
col4

now query 1 is mapped with TEST TABLE. and this table has just col1 and col2.

query2 is mapped with TEST_DATE table and this table has columns col1, col3 and col4.

i tried to add col1,col2,col3 and col4 to query1 so that i can use them in query2. But this gives me error - CANNOT FIND MATCHING NAME FOR SOURCE COLUMNS col3 AND col4 IN THE TARGET - TEST TABLE.

:? :x what to do?

Thanks.

invincible (BOB member since 2009-08-07)

5

:hb: :hb:
:cry: :cry:
:nonod: :nonod:
:x :x

Please Help …

invincible (BOB member since 2009-08-07)

6 

validation-->query_col1_to_col4 --> Query_col1_col2 --> TEST TABLE
               |
               --> query_col1_col3_col4 --> TEST DATE TABLE

Werner Daehn :de: (BOB member since 2004-12-17)

7

Thank You so much Werner!! I m a big fan of yours ! You are a BOB DI hero ! :mrgreen: :+1: Hail Werner !

invincible (BOB member since 2009-08-07)

8

Werner Daehn :de: (BOB member since 2004-12-17)