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.
