Get Data from the Database based on a key column.

Minhajuddin · Post by **Minhajuddin** » Mon Jun 18, 2007 7:23 am

Hi all,

We have a scenario in which we need to pass data into a DB table and get the output based on the input column.

I have a job which has

SequentialFile--------->DRS_Stage-------------->SequentialFile

I am giving this query in the inputs tab of the DRS_Stage.
And it throws up a warning " Variables are not bound correctly".
This is not the exact query. But it looks like this.

select * from emp where emID=:1

Can we use the data coming from the input link of the DRS stage as a bind variable in the Inputs tab.

DSguru2B · Post by **DSguru2B** » Mon Jun 18, 2007 8:26 am

A better way would be to do a select from your table and do a lookup on your first sequential file stage based on the key and get the output.

Minhajuddin · Post by **Minhajuddin** » Mon Jun 18, 2007 8:43 am

But here the query has a "START WITH" clause and we put the :1 in the Start with clause

Code: Select all

select customer
  from (select customer, parent_customer, customer_level
          from (select customer_number as customer,
                       parenthdqtrcustomernum as parent_customer,
                       level as customer_level
                  from customer
                start with customer_number = :1
                connect by prior parenthdqtrcustomernum =
                                 customer_number) d1
        order by customer_level desc) d2,
       (select nvl(max(customer_level), 0) as max_customer_level
          from (select customer_number as customer,
                       parenthdqtrcustomernum as parent_customer,
                       level as customer_level
                  from customer
                start with customer_number = :1
                connect by prior parenthdqtrcustomernum =
                                 customer_number)) d3
 where rownum <= 4

Initially we had a transformer which did the lookup into this table and returned rows. But now the number of rows returned for one input record is equal to four. And the transformer just gets a single match record.

Thanks for the help DSguru

DSguru2B · Post by **DSguru2B** » Mon Jun 18, 2007 9:23 am

Well, instead of the DRS stage, you can use an odbc stage for direct lookup and specify that this link returns multi-row result set.

Minhajuddin · Post by **Minhajuddin** » Mon Jun 18, 2007 10:29 am

My backend DB is Oracle.

So if I use a lookup. Will it not throw warnings if we have more than one match in the lookup table?

Thanks again.

DSguru2B · Post by **DSguru2B** » Mon Jun 18, 2007 10:33 am

No. If you turn on "Multi-row result set". You should not get any warnings.

Minhajuddin · Post by **Minhajuddin** » Mon Jun 18, 2007 12:21 pm

Thanks DSguru.

But my DB is huge. So, I can't use the Lookup stage unless I make it a Sparse Lookup. I have done that. Since my input file also has a large amount of data, I was just wondering if we had any other way to implement this.

Thank you.

DSguru2B · Post by **DSguru2B** » Mon Jun 18, 2007 12:58 pm

Consequently, you can load your value into a staging table and then use your sql query to join this staging table. Thats another way.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 18, 2007 2:34 pm

Minhajuddin wrote:But my DB is huge. So, I can't use the Lookup stage unless I make it a Sparse Lookup. I have done that. Since my input file also has a large amount of data, I was just wondering if we had any other way to implement this.

But you asserted that this is a server job. If it is, then an ODBC stage with multi-row return is an ideal solution, particularly if the search key is indexed in the Oracle table.

Minhajuddin · Post by **Minhajuddin** » Tue Jun 19, 2007 10:19 am

Thanks for all your responses guys! :D

I am using a sparse lookup on the table, the performance will degrade because of this. But this the only choice I have.

Once again thank you.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 19, 2007 2:29 pm

It's not a server job, is it?