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.

Lookup not detecting new column on table

I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.

It does show up in the preview.

Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?

In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor

Unfortunately, Lookup does not automatically updates the reference metadata, but you can do this using Advanced UI. Right click the component, select 'Show Advanced Editor'. Click Refresh button in the lower-left corner, click OK.

Now open regular Lookup Editor, you should see the new columns.

|||My workaround was to script out the table as a select and change the type to sql stmt.

The column names were all the same so this didn't seem to break it.|||

Adolf,

More than a workaround; that is the best way of using a lookup transformation. There are several negative effects performance wise when you select the table from the list as opposed to provide a sql statement.

Lookup transformation by default uses RAM and you can reduce that impact by trimming the number of columns and rows that it uses (via query).

Lookup not detecting new column on table

I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.

It does show up in the preview.

Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?

In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor

Unfortunately, Lookup does not automatically updates the reference metadata, but you can do this using Advanced UI. Right click the component, select 'Show Advanced Editor'. Click Refresh button in the lower-left corner, click OK.

Now open regular Lookup Editor, you should see the new columns.

|||My workaround was to script out the table as a select and change the type to sql stmt.

The column names were all the same so this didn't seem to break it.|||

Adolf,

More than a workaround; that is the best way of using a lookup transformation. There are several negative effects performance wise when you select the table from the list as opposed to provide a sql statement.

Lookup transformation by default uses RAM and you can reduce that impact by trimming the number of columns and rows that it uses (via query).