Parm format passing to sql (using SPARSE lookup)
Moderators: chulett, rschirm, roy
Parm format passing to sql (using SPARSE lookup)
I am trying to run a query, listing salary by the year.
Suppose I have the following query and would like to construct a DataStage job to do just that (with the same query)
select year, salary
from dept_cost
where year in ('2013','2014')
DataStage job structure;
Dataset(input) -> lookup (referencing Dept_cost table) -> Dataset(output)
The input Dataset has one field as follows;
input_year 100 varchar
The lookup is a SPARSE lookup and the table reference query is;
select year, salary
from dept_cost
where year in (ORCHESTRATE.input_year)
below are the tests with different values of input_col;
input_col = 2013 (successful - when this is run through the job)
input_col = 2013','2014 (unsuccessful - empty output)
input_col = '2013','2014' (unsuccessful - empty output)
My question: What format do I need to pass the two or more values of the year from the input_col to get a successful result?
I understand there are other ways to do that, but specifically I need to do it this way (using SPARSE lookup and IN clause in my query). Thanks
Table:
YEAR(varchar) SALARY
---------------- --------
2012 10000
2013 11000
2014 12000
Suppose I have the following query and would like to construct a DataStage job to do just that (with the same query)
select year, salary
from dept_cost
where year in ('2013','2014')
DataStage job structure;
Dataset(input) -> lookup (referencing Dept_cost table) -> Dataset(output)
The input Dataset has one field as follows;
input_year 100 varchar
The lookup is a SPARSE lookup and the table reference query is;
select year, salary
from dept_cost
where year in (ORCHESTRATE.input_year)
below are the tests with different values of input_col;
input_col = 2013 (successful - when this is run through the job)
input_col = 2013','2014 (unsuccessful - empty output)
input_col = '2013','2014' (unsuccessful - empty output)
My question: What format do I need to pass the two or more values of the year from the input_col to get a successful result?
I understand there are other ways to do that, but specifically I need to do it this way (using SPARSE lookup and IN clause in my query). Thanks
Table:
YEAR(varchar) SALARY
---------------- --------
2012 10000
2013 11000
2014 12000
iShoreETL
Take distinct input_year from dataset by using remove duplicate stage.
Then in sequential transformer construct the string :
svyear : if @INROWNUM = 1 then ''':INPUT.input_year:''' else svyear:',':''':INPUT.input_year:'''
This will construct string '2013','2014'
Pass this as column YEAR_COL to the sparse lookup stage :
select year, salary
from dept_cost
where year in (ORCHESTRATE.YEAR_COL)
Then in sequential transformer construct the string :
svyear : if @INROWNUM = 1 then ''':INPUT.input_year:''' else svyear:',':''':INPUT.input_year:'''
This will construct string '2013','2014'
Pass this as column YEAR_COL to the sparse lookup stage :
select year, salary
from dept_cost
where year in (ORCHESTRATE.YEAR_COL)
Last edited by ssnegi on Wed Jun 18, 2014 4:26 pm, edited 1 time in total.
If constructing the string in a transformer ends up giving you the same results as reading the string directly from your dataset, you may see if protecting the quotes from the orchestrate shell will help.
You could try
Mike
You could try
Code: Select all
\'2013\',\'2014\'
I dynamically created the input_year as per instruction above and the result was the same. Here is the peek result;ssnegi wrote:stuff
Peek_1,0: in_year:'2012','2013','2014' year:NULL month:NULL
- (in_year) is the dynamically created string
- (year) is coming from table reference
- (month) is coming from table reference
any other suggestion -- anyone?
iShoreETL
Using ORACLE CONNECTOR--
Added $CC_MSG_LEVEL=2, I get the query but cannot determine how it substitute the variable (another word does not list the query with the value of in_year at the run time)
Here is what I get in director;
DEBUG:Preparing statement: select year, month from table_01 where year in (:year)
Added $CC_MSG_LEVEL=2, I get the query but cannot determine how it substitute the variable (another word does not list the query with the value of in_year at the run time)
Here is what I get in director;
DEBUG:Preparing statement: select year, month from table_01 where year in (:year)
iShoreETL
OK... a difference between using a job parameter vs. a column from the input stream.
I haven't used Oracle in the recent past, but maybe you can try this as a workaround while you wait on a verdict from your support provider.
Use a parameter set with a values file to hold your IN selection criteria. That approach recently worked just fine for me using escaped single quotes in conjunction with the Teradata connector stage where I parameterized the entire WHERE clause.
CC_MSG_LEVEL=2 will show you the actual value from parameter substitution, which it apparently doesn't with a host variable.
Mike
I haven't used Oracle in the recent past, but maybe you can try this as a workaround while you wait on a verdict from your support provider.
Use a parameter set with a values file to hold your IN selection criteria. That approach recently worked just fine for me using escaped single quotes in conjunction with the Teradata connector stage where I parameterized the entire WHERE clause.
CC_MSG_LEVEL=2 will show you the actual value from parameter substitution, which it apparently doesn't with a host variable.
Mike
And yet the prepared SQL with the bind variable is exactly what I'd expect to see at that point when the DEBUG message was written out. After the SQL is prepared, each time it is called the bind variable is replaced with a value from the input stream. I'm surprised that that is "not working" and am interested as well in what verdict you get back from support.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Can one of you elaborate on IBM response below as it relates to this problem; Thanks
-----------------------------------------------------------------------------
Thank you for contacting IBM Support. The "IN" Keyword is looking for a list of values to execute the query against. Unfortunately when used with "where year in (ORCHESTRATE.input_year)" You will only get one value and that will be for the exact row the sparse lookup is running against. You will need to restructure your logic to be able to pull a subset of data if that is your requirement.
Since we are processing sparse lookups for each input row, you will not have the ability to see all past or previous row values.
-----------------------------------------------------------------------------
Thank you for contacting IBM Support. The "IN" Keyword is looking for a list of values to execute the query against. Unfortunately when used with "where year in (ORCHESTRATE.input_year)" You will only get one value and that will be for the exact row the sparse lookup is running against. You will need to restructure your logic to be able to pull a subset of data if that is your requirement.
Since we are processing sparse lookups for each input row, you will not have the ability to see all past or previous row values.
iShoreETL