cancel
Showing results for 
Search instead for 
Did you mean: 

Configurable Source Table Name

Former Member
0 Kudos
361

I am new to data services. We are using V14.1.

We have a source system (it is Sybase but not sure that is relevant) which creates a table for each day so something like

Tablename20140113

Tablename20140114

Our requirement is to process all data since the last run so we might need to access several of these tables.

We have no rights (other than read) on the source system so cannot create any views.

My question is how can we dynamically change the source table name in a data flow ?

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member187605
Active Contributor

You can do this by using a SQL Transform as source in your dataflow.

First define a global variable, let's call it $TableName. Initialise it in a script:

     $Tablename= 'Tablename20140113';

or anything more complex using timestamps, sql() function to get the table name(s) from the Sybase dictionary...

Open the SQL transform in your dataflow. Enter

     select * from Tablename20140113

in the "SQL text" field. Select the "Update Schema" button.

Then replace "SQL text" by

     select * from [$TableName]

execute your job.

Former Member
0 Kudos

Thanks for the reply. Sounds like that will work.

I am having a problem returning multiple lines to a variable though - if my query results in multiple table name I seem to only get the first one in the variable. Is this a restriction and if so is there a way around it?

former_member187605
Active Contributor
0 Kudos

That's correct. You only get the first name. If you've got multiple tables, you can better process them one by one, using a DS While loop.

In a script before the loop:

     $Tablename = sql('DS','select min (tablename) from db_dictionary...');

The condition in the while:

    $Tablename is not null

And then a 2nd script within the loop, following the dataflow, with:

  $Tablename = sql('DS','select min (tablename) from db_dictionary where tablename > {$Tablename}...');

Former Member
0 Kudos

Thank you for that. I will have another go and let you know how I get on!

Former Member
0 Kudos

I think I'm getting an infinite loop. Here is what I have :


$V_TABLENAME = sql('Sybase',
  'select name from sysobjects
   where substring(name,16,8) >= substring(\'[$V_LAST]\',1,4)||
       substring(\'[$V_LAST]\',6,2)||
       substring(\'[$V_LAST]\',9,2)
  ');

while ( $V_TABLENAME is not null )
  begin

   $V_QUERY = sql('Sybase','select min(name) from sysobjects
                 where name name > \'[$V_TABLENAME]\'
     ');
end

former_member187605
Active Contributor
0 Kudos

That's because your $V_TABLENAME does not change in the loop. Your 2nd assignment statement must be:

$V_TABLENAME= sql('Sybase','select min(name) from sysobjects

                 where name > {$V_TABLENAME}';

(By the way, if you're using curly brackets instead of straight ones, you can get rid of all those escaped quotes.)

Former Member
0 Kudos

I see. So I need something like this :


$V_TABLENAME = sql('Sybase',
  'select name from sysobjects
   where substring(name,16,8) >= substring(\'[$V_LAST]\',1,4)||
       substring(\'[$V_LAST]\',6,2)||
       substring(\'[$V_LAST]\',9,2)
  ');

while ( $V_TABLENAME is not null )
  begin

  $V_QUERY = [$V_QUERY][$V_TABLENAME]
   $V_TABLENAME = sql('Sybase','select min(name) from sysobjects
                 where name name > \'[$V_TABLENAME]\'
     ');
end

I've tried that and it is complaining about syntax. I think it is to do with the line   $V_QUERY = [$V_QUERY][$V_TABLENAME]?

Can I find an example somewhere of what I'm trying to do?

former_member187605
Active Contributor
0 Kudos

I don't understand what you're trying to achieve with $V_QUERY = [$V_QUERY][$V_TABLENAME].

What you need, would look like this:

with as code in the Init script:

$V_TABLENAME = sql('Sybase', 'select name from sysobjects where substring(name,16,8) >= substring({$V_LAST},1,4)||substring({$V_LAST},6,2)||substring({$V_LAST},9,2)  ');

The contents of the while loop would be something like:

with as code in the NextTable script:

$V_TABLENAME = sql('Sybase','select min(name) from sysobjectswhere name > {$V_TABLENAME}';

And the SQL transform as source in the dataflow as explained earlier.

Former Member
0 Kudos

Dirk,

Thanks for your help - I have got it working now!