August 21, 2009 at 1:57 pm
The more I look at your code, the more I wonder what you are trying to accomplish. The sub-query you have is on the RIGHT side of a LEFT JOIN and has a bunch of correlated columns so I'm not sure how you'd get a single MAX(Fund_Valuation_Date) since it would have to be grouped on the the 3 columns used in the where clause (Fund_Number, Class_Type_Abbr_Name, Class_Type_Category) so you should/could get multiple values.
I think in SSIS, and without table definitions and some sample data to test on, you'd want your Source to be the query excluding the sub-query. Then put the sub-query in a Lookup component as your second step. Then set the columns you are getting from Fund_Valuation VV to NULL where no matching row is found. So your lookup sql would be something like this:
SELECT
Fund_Number AS VV_Fund_Number,
Class_Type_Abbr_Name,
Class_Type_Category,
MAX(Fund_Valuation_Date)
FROM
dbo.Fund_Valuation VVV
WHERE
VVV.Fund_Valuation_Date < '2006-09-01'
GROUP BY
Fund_Number,
Class_Type_Abbr_Name,
Class_Type_Category
Then you would match on all 4 columns and add VV_FUND_NUMBER to the dataflow. Step 3 would be a derived column where you say if VV_FUND_NUMBER is null set any of the columns you are using from FUND_VALUATION VV to null.
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 2:09 pm
DBASkippack (8/21/2009)
ahhh.. the Bill O'Reilly approach worked.. Thanks for the feedback..
It also just got you onto a list I keep of people I won't ever help again.
There are three people that I've ever dealt with that I actively dislike. One of them is a convicted child molester that I helped kick out of my church. You're the fourth. I hope you like being in that kind of category.
- 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 2:10 pm
I inherited this SQL. I'm wrapping up a DTS to SSIS 2008 conversion for approx 50 pkg's (just about done).. all dynamic in nature (XML config to get into the DB and use a SQL table for remaining config's) I'm using SQL Server 2000, 2005, 2008, Oracle 9, 10, Sybase 12.5, XLS (97-2003), and flat files (csv's) as input data sources to the variety of pkg's.
This particular SQL parses and runs fine (once a day) on SQL 2000. When I paste the SQL into my SSIS Data Flow (Sybase 12.5 as a data source) it does not even parse.
When I comment out the .... "AND ... SELECT MAX" condition, it parses. Of course I can't leave it that way. Thought I 'd solicit some idea's to have the SQL rewritten. I've received 2 suggestions (so far) that I'm going to try..
August 21, 2009 at 4:04 pm
DBASkippack (8/21/2009)
I inherited this SQL. I'm wrapping up a DTS to SSIS 2008 conversion for approx 50 pkg's (just about done).. all dynamic in nature (XML config to get into the DB and use a SQL table for remaining config's) I'm using SQL Server 2000, 2005, 2008, Oracle 9, 10, Sybase 12.5, XLS (97-2003), and flat files (csv's) as input data sources to the variety of pkg's.This particular SQL parses and runs fine (once a day) on SQL 2000. When I paste the SQL into my SSIS Data Flow (Sybase 12.5 as a data source) it does not even parse.
When I comment out the .... "AND ... SELECT MAX" condition, it parses. Of course I can't leave it that way. Thought I 'd solicit some idea's to have the SQL rewritten. I've received 2 suggestions (so far) that I'm going to try..
So all this justifies your being rude and insulting? You realize you have alienated yourself from some of the best SQL people by your single act on this thread. Not only that, you probably would have had this issue resolved already had you acted with the same professionalism we have shown you and provided the information (ddl for the tables/sample data/expected results) that you had been asked for several times.
And your continued lack of professionalism continues. You have yet to acknowledge and apologize for your rude and insulting comment, and in fact have shown smugness in getting more information from it. I truely hope this is not how you are around your co-workers.
August 22, 2009 at 11:52 pm
Welcome to my list, Skipdick.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 23, 2009 at 6:39 am
To the OP: another not so recent SSC member, working on the Fora2009 rudiness program ? :sick:
btw, did you try it like the good old msaccess worked around your problem ? (Create a view to accomplish the nested query part and join with the view !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 25, 2009 at 5:43 am
wanted to apologize for my comment(s) last week. You guys (& gals) have been nothing short of tremendously helpful to me over the years. Sincerely sorry -- my bad...
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply