Parm format passing to sql (using SPARSE lookup)

[iShoreETL]

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

[ssnegi]

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)

[Mike]

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

Code: Select all

\'2013\',\'2014\'

Mike

[iShoreETL]

stuff

I dynamically created the input_year as per instruction above and the result was the same. Here is the peek result;

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]

I tested Mike's method as well with the same result.
here is how I constructed the string;

From Transformer Derivation;
"\'2013\',\'2014\'"

Here is the result from peak

Peek_1,0: in_year:'2013','2014' year:NULL month:NULL

[Mike]

Which connector stage are you using? They don't all necessarily behave the same.

You could add the $CC_MSG_LEVEL environment variable to your job to get some debugging information added to your job log (including the exact SQL statement that the connector has been given to execute).

Mike

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

[Mike]

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

[iShoreETL]

Thanks mike

It does work with the parameter set, it is strange though that it does not work with the host variable.




Anyone else has any idea on how to solve the original problem?

[Mike]

Maybe not so strange.

Using a host variable would seem to assume a static SQL statement with a variable "value" provided. In your case, your host variable is meant to actually change the SQL statement.

Parameter substitution works because it happens before the SQL statement is prepared.

Mike

[chulett]

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.

[iShoreETL]

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.

[chulett]

Sounds like they don't understand that your INPUT_YEAR string contains a delimited list of values rather than a single one. Best to ask them to elaborate on and clarify their statement.