Help on a complex query that may be easier than what we have??

Question

Post reply

Help on a complex query that may be easier than what we have??

david.john.gilligan

Default port

Points: 1471
More actions
September 12, 2013 at 3:06 am

#282344

Hello all.
We have a solution to a problem but think there is a much easier way to do this...I was thinking a join with a "temp table....what we have is a "heiarchy" in a tree structure that can contain hundres of different items, each will have a version number, or a null as default. The example below shows one way to do it:
select *
FROM sometable
where NodeId ='5100' and NodeVersion =1 and SUBSTRING(CICS,0, LEN('1.1')+1)<>'1.1'
union
select *
FROM factTenderItemValue
where NodeId ='5130.1.1' and NodeVersion =2
5100 is the "parent" of 5130....what we want is to recognize that 5130.1.1 version to has been selected (we do that in C#) and incluse all children of 5100 with a version number of 1 BUT exclude version 1 from 5300 and use the selected version #2.....the sql will be dynamically created via C#...whis does work but can produce hundreds of statements...
Any ideas/suggetions welcomed....my thought was to populate an "#IWant" temp table and a join.....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

See if one of these links can help (from SSC.com's own Jeff Moden):

http://www.sqlservercentral.com/articles/94040/

http://www.sqlservercentral.com/articles/94570/

http://www.sqlservercentral.com/articles/72503/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

I love hierarchy problems. If Jeff's articles don't help you, I suggest you post DDL, some sample data and the expected results.

Along will come many I'm sure offering various suggestions, one of which may be the tested solution you seek.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

david.john.gilligan Default port Points: 1471 More actions · Answer 3

Actually they have helped but I think the new solution (basically creating a list in C# or a temp table) and populating it with all "version 1" data (or whatever they chose as the top version in the tree) then replacing those with the times when the user has selected a different version of a child with the needed changes using a union seems to do the job, and for our needs, gets the job done in under a couple of seconds....which is definitely OK, considering that this not a "quick and easy" task IMHO....