Lookup Override - Group By

[pbackstrom]

I am trying to get a lookup by ID that would give me a minimum value from one of the source tables:
Select ID, Min(value)
From table
where value = 0
group by ID.

I get Invalid Lookup Override even though the SQL itself validates fine (actually the sql is large, this is a simplification). I tried burying the group by in a subselect and doing a join so that the main statement didn't have group by, but no dice.

The Help file says WHERE, SELECT, and FROM are allowed in the lookup override. Is this really all? If true, any suggestions as to the best alternative? (My source control override is large with multiple joins already so I hate to do it there. I might try a separate data stream frmo the same source table with a joiner but the function I'm really after is a lookup).

 

[blom0344]

One suggestion (foolproof)

Work your entire SQL into a read-only database view and use the view as the source for a lookup. This may not be textbook solution, but in the end it just an SQL solution just the same....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[roopalidoshi]

You can write SQL in the Relation Connection Object under 'Enviornment SQL'. This wont allow informatica to invalid your SQL. The only disadvantage of this that you need to have specific connection object for this Lookup SQL.

The other solution of creating a view also works well but you may end up creating too many views for each different lookup.

I hope this helps.

-Roopali Doshi