August 18, 2009 at 7:28 am
Here's a doozie... a known issue w/ SSIS is preventing a successful parse of a T-SQL statement that works perfectly fine in DTS.
The "MASTER SQL" below is failing the SSIS Parse on this condition -- a MAX function w/in a correlated subquery (listed about 4/5 of the way down in the MASTER SQL):
AND (VV.Fund_Valuation_Date = (SELECT MAX (Fund_Valuation_Date)
FROM dbo.Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2006-09-01'
AND VVV.Fund_Number = V.Fund_Number
AND VVV.Class_Type_Abbr_Name = V.Class_Type_Abbr_Name
AND VVV.Class_Type_Category = V.Class_Type_Category))
I'd like to break this into 2 SQL statements:
1st - render the MAX (Fund_Valuation_Date) into a variable
2nd - replace the condition above w/ the populated variable
Here is the MASTER SQL statement in it's original form. Not being a T-SQL guru -- how would I rewite this to 1st populate the MAX (Fund_Valuation_Date) into a variable? (I'll then replace the condition w/ the populated variable)
MASTER SQL
==========
SELECT DISTINCT RTRIM(F.Fund_Number) AS Fund_Number,
RTRIM(C.Class_Type_Abbr_Name) AS Class_Type_Abbr_Name,
RTRIM(C.Class_Type_Category) AS Class_Type_Category,
RTRIM(F.Fund_Name) AS Fund_Name,
RTRIM(F.Fund_Active_Ind) AS Fund_Active_Ind,
F.Fund_Inactive_Date,
-- RTRIM(C.Cusip) AS CUSIP,
COALESCE(C.CUSIP,'UNK' + RIGHT('000000' + RTRIM(CAST(F.Fund_Number AS varchar(10))),6)) as CUSIP,
RTRIM(F.Index_ID) AS Index_ID,
RTRIM(F.Fund_Family_Abbr_Name) AS Fund_Family_Abbr_Name,
RTRIM(F.Sub_Advisor_Abbr_Name) AS Sub_Advisor_Abbr_Name,
RTRIM(F.Asset_Type) AS Asset_Type,
RTRIM(F.Fund_Market_Type) AS Fund_Market_Type,
RTRIM(F.Market_Capitalization) AS Market_Capitalization,
RTRIM(F.Strategic_Category_Type) AS Strategic_Category_Type,
RTRIM(P.Admin_Code) AS Admin_Code,
RTRIM(P.IID_Div) AS IID_Div,
V.Fund_Valuation_Date,
V.Fund_Actual_Price,
COALESCE(D.St_Cap_Gain_Rate,0) AS St_Cap_Gain_Rate,
COALESCE(D.Lt_Cap_Gain_Rate,0) AS Lt_Cap_Gain_Rate,
COALESCE(D.Ordinary_Income_Rate,0) AS Ordinary_Income_Rate,
VV.Fund_Valuation_Date AS Prior_Fund_Valuation_Date,
VV.Fund_Actual_Price AS Prior_Fund_Actual_Price
FROM dbo.Fund F
INNER JOIN dbo.Fund_Valuation V
ON F.Fund_Number = V.Fund_Number
INNER JOIN dbo.Fund_Class C
ON F.Fund_Number = C.Fund_Number
AND V.Class_Type_Abbr_Name = C.Class_Type_Abbr_Name
AND V.Class_Type_Category = C.Class_Type_Category
INNER JOIN dbo.Fund_Separate_Account S
ON F.Fund_Number = S.Fund_Number
AND V.Class_Type_Abbr_Name = S.Class_Type_Abbr_Name
AND V.Class_Type_Category = S.Class_Type_Category
INNER JOIN dbo.Plan_Table P
ON F.Fund_Number = P.Fund_Number
AND V.Class_Type_Abbr_Name = P.Class_Type_Abbr_Name
AND V.Class_Type_Category = P.Class_Type_Category
LEFT OUTER JOIN dbo.Fund_Dividend D
ON F.Fund_Number = D.Fund_Number
AND V.Fund_Valuation_Date = D.Fund_Dividend_Ex_Date
LEFT OUTER JOIN dbo.Fund_Valuation VV
ON V.Fund_Number = VV.Fund_Number
AND V.Class_Type_Abbr_Name = VV.Class_Type_Abbr_Name
AND V.Class_Type_Category = VV.Class_Type_Category
-- THIS IS THE CONDITION CAUSING SSIS Task to FAIL the PARSE:
AND (VV.Fund_Valuation_Date = (SELECT MAX (Fund_Valuation_Date)
FROM dbo.Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2006-09-01'
AND VVV.Fund_Number = V.Fund_Number
AND VVV.Class_Type_Abbr_Name = V.Class_Type_Abbr_Name
AND VVV.Class_Type_Category = V.Class_Type_Category))
WHERE V.Fund_Valuation_Date = '2006-09-01'
AND S.System_Abbr_Name IN ('GARWN','GAAS')
AND P.System_Abbr_Name IN ('GARWN','GAAS')
AND P.Plan_Type_Code <> 'W08'
August 18, 2009 at 7:41 am
since your subquery is going to return a different value for each row of the outer query, it would be impossible to extract a single variable and then invoke the procedure for every row with that variable.
what you could do though, if your goal is just to avoid using the MAX statement, is rewrite your subquery:
AND (VV.Fund_Valuation_Date = (
SELECT TOP 1 VVV.Fund_Valuation_Date
FROM dbo.Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2006-09-01'
AND VVV.Fund_Number = V.Fund_Number
AND VVV.Class_Type_Abbr_Name = V.Class_Type_Abbr_Name
AND VVV.Class_Type_Category = V.Class_Type_Category
ORDER BY Fund_Valuation_Date DESC
)
)
WHERE V.Fund_Valuation_Date = '2006-09-01'
AND S.System_Abbr_Name IN ('GARWN','GAAS')
AND P.System_Abbr_Name IN ('GARWN','GAAS')
AND P.Plan_Type_Code 'W08'
But, here's a problem: you're just saying "Fund_Valuation_Date". That particular column exists in multiple places, you're going to need to use the alias to refer to it, hence calling it VVV.Fund_Valuation_Date.
I mean that might even allow you to use the MAX, since i'm not too convinced that you can't use MAX in a subquery in SSIS
August 18, 2009 at 8:37 am
What is needed is the DDL for the tables (or a least enough of it to work on the query), sample data for the tables (fake data is fine as long as it is representative of the problem at hand), expected results based on the sample data.
With 474 points, you've been around long enough to know that the more you do up front to help us the better answers you will get. Need help with this request, check out the first article in my signature block and follow the instructions it provides.
August 18, 2009 at 9:01 am
I tried the TOP 1 idea prior to your suggestion.. but due to the fact that my source is Sybase, the TOP 1 ORDER BY idea does not work.... (great minds think alike)
August 18, 2009 at 9:06 am
Did you try changing the subquery to use the alias, and see if it still crashes?
August 18, 2009 at 9:35 am
I'm familiar w/ alias.. but not sure what you mean by "changing the query to use the alias" ???
August 18, 2009 at 9:46 am
AND (VV.Fund_Valuation_Date = (SELECT MAX (Fund_Valuation_Date)
FROM dbo.Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2006-09-01'
AND VVV.Fund_Number = V.Fund_Number
AND VVV.Class_Type_Abbr_Name = V.Class_Type_Abbr_Name
AND VVV.Class_Type_Category = V.Class_Type_Category))
that fund_valuation_date is being refered to by V, VV, and VVV. It's not going to know which one to use, unless you tell it VVV.Fund_Valuation_Date
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply