Lookup UDF

Hi,
Can anybody provide me a Lookup UDF? I need to supply columnname,Tablename and condition dynamically and I need the scalar value in return.
Any help will be greatly appreciated...Hello again Rudra - I very recently commented on you not being around much these days :)

I have a suspicion about what you want to do and it is probably bad and likely impossible if interpreted literally. Do you want to supply a column name, table name, a key value to a function and get an alternate key value in return (i.e. have some dynamic SQL within the UDF)? If not please can you give more details about what you want this function to actually do.|||Hello again Rudra - I very recently commented on you not being around much these days :)

If not please can you give more details about what you want this function to actually do.

Thanks Pootle.:p It seems just like the old days.
Yea ,I was not around for a long time.But I always find the Yak Corral's B-E-A-U-T -Y Bcoz it always goes on and on and on...

Well,I know what I am trying to do is quite impossible but can you suggest me a way by which I can fetch a value in by lookup UDF or something like that in a long script?
In one of the script there are no of cases where things are done in this way-
case when Policy.FinanceID = 0 Then ' ' else 'Finance Co.: ' + (select name from Finance where financeId= policy.financeid)Can I use something else for that line?|||In one of the script there is no of cases where things are done in this way-
case when Policy.FinanceID = 0 Then ' ' else 'Finance Co.: ' + (select name from Finance where financeId= policy.financeid)Can I use something else for that line?Well, not knowing any more about your problem, the easiest thing would be to insert the data into the table.

INSERT Finance (financeId, name)
SELECT 0, ''
This also covers a bug bear of mine which is sticking your data into code. (linky: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx) Otherwise you could create a view:

CREATE VIEW finance_x
AS
SELECT financeId, name
FROM finance
UNION ALL
SELECT 0, ''
and use that.|||Hmm... that link was great :cool: thanks a lot Pootle...see u soon.

Lookup Transformation Fails on Oracle

Hi,

I am trying to use a lookup in a package and check for some conditions. On the advanced tab, I am trying to modify the condition from = to <=. But the same doesnt work when the target is on oracle, but the same works fine on SQL Server and DB2.

Any idea regarding the same?

Thanks,

Manish Singh

Can you execute the SQL query against the Oracle database using SQL*Plus or another similar query tool? It may be possible that Oracle does not support the specific syntax you're trying to use; since it's not included in your post it is difficult to say.

Can you post the modified SQL statement here?

|||

Yes, the query gets executed against oracle database after passing some defalt values. Below is the modified sql which I am changing on the advanced tab. The same works for SQL Server and DB2

select * from

(SELECT A_KEY, KEY_ID, SOURCE_ID, EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, EFF_START_DT, EFF_END_DT

FROM TABLEA) as refTable

where [refTable].[SOURCE_ID] = ? and [refTable].[KEY_ID] = ? and [refTable].[EFFECTIVE_FROM_DT] <= ? and [refTable].[EFFECTIVE_TO_DT] >= ? and [refTable].[EFF_START_DT] <= ? and [refTable].[EFF_END_DT] >= ?

The default condition generated is as follows:

select * from

(SELECT A_KEY, KEY_ID, SOURCE_ID, EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, EFF_START_DT, EFF_END_DT

FROM TABLEA) as refTable

where [refTable].[SOURCE_ID] = ? and [refTable].[KEY_ID] = ? and [refTable].[EFFECTIVE_FROM_DT] = ? and [refTable].[EFFECTIVE_TO_DT] = ? and [refTable].[EFF_START_DT] = ? and [refTable].[EFF_END_DT] = ?

|||Thanks for the additional information, although I'm not sure what to tell you. It looks fine to me, so I do not know why Oracle doesn't like it.