Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: tree in database - I can't make a function

    I have table Categories to store hierarchical data like in tree, with columns:
    CategoryId
    Name
    Lft
    Rgt
    ParentId

    I would like to create function to pass CategoryId and get subtree. I have that query and I would like to create function using it:
    Code:
                    SELECT node.categoryid, node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
    		FROM categories AS node,
    			categories AS parent,
    			categories AS sub_parent,
    			(
    				SELECT node.name, (COUNT(parent.name) - 1) AS depth
    				FROM categories AS node,
    				categories AS parent
    				WHERE node.lft BETWEEN parent.lft AND parent.rgt
    				AND node.categoryid = 1
    				GROUP BY node.name, node.lft
    				ORDER BY node.lft
    			)AS sub_tree
    		WHERE node.lft BETWEEN parent.lft AND parent.rgt
    			AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    			AND sub_parent.name = sub_tree.name
    		GROUP BY node.categoryid, node.name, node.lft, depth
    		ORDER BY node.lft;
    So I thought that maybe I should create temp table and insert in this table data from this query so I have written:
    Code:
    CREATE FUNCTION dbo.GetCategories
    	(
    	@categoryId int = 1
    	)
    RETURNS @t TABLE (int CategoryId, CategoryName nvarchar(250), int Depth)
    
    AS
    	BEGIN
    
    
    		INSERT INTO @t
    
    		SELECT node.categoryid, node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
    		FROM categories AS node,
    			categories AS parent,
    			categories AS sub_parent,
    			(
    				SELECT node.name, (COUNT(parent.name) - 1) AS depth
    				FROM categories AS node,
    				categories AS parent
    				WHERE node.lft BETWEEN parent.lft AND parent.rgt
    				AND node.categoryid = @categoryId
    				GROUP BY node.name, node.lft
    				ORDER BY node.lft
    			)AS sub_tree
    		WHERE node.lft BETWEEN parent.lft AND parent.rgt
    			AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    			AND sub_parent.name = sub_tree.name
    		GROUP BY node.categoryid, node.name, node.lft, depth
    		ORDER BY node.lft;
    
    
    	RETURN
    	END

    But I can't save it - I have error: The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

    What can I do ?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Remove the order by clauses. Does a function really need these? if it does, maybe this needs to be a stored procedure. If not, the calling query shoudl choose how to order the results.

  3. #3
    Join Date
    Dec 2008
    Posts
    53
    But can stored procedure return temp table like above function ? If yes it would be wonderful.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It can return a result set that can be stored in a temp table, but again, why would it need to be ordered?

  5. #5
    Join Date
    Dec 2008
    Posts
    53
    Because in trees you get sorted tree if you go left order nodes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •