Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

SQL Query using result set for criteria

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
jeremyj54
Asked:
jeremyj54
  • 8
  • 6
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
Here is one -- http:Q_23203927.html
0
 
Kevin CrossChief Technology OfficerCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
jeremyj54Author Commented:
How do I call this function now?
0
 
jeremyj54Author Commented:
How do I call this with a view that I want to report in crystal from?
0
 
Kevin CrossChief Technology OfficerCommented:
SELECT * FROM dbo.fn_GetSupply(1)
0
 
jeremyj54Author Commented:
How do I send the variable from crystal reports to the function?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
jeremyj54Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
Do you not want to use the function directly in code?  Are you getting additional data in the view?
0
 
jeremyj54Author Commented:
I am getting additional data from the view, I have the function attached to another table to grab the data I need.
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
jeremyj54Author Commented:
The stored procedure worked great, one last question is it possible for the original parameter @demand to show in the table?
0
 
Kevin CrossChief Technology OfficerCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now