Lookup SQL Override and using a port? 1

[jisoo23]

Ok so I'm trying to do another funny thing now in Informatica =)

I have a lookup with a SQL override statement already written and working. Now I need a little more flexibility in terms of being able to dynamically adjust that override statement. Basically what I need to do is this...the last part of the statement is "FETCH FIRST 3 ROWS ONLY". I'd like to adjust it so that "3" can be changed dynamically by adjusting a value in a designated table column.

I attempted to do another lookup and pull that value before hitting this lookup and forcing the value in via a port...no go. Am I going about this the wrong way? Or should I try something like a parameter variable?

Thanks,
Jisoo23

 

[blom0344]

Doubt if it is possible what you want.

In case your range of values to be used is a limited set the following will work quite nicely:

Create lookups for each value from the range with the specific SQL override statement.
Use a variable port to state the constant.
Build outgoing port like:

Decode(true,
1,:lkp.lkp_override_contant1(variable),
2,:lkp.lkp_override_contant2(variable),
..................
10,:lkp.lkp_override_contant10(variable),
null)


Ties Blom
Information analyst

 

[jisoo23]

A-ha, good idea. Thanks, that worked perfectly!

 

[jisoo23]

One more question, can an input port be used in the "WHERE" clause?

 

[blom0344]

One more question, can an input port be used in the "WHERE" clause?

AFAIK no...

Ties Blom
Information analyst