problem with lookup ,is this a bug?

All,

I’m having problem with lookup transformation using Advanced TAB, it seems TOP 1 in a query doesn’t work and lookup cache some values no matter what. Below are the details if someone likes to try it out.

-This is the source table

CREATE TABLE [dbo].[DEPT_temp2](

[DEPTNO] [int] NOT NULL,

[NUM_Id] [int] NULL,

[date_id] [int] NULL

) ON [PRIMARY]

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,111,11111);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,222,22222);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,333,33333);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,444,44444);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,555,55555);

--This is the destination table

CREATE TABLE [dbo].[DEPT_temp2_end](

[DEPTNO] [int] NOT NULL,

[NUM_Id] [int] NULL,

[NEW] [int] NULL,

[date_id] [int] NULL

) ON [PRIMARY]

in the LOOKUP transformation

OLE DB source-àSQL command:

select * from DEPT_temp2

In LOOKUP transformation à Reference Table tab à Use a result ofa sql query

select deptno, num_id as new

from DEPT_temp2

à Columns TAB

Linked the deptno, num_id from the input to lookup column deptno, New

--à Advanced TAB --à Checked Enable memory restriction -à Modify the SQL statement

selecttop 1 *from

(select deptno, num_id as new

from DEPT_temp2) as refTable

where [refTable].[deptno] = ? and [refTable].[new] > ?

In the property of the lookup, cache type is none( I tried partial also) and enable cache is unchecked. I got the same result

Here is the result from the lookup tran

Deptnonum_id newdate_id

111122211111

122222222222

133344433333

144444444444

1555NULL55555

But expected result should be

Deptnonum_id newdate_id

111122211111

122233322222

133344433333

144455544444

1555NULL55555

What I’m missing here? Is this a bug?

Hi,

I know that when you are using memory restrictions some lookups still have caching enabled in the properties tab. Apart from the SQL used I cannot see anything wrong.

Normally I do not use a select top 1 because as far as I know, the first result found in the lookup is used.

select deptno, num_id as new from DEPT_temp2 where [deptno] = ? and [num_id] > ? order by num_id

Naturally the hash table created by the lookup when using partial or full caching disabled the possibility of using greater or smaller than. I have once solved an issue like this one using a view that handled the complex greater than statement. I simply could use the id as lookup to the view and full caching.