SolvedPrivate

SSIS package and using a Lookup tranformation

Posted on 2013-01-15
6
38 Views
Last Modified: 2016-02-10
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_Level_Nbr
FROM   cdr.Account_Main_Financial
WHERE  Account_Main_Financial_Id > 0
       AND current_usage_ind = 1
       ) [refTable]
WHERE 4390 BETWEEN [refTable].[Account_Main_Start_Code] AND [refTable].[Account_Main_End_Code]
AND [refTable].Account_Main_Financial_Level_Nbr =
        ( SELECT MAX(Account_Main_Financial_Level_Nbr) FROM cdr.Account_Main_Financial
                WHERE [refTable].Account_Main_Financial_Id =Account_Main_Financial_Id )
               
Account_Main_Financial_Id        Account_Main_Start_Code        Account_Main_End_Code        Account_Main_Financial_Level_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_Level_Nbr
FROM   cdr.Account_Main_Financial
WHERE  Account_Main_Financial_Id > 0
       AND current_usage_ind = 1
       ) [refTable]
WHERE ? BETWEEN [refTable].[Account_Main_Start_Code] AND [refTable].[Account_Main_End_Code]
AND [refTable].Account_Main_Financial_Level_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.
0
Comment
Question by:ExpertSkills
  • 3
  • 3
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38781162
Something like this perhaps:
SELECT  a.Account_Main_Financial_Id,
        a.Account_Main_Start_Code,
        a.Account_Main_End_Code,
        d.Account_Main_Financial_Level_Nbr
FROM    cdr.Account_Main_Financial a
        INNER JOIN (SELECT  Account_Main_Financial_Id,
                            MAX(Account_Main_Financial_Level_Nbr) Account_Main_Financial_Level_Nbr
                    FROM    cdr.Account_Main_Financial
                    GROUP BY Account_Main_Financial_Id
                   ) d ON a.Account_Main_Financial_Id = d.Account_Main_Financial_Id
WHERE   a.Account_Main_Financial_Id > 0
        AND a.current_usage_ind = 1
        AND 4390 BETWEEN a.Account_Main_Start_Code AND a.Account_Main_End_Code

Open in new window

0
 

Author Comment

by:ExpertSkills
ID: 38781176
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_Level_Nbr
1        0        4999        2
2        0        9999        1
20        4000        4499        3
47        4390        4399        4
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38781227
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

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ExpertSkills
ID: 38781258
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?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38781298
I am afraid I have no idea.
0
 

Author Closing Comment

by:ExpertSkills
ID: 38781326
Appreciate your help! Will try to get the code integrated into the component!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Executing dynamic queries via ssis 2012 6 15
T-SQL Default value in Select? 5 28
SQL Function NOT ROUND 9 11
Proper Case SQL Command 2 13
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question