Solved

SQL Query using result set for criteria

Posted on 2008-10-07
14
178 Views
Last Modified: 2012-05-05
I am trying to find a way with sql server 2005 to query a table with a demand column and a supply column, I want to search a top level demand and find the supply/s for it then I want to see if those supplies are in the demand column also and find the supplies for them and so on.  I have attached a small file that may explain it a little better.  Thanks
Sql-Table.xls
0
Comment
Question by:jeremyj54
  • 8
  • 6
14 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Here is a recursive UDF that does this, although you can write a really cool common table expression to do it.  Since I have seen it so much from my EE experts I won't claim the fame; therefore, I will find an example and post for you.

http:Q_23680666.html
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Here is one -- http:Q_23203927.html
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Here is an example with your data and UDF method -- I will leave the cte for Angel Eyes, Chapman, or Brandon -- I am sure they will drop by especially since I didn't use a CTE. :)
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[fn_GetSupply] 

(

	-- Input parameters

	@demand int

)

RETURNS 

@TBL TABLE 

(

	-- Returned table layout

        supply int

)

AS

BEGIN

	DECLARE @currentID int  -- item currently being processed

	

        -- grab first child record

        SELECT @currentID = MIN(supply)

        FROM TABLENAME WHERE demand = @demand

 

	WHILE @currentID IS NOT NULL

		BEGIN

			-- explode downward

			INSERT INTO @TBL

            SELECT supply

            FROM dbo.fn_GetSupply(@currentID)

			

-- insert id matching input

        INSERT INTO @TBL

        SELECT supply

        FROM tablename WHERE supply = @currentID
 

			-- grab the next record

			SELECT @currentID = MIN(supply)

        FROM TABLENAME WHERE demand = @demand AND supply > @currentID

		END

	RETURN 

END 

Open in new window

0
 

Author Comment

by:jeremyj54
Comment Utility
How do I call this function now?
0
 

Author Comment

by:jeremyj54
Comment Utility
How do I call this with a view that I want to report in crystal from?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
SELECT * FROM dbo.fn_GetSupply(1)
0
 

Author Comment

by:jeremyj54
Comment Utility
How do I send the variable from crystal reports to the function?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
SELECT * FROM dbo.fn_GetSupply(@parameter)

You just send the parameter into the dbo.fn_GetSuppy like above.  It is like calling a stored procedure.
0
 

Author Comment

by:jeremyj54
Comment Utility
My problem is that I am trying to use the function which is working great thank you as part of a view and I want crystal reports to be able to report off this view when the user selects a certain demand id.  If I try to save the view now without a static demand Id it gives me an error "insufficient number of arguments"  is what im trying to do doable or is there another way I should be doing it?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Do you not want to use the function directly in code?  Are you getting additional data in the view?
0
 

Author Comment

by:jeremyj54
Comment Utility
I am getting additional data from the view, I have the function attached to another table to grab the data I need.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Ok, you can either change the VIEW to be a procedure that takes in a parameter that you can pass along to this function OR you can use this function like any other table and JOIN it to VIEW from the report.

SELECT s.supply, v.column1, v.column2, v.column3, v.column4
FROM dbo.fn_GetSupply(@parameter) s JOIN viewName v
ON s.supply = v.supply
0
 

Author Comment

by:jeremyj54
Comment Utility
The stored procedure worked great, one last question is it possible for the original parameter @demand to show in the table?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
Comment Utility
Yes just alter this and change all the inserts to add in the demand id if you want this to be the demand id in the table:

(
      -- Returned table layout
        supply int
        , demand int /* ADDED THIS LINE */
)

If you want all the records to match the original demand id passed in you can do it easier, by doing this:

SELECT @parameter AS demand, s.supply, v.column1, v.column2, v.column3, v.column4
FROM dbo.fn_GetSupply(@parameter) s JOIN viewName v
ON s.supply = v.supply
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now