Get filtered result set from CTE
-
Good afternoon folks,
I could use a little help/advice with extracting some data from a CTE.
I have attached the source code of my stored procedure that includes the CTE as a text file for reference. At present, the proc just returns all the results from the CTE.
What the CTE does is get a set of config values for a device. Each level of the CTE contains a set of config values. In each level, some values may or may not be null.
What I actually want to return is a single config where each value is the first non-null value from the CTE (i.e If value A is null in recursion level 0 but populated in recursion 1 and 2 then I want the value from recursion 1).
I could do this based on a single value using a query something like:
SELECT VALUEAFROM CTE
WHERE RECURSIONLEVEL = (SELECT MIN(RECURSIONLEVEL) FROM CTE WHERE VALUEA IS NOT NULL)
But is there a way I can check and return the non-null values from the lowest recursion for all values the in the config in a set based way?
TIA,
Chris
-
Chris I looked at your code, but I'm afraid I can't visualize the results you want from the description you gave. (It's late and my mental telepathy is breaking down.) Could you post up a script to create your tables and populate them with some sample data? Then add a picture of the results as you would expect to see them?
Doing that almost guarantees that more people will attempt to help you out with your question. Sorry I can't be more help tonight.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
I've now uploaded a script to create a test db with some data.
I've also uploaded an Excel sheet showing the result set currently returned by my procedure and what I actually want to get back.
Hopefully someone can point me in the right direction.
Edit: Here's how I call the sp to get the supplied result set as well:
EXEC dbo.usp_Comms_GetMUConfig @SN = '12345678' -- varchar(8)TIA,
Chris
-
Beautiful. Thanks for making the effort. I can see what you're after now. Answer soon come.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Great.
I've implemented a solution in the interim that uses a sub-query to provide the first non-null value for each column but I'm still hoping that there's a better way to do it.
Example:
SELECTTOP 1(SELECTTOP 1 Code + Number FROM MUConfigCTE INNER JOIN dbo.PhoneNumber ON MUConfigCTE.DataOutPhoneNumberID = dbo.PhoneNumber.PhoneNumberID WHERE MUConfigCTE.DataOutPhoneNumberID IS NOT NULL ORDER BY RecursionLevel) AS DataOutPhoneNumber,
(SELECTTOP 1 Code + Number FROM MUConfigCTE INNER JOIN dbo.PhoneNumber ON MUConfigCTE.DataOutBkUpPhoneNumberID = dbo.PhoneNumber.PhoneNumberID WHERE MUConfigCTE.DataOutBkUpPhoneNumberID IS NOT NULL ORDER BY RecursionLevel),
(SELECTTOP 1 Code + Number FROM MUConfigCTE INNER JOIN dbo.PhoneNumber ON MUConfigCTE.VoiceOutPhoneNumberID = dbo.PhoneNumber.PhoneNumberID WHERE MUConfigCTE.VoiceOutPhoneNumberID IS NOT NULL ORDER BY RecursionLevel)
FROM MUConfigCTE
Good luck and thanks for your help!
Chris
-
I don't see a good way to get there with a cte. You want recursion with a while loop or a cursor, that assigns values to scalar variables using COALESCE or ISNULL.
I have a simple example for you, but I was getting an error trying to include the code.
Its been attached now.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Hi Dixie,
Thanks for the example you sent. I've been trying it out but it's not giving me exactly what I need.
In the example, the returned resultset is
idfruit meat
0NULL NULL
1NULL Beef
2Apples Chicken
3Oranges NULL
In terms of the example, what I am trying to get to is a resultset that would look like:
id fruit meat
0 Apples Beef
I'm trying to get a single row which contains the lowest (in terms of id) non-null value from each column.
Is there a way to adapt the recursive WHILE loop to return this?
TIA,
Chris
-
Hi Chris
Just as a matter of interest, what is the maximum value for RECURSIONLEVEL that ever appears in a result set from this sproc?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hi Chris,
In the real-world system which I am building this for, the max recursion level will never exceed 10 and will on most occasions be 3 or 4.
Each level will return a single row of data.
Thanks,
Chris
-
Can you please provide a full explanation of the relationship here:
FROM dbo.[Site] s
INNER JOIN dbo.Node n ON s.OwnerNodeID = n.NodeID
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Certainly,
What the proc does is assemble a config for a device. Some of the config items are in a table called SiteConfig.
Sites belong to nodes in the system and have an OwnerNodeID field in their table.
The proc looks first for config items at a site level then at the level of the node that owns the site then recursively up a hierachical tree of nodes.
Hope that makes sense. 🙂
-
Here's a hacky way of doing it...
declare @sample table (id int identity (0,1), fruit varchar(20), meat varchar(20))insert into @sample
select null, null union all
select null, 'Beef' union all
select 'Apples', 'Chicken' union all
select 'Oranges',null
select min(id) as id,
stuff(min(cast(id as char(2))+fruit),1,2,'') as fruit,
stuff(min(cast(id as char(2))+meat),1,2,'') as meat
from @sample
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Here's another hacky way of doing it. You would benefit from running the results of the CTE into a #Temp table first.
SELECT ISNULL(r0.DataOutPhoneNumber, ISNULL(r1.DataOutPhoneNumber, r2.DataOutPhoneNumber))FROM #MUConfigCTE r0
LEFT JOIN #MUConfigCTE r1 ON r1.RecursionLevel = 1
LEFT JOIN #MUConfigCTE r2 ON r2.RecursionLevel = 2
WHERE r0.RecursionLevel = 0
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thanks for the suggestions gents, very much appreciated. 🙂
I am going to try both approaches and see if there are any performance benefits.
-
Having tried out both suggestions, I found that the method using STUFF worked best as I was able to adapt my existing code using a CTE to use this method.
I also found that any columns that weren't char based such as bit and int needed to be converted to chars in my select statement before I could use the stuff function on them.
Once that was done though it worked a treat.
Thanks again for the wise advice chaps. 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply