August 21, 2009 at 10:18 am
Can someone take a look at this statement and help me rewrite it to render the SELECT MAX (Fund_Valuation_Date) -- located near the bottom -- into a variable?
Because I'm using Sybase as a Data Source in an SSIS Data Flow, I am encountering a known SSIS bug -- using a correlated subquery w/a MAX function .. What I need to do is to break this SQL into 2 seperate statements.. (I tried SELECT TOP 1 Fund_Valuation_Date.... ORDER BY DESC and ourflavor of Sybase did not accpet this)
Step 1: I need to remove the SELECT MAX function )nested near the bottom) and render this MAX Fund_Valuation_Date into a variable
Step 2: I can use this existing SQL and substitute the SELECT MAX function w/ the variable I populated in step 1
SELECT stuff....
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
----- NEED TO REPLACE THE NEXT CONDTION w/ a VARIABLE rendered in step 1:
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))
-- the rest here is OK as is:
WHERE V.Fund_Valuation_Date = '2006-09-01'
AND S.System_Abbr_Name IN ('Fred','Barney')
AND P.System_Abbr_Name IN ('Fred','Barney')
AND P.Plan_Type_Code <> 'W08'
August 21, 2009 at 10:30 am
It is really hard to write working code without the tables and sample data to work with. You have been asked on several occasions in several threads for the DDL for the tables and for sample data.
You will get much better results if you provide as much as possible for those of us who would be willing to help. As a volunteer, I'm not taking the time to build a set of test tables and test data to work on your problem. Want help? Help us help you by giving us what we need.
I understand the actual data source is not SQL Server, but you should still be able to put together the necessary scripts (tables/sample) that would allow us to work with you.
August 21, 2009 at 11:50 am
please reply with technical solutions, not jibber jabber -- I can find that in the bafoons/democratic blogs.
August 21, 2009 at 12:06 pm
I guess I don't understand your request. You say you need to replace this:
(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))
with this:
(VV.Fund_Valuation_Date = @Variable)
and add this to the top of the proc:
declare @Variable appropriatedatatype;
select @Variable = 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's what your request seems to be for. But that's kindergarden coding that most script kiddies could manage, so I assume you must have something more that you're asking for. What am I missing?
PS: Being rude isn't going to help your cause. It just makes you look childish.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2009 at 12:21 pm
GSquared (8/21/2009)
PS: Being rude isn't going to help your cause. It just makes you look childish.
Maybe the OP is related to "Fred" or "Barney" and doesn't know better???? Or maybe the OP is simply a "Rockhead", to quote the infamous Fred....which, by insulting Lynn, seems highly possible.
-- You can't be late until you show up.
August 21, 2009 at 12:22 pm
Re-write it such that you're returning all the records on the join, rather than just the MAX record, then use the ROW_NUMBER() function to partition your recordset in a way that lets you put the total result set into a CTE and then choose the one with row_number = 1 for your final result set.
Since you aren't providing table structures and data, that's the best I can do for you.
August 21, 2009 at 12:23 pm
Actually, in taking another look at this, a variable is completely the wrong solution. Would have seen that earlier if I were able to test this, but I don't have the tables.
What you need to do is convert the correlated sub-query into a temp table, before the main query, and then join to that. Select Into should be perfect for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2009 at 12:26 pm
DBASkippack (8/21/2009)
please reply with technical solutions, not jibber jabber -- I can find that in the bafoons/democratic blogs.
If you consider asking for table definition and sample data as "jibber jabber", then a forum like this one might be the wrong place to look for an answer to your question.
Most of us trying to help by providing tested code. So you should help us help you.
August 21, 2009 at 12:38 pm
DBASkippack (8/21/2009)
please reply with technical solutions, not jibber jabber -- I can find that in the bafoons/democratic blogs.
I am sorry if I offended your sensibilities by asking you to help us help you with your problem. The help you get here is free, no cost to you.
Again, why should we even try to help you when you refuse to honor our requests for additional information that will allow us to help you with your problem?
August 21, 2009 at 1:04 pm
I'm going to preface this by saying that I am not a Sybase expert, but couldn't you put all your Sybase logic in a stored procedure and just return what you need?
If you can't do it on the Sybase side, then it looks like you may need to add some steps to your SSIS DataFlow. I don't have SSIS right now and will look at what might work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2009 at 1:08 pm
ahhh.. the Bill O'Reilly approach worked.. Thanks for the feedback..
August 21, 2009 at 1:10 pm
Yes, that could work. Use SSIS to pull the data from Sybase to staging tables (temp tables) on SQL Server and then do the work directly from those tables. Then you wouldn't have to worry about directly querying the data from the Sybase database across the network.
August 21, 2009 at 1:14 pm
DBASkippack (8/21/2009)
please reply with technical solutions, not jibber jabber -- I can find that in the bafoons/democratic blogs.
Last time I checked this guys are not payed to solve you problems, so leave your rudeness at home/office.
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."
August 21, 2009 at 1:15 pm
Not a bad idea.. I'll need to view the size of the tables on Sybase.. If they're too large, it may be prohibitive to stage all of them for this daily batch process -- but your suggestion certainly is viable.. thanks 🙂
August 21, 2009 at 1:27 pm
DBASkippack (8/21/2009)
ahhh.. the Bill O'Reilly approach worked.. Thanks for the feedback..
By the way, the insult had nothing to do with your getting any of the ideas that followed, that was just the Jibber Jabber between us professionals that resulted in that.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply