Question Re: Configurable Source Table Name in Technology Q&A

Configurable Source Table Name

Former Member — Tue, 14 Jan 2014 10:57:50 GMT

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 ?

Re: Configurable Source Table Name

former_member187605 — Tue, 14 Jan 2014 11:42:25 GMT

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.

Re: Configurable Source Table Name

Former Member — Wed, 15 Jan 2014 11:11:00 GMT

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?

Re: Configurable Source Table Name

former_member187605 — Wed, 15 Jan 2014 11:24:13 GMT

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}...');

Re: Configurable Source Table Name

Former Member — Wed, 15 Jan 2014 13:48:07 GMT

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

Re: Configurable Source Table Name

Former Member — Wed, 15 Jan 2014 14:16:16 GMT

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

Re: Configurable Source Table Name

former_member187605 — Wed, 15 Jan 2014 14:30:11 GMT

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

Re: Configurable Source Table Name

Former Member — Thu, 16 Jan 2014 08:58:19 GMT

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?

Re: Configurable Source Table Name

former_member187605 — Thu, 16 Jan 2014 10:25:29 GMT

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.

Re: Configurable Source Table Name

Former Member — Tue, 21 Jan 2014 14:37:19 GMT

Dirk,

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