August 11, 2009 at 10:59 am
/* This SQL will not execute unless I comment the SELECT MAX FUNCTION below: */
SELECT DISTINCT RTRIM(F.Fund_Number) AS Fund_Number,
RTRIM(C.Class_Type_Abbr_Name) AS Class_Type_Abbr_Name
...
...
FROM dbo.Fund F
INNER JOIN dbo.Fund_Valuation V ON F.Fund_Number = V.Fund_Number
LEFT OUTER JOIN dbo.Fund_Valuation VV ON V.Fund_Number = VV.Fund_Number
-- IF I COMMENT THE NEXT 4 LINES, the Statement WORK's FINE
AND (VV.Fund_Valuation_Date =
(SELECT MAX (Fund_Valuation_Date) FROM dbo.Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2009-08-01'
AND VVV.Fund_Number = V.Fund_Number))
WHERE V.Fund_Valuation_Date = '2009-08-01'
AND S.System_Abbr_Name IN ('MyVal','MyVal_2')
-- I NEED the SELECT MAX FUNCTION IN HERE.. Any Suggestions?
August 11, 2009 at 11:27 am
I don't think we really know enough to help here. We will need CREATE and INSERT statements with sample tables/data to figure out what's wrong. As is, I drew up some fake tables to see if I could reproduce your problem, but not knowing anything about the data, I could not. My query with the MAX function worked just fine.
SET NOCOUNT ON
DECLARE @Fund TABLE
(
Fund_Number INT
)
DECLARE @Fund_Valuation TABLE
(
Fund_Number INT
, Fund_Valuation_Date DATETIME
)
INSERT INTO @Fund ( Fund_Number ) VALUES ( 0 )
INSERT INTO @Fund ( Fund_Number ) VALUES ( 1 )
INSERT INTO @Fund ( Fund_Number ) VALUES ( 2 )
INSERT INTO @Fund ( Fund_Number ) VALUES ( 3 )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 0, '8/1/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 0, '8/12/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 1, '8/16/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 1, '7/1/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 1, '8/1/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 2, '8/27/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 2, '12/12/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 2, '8/1/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 3, '8/15/2009' )
INSERT INTO @Fund_Valuation ( Fund_Number, Fund_Valuation_Date ) VALUES ( 3, '8/14/2009' )
SELECT DISTINCT RTRIM( F.Fund_Number ) AS Fund_Number
FROM @Fund F
INNER JOIN @Fund_Valuation V
ON F.Fund_Number = V.Fund_Number
LEFT OUTER JOIN @Fund_Valuation VV
ON V.Fund_Number = VV.Fund_Number
AND ( VV.Fund_Valuation_Date =
( SELECT MAX ( Fund_Valuation_Date )
FROM @Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2009-08-01'
AND VVV.Fund_Number = V.Fund_Number
)
)
WHERE V.Fund_Valuation_Date = '2009-08-01'
It returned:
Fund_Number
------------
0
1
2
If you could provide some sample data and what you expect the output to look like, that would really help us help you.
August 11, 2009 at 12:08 pm
Untested, but you could give this a try:
with MaxFundDate (
Fund_Number,
Fund_Valuation_Date
) as (
select
Fund_Number,
max(Fund_Valuation_Date
from
dbo.Fund_Valuation
where
Fund_Valuation_Date < '2009-08-01' -- dateadd(mm, datediff(mm, 0, getdate()), 0), will return first day of current month
)
SELECT DISTINCT
RTRIM(F.Fund_Number) AS Fund_Number,
RTRIM(C.Class_Type_Abbr_Name) AS Class_Type_Abbr_Name
...
...
FROM
dbo.Fund F
INNER JOIN dbo.Fund_Valuation V
ON (F.Fund_Number = V.Fund_Number)
LEFT OUTER JOIN (dbo.Fund_Valuation VV
inner join MaxFundDate mfd
on (VV.Fund_Number = mfd.Fund_Number
and VV.Fund_Valuation_Date = mfd.Fund_Valuation_Date))
ON (V.Fund_Number = VV.Fund_Number)
WHERE
V.Fund_Valuation_Date = '2009-08-01' -- dateadd(mm, datediff(mm, 0, getdate()), 0), will return first day of current month
AND S.System_Abbr_Name IN ('MyVal','MyVal_2')
August 11, 2009 at 1:23 pm
Did I miss the part which explains what doesn't work? Sorry if I'm being dense- I just don't get a good understanding of what is not happening. Syntax error? returns no data? churns forever?
A few more details would help. Thanks!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 11, 2009 at 1:58 pm
Not 100% on this, but from what I understand (and after a few beers probably not alot!) this is what I've come up with...
SELECT
...
MaxFund.MaxDate
...
FROM dbo.Fund F
JOIN dbo.Fund_Valuation FV ON F.Fund_Number = FV.Fund_Number
LEFT JOIN
(SELECT Fund_Number, MAX(Fund_Valuation_Date) AS MaxDate
FROM Fund_Valuation
WHERE Fund_Valuation_Date < '2009-08-01'
GROUP BY Fund_Number) AS MaxFund
ON MaxFund.Fund_Number = FV.Fund_Number
WHERE
...
?? Dave
August 11, 2009 at 10:18 pm
Matt Miller (8/11/2009)
Did I miss the part which explains what doesn't work? Sorry if I'm being dense- I just don't get a good understanding of what is not happening. Syntax error? returns no data? churns forever?
You're not alone there. What are the symptoms of it not working?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2009 at 5:34 am
Here's the situation. I am converting DTS pkg's to SSIS (2008). When I copy this SQL from DTS (which executes just fine in DTS) and paste it into my SSIS Data Flow (data source) I receive the error:
Incorrect Syntax neat the Keyword 'WHERE'. Unable to retrieve column information from the data source. Make sure your target table in the database is available.
THIS IS the 50th or so package I've converted from DTS to SSIS. The data source is Sybase.. My connection works fine for other Sybase sources. My target table is there and available. AND -- the clincher -- IF I rearrange this SQL to relocate the WHERE CLAUSE and seat it BEFORE the last SELECT MAX function, the SQL passes validation and runs in SSIS just fine. Problem is, I don't believe I get the expected source data by rearranging. (SEE *** REARRANGED SQL after my ORIGINAL SQL below:)
ORIGINAL 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
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'
REARRRANGED SQL:
---------------
starting from the LAST .. Outer Join statement above, I move the SELECT MAX function to the bottom and this passes SSIS validation in the Data Flow data source)
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
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'
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))
August 12, 2009 at 5:46 am
Why not change this:
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))
to this:
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)
Maybe the second paren is throwing things off.
August 12, 2009 at 6:05 am
The 2nd paren is valid.. (I tried removing it for the heckuvit -- no difference)
Also, just ran the exact SQL in Embarcadero's DBArtisan against the source DB and it ran fine.. For some reason, SSIS's Data Source Editor is choking on the T-SQL syntax.. (Microsoft SSIS bug ????)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply