Avoid Multiple lookups

[yvkumar]

Hi Team

I am not able to resolve this issue

source ---> source.xls
lookup ---> test.xls
target --> target.xls

From the source file i need to look into lookup i.e test file and i have to get the result i.e target.csv.. I should avoid multiple lookups for each column.. Actually we have more than 15 columns like this M1,M3,and so on..

How can we do this I am not able to get any idea can anyone suggest me how to do avoid multiple lookups and get the result?

Any help is appreciated...

[yvkumar]

I want to do single database lookup multiple times for different fields from the source.xls i.e M1,M2,and so on..
If its not possible i should knw hw can we appraoch for this solution to get the target structure based on source.xls and test.xls(lookup file)

[marabu]

What about a subquery?

SELECT 
    (SELECT t.description FROM test t WHERE t.name = 'M1' AND t.option_value = s.M1) AS M1,
    (SELECT t.description FROM test t WHERE t.name = 'M3' AND t.option_value = s.M3) AS M3
FROM source s

[yvkumar]

Thanks a lot Marabu for giving me good solution in query level..

If we have additional columns along with this in source table like s.rec_id etc(values are 100,200,210,310,A100,etc..).. if we are adding this column in query we will get cartesian join result..we have to avoid this issue with some join condition but in lookup table and source table we wont find such common column.

Is there any way we can avoid queries and do it with other steps in pentaho..

What about a subquery?

SELECT 
    (SELECT t.description FROM test t WHERE t.name = 'M1' AND t.option_value = s.M1) AS M1,
    (SELECT t.description FROM test t WHERE t.name = 'M3' AND t.option_value = s.M3) AS M3
FROM source s

[marabu]

I don't understand what you are heading for, I'm afraid.

You most certainly don't get a cartesian join from subqueries.

Is there any way we can avoid queries and do it with other steps in pentaho..

Why avoid queries?
Your database engine can handle queries more economically than we can write PDI steps.
I bet.

[yvkumar]

Hi

Sorry for my question might be wrong
I just modified ur query with adding extra field

SELECT s.id_n,
(SELECT t.description FROM test t WHERE t.name = 'M1' AND t.option_value = s.M1) AS M1,
(SELECT t.description FROM test t WHERE t.name = 'M3' AND t.option_value = s.M3) AS M3
FROM source s

[Ato]

Not sure to get the point here.
Perhaps a database join is what you need. Actually you need two db join: one for M1 and one for M2, as per your example.

Give a look at the screenshot.

HTH
Schermata.jpg

[yvkumar]

Apologies for u..

thanks Marabu it is coming fine... I just did small mistake..

the solution what u have given in query level it works fine..

[yvkumar]

Hi Ato,

Thanks for ur reply..