ExpertSkills
asked on
SSIS package and using a Lookup tranformation
I am currently working on a SSIS package and using a Lookup tranformation to output a value.
Lookup transformation uses a custom query as follows but the query returns mulitple results.
select * from (SELECT Account_Main_Financial_Id,
Account_Main_Start_Code,
Account_Main_End_Code,
Account_Main_Financial_Lev el_Nbr
FROM cdr.Account_Main_Financial
WHERE Account_Main_Financial_Id > 0
AND current_usage_ind = 1
) [refTable]
WHERE 4390 BETWEEN [refTable].[Account_Main_S tart_Code] AND [refTable].[Account_Main_E nd_Code]
AND [refTable].Account_Main_Fi nancial_Le vel_Nbr =
( SELECT MAX(Account_Main_Financial _Level_Nbr ) FROM cdr.Account_Main_Financial
WHERE [refTable].Account_Main_Fi nancial_Id =Account_Main_Financial_Id )
Account_Main_Financial_Id Account_Main_Start_Code Account_Main_End_Code Account_Main_Financial_Lev el_Nbr
1 0 4999 2
2 0 9999 1
20 4000 4499 3
47 4390 4399 4
I tried to use a parameter to resolve this issue but parameters in a sub query are not permitted in the custom query?
select * from (SELECT Account_Main_Financial_Id,
Account_Main_Start_Code,
Account_Main_End_Code,
Account_Main_Financial_Lev el_Nbr
FROM cdr.Account_Main_Financial
WHERE Account_Main_Financial_Id > 0
AND current_usage_ind = 1
) [refTable]
WHERE ? BETWEEN [refTable].[Account_Main_S tart_Code] AND [refTable].[Account_Main_E nd_Code]
AND [refTable].Account_Main_Fi nancial_Le vel_Nbr =
( SELECT MAX(Account_Main_Financial _Level_Nbr ) FROM cdr.Account_Main_Financial
WHERE ? BETWEEN [Account_Main_Start_Code] AND [Account_Main_End_Code] )
Esentially, I need to return the Max(Account_Main_Financial _Level_Nbr ) so that only Account_Main_Financial_Id= 47 is returned.
Unfortunately, its not 1=1 and there are multiple rows being returned. Is there an easier way to do this?
Many thanks in anticipation.
Lookup transformation uses a custom query as follows but the query returns mulitple results.
select * from (SELECT Account_Main_Financial_Id,
Account_Main_Start_Code,
Account_Main_End_Code,
Account_Main_Financial_Lev
FROM cdr.Account_Main_Financial
WHERE Account_Main_Financial_Id > 0
AND current_usage_ind = 1
) [refTable]
WHERE 4390 BETWEEN [refTable].[Account_Main_S
AND [refTable].Account_Main_Fi
( SELECT MAX(Account_Main_Financial
WHERE [refTable].Account_Main_Fi
Account_Main_Financial_Id Account_Main_Start_Code Account_Main_End_Code Account_Main_Financial_Lev
1 0 4999 2
2 0 9999 1
20 4000 4499 3
47 4390 4399 4
I tried to use a parameter to resolve this issue but parameters in a sub query are not permitted in the custom query?
select * from (SELECT Account_Main_Financial_Id,
Account_Main_Start_Code,
Account_Main_End_Code,
Account_Main_Financial_Lev
FROM cdr.Account_Main_Financial
WHERE Account_Main_Financial_Id > 0
AND current_usage_ind = 1
) [refTable]
WHERE ? BETWEEN [refTable].[Account_Main_S
AND [refTable].Account_Main_Fi
( SELECT MAX(Account_Main_Financial
WHERE ? BETWEEN [Account_Main_Start_Code] AND [Account_Main_End_Code] )
Esentially, I need to return the Max(Account_Main_Financial
Unfortunately, its not 1=1 and there are multiple rows being returned. Is there an easier way to do this?
Many thanks in anticipation.
ASKER
Thanks for your solution but when executing, still get multiple rows returned;
Account_Main_Financial_Id Account_Main_Start_Code Account_Main_End_Code Account_Main_Financial_Lev el_Nbr
1 0 4999 2
2 0 9999 1
20 4000 4499 3
47 4390 4399 4
Account_Main_Financial_Id Account_Main_Start_Code Account_Main_End_Code Account_Main_Financial_Lev
1 0 4999 2
2 0 9999 1
20 4000 4499 3
47 4390 4399 4
If all you want is a single row returned then do something like this:
;WITH MyCTE AS (
SELECT Account_Main_Financial_Id,
Account_Main_Start_Code,
Account_Main_End_Code,
Account_Main_Financial_Level_Nbr,
ROW_NUMBER() OVER (ORDER BY Account_Main_Financial_Level_Nbr DESC) Row
FROM cdr.Account_Main_Financial a
WHERE Account_Main_Financial_Id > 0
AND current_usage_ind = 1
AND 4390 BETWEEN Account_Main_Start_Code AND Account_Main_End_Code
)
SELECT Account_Main_Financial_Id,
Account_Main_Start_Code,
Account_Main_End_Code,
Account_Main_Financial_Level_Nbr
FROM MyCTE
WHERE Row = 1
ASKER
That works a treat in Query Analyser but not in the SSIS Lookup Transformation; Advanced > Modify the SQL Statement.
Any way to incorporate this into the Lookup transformation?
Any way to incorporate this into the Lookup transformation?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate your help! Will try to get the code integrated into the component!
Open in new window