How to lookup a field with conditional field from input
Posted: Wed Sep 05, 2007 3:14 am
hi,
Need advise on how to lookup a column from a table as below:-
Table = tableA
col1 col2 col3 col4 col5
------------------------------------------------------------------
A a1 <100cc 0 99
A a1 100cc to 200cc 100 200
A a1 >200cc 201 9999
A a2 <5months 0 4
A a2 5months to 10months 5 10
A a2 >10months 11 99
How to lookup for col3 in transformer stage?
if given input col1 = A, col2 = a1, measure = 50;
expected result = <100cc
via SQL:-
select col3 from tableA where col1 = A and col2 = a1 and col4 <= 50 and col5 >= 50
Lookup thru ODBC stage? or write the table to Hash File and retrieve the value thru routine?
Need advise on how to lookup a column from a table as below:-
Table = tableA
col1 col2 col3 col4 col5
------------------------------------------------------------------
A a1 <100cc 0 99
A a1 100cc to 200cc 100 200
A a1 >200cc 201 9999
A a2 <5months 0 4
A a2 5months to 10months 5 10
A a2 >10months 11 99
How to lookup for col3 in transformer stage?
if given input col1 = A, col2 = a1, measure = 50;
expected result = <100cc
via SQL:-
select col3 from tableA where col1 = A and col2 = a1 and col4 <= 50 and col5 >= 50
Lookup thru ODBC stage? or write the table to Hash File and retrieve the value thru routine?