on 2014 Jan 14 10:57 AM
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 ?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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}...');
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
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.)
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?
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.
User | Count |
---|---|
70 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.