SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help to improve the query with a loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vpawa
Starting Member

3 Posts

Posted - 11/05/2012 :  07:13:58  Show Profile  Reply with Quote
Hi,

Im trying to loop my query but dont know how to do. can somebody help me to improve the query?

I have created the following query.

SELECT MEIT.client, MEIT.tree_type, 0 AS tree_level, MEIT.menu_id, MEIT.parent_menu_id, MEIT.menu_type,
MEIT.licence_ref, MEIT.icon_type, MEIT.title_nl, MEIT.title_en, MEIT.title_fr, MEIT.title_de,
CONVERT(NVARCHAR, RIGHT(N'0000' +
CONVERT(NVARCHAR, ROW_NUMBER() OVER (PARTITION BY MEIT.client, MEIT.tree_type
ORDER BY CASE WHEN MEIT.menu_type = 5 THEN 5 WHEN MEIT.menu_type = 6 THEN 6 ELSE 1 END,
MEIT.sequence_no, MEIT.title_no)),4)) AS tree_sequence
FROM uvimenuitem MEIT
WHERE MEIT.parent_menu_id = N'!'
UNION ALL
SELECT MEIT.client, MEIT.tree_type, TREES.tree_level+1 AS tree_level, MEIT.menu_id, MEIT.parent_menu_id, MEIT.menu_type,
MEIT.licence_ref, MEIT.icon_type, MEIT.title_nl, MEIT.title_en, MEIT.title_fr, MEIT.title_de,
CONVERT(NVARCHAR, TREES.tree_sequence +
RIGHT(N'0000' +
CONVERT(NVARCHAR, ROW_NUMBER() OVER (PARTITION BY MEIT.client, MEIT.tree_type
ORDER BY CASE WHEN MEIT.menu_type = 5 THEN 5 WHEN MEIT.menu_type = 6 THEN 6 ELSE 1 END,
MEIT.sequence_no, MEIT.title_no)),4)) AS tree_sequence
FROM uvimenuitem MEIT
INNER JOIN tree_structure TREES
ON TREES.client = MEIT.client
AND TREES.tree_type = MEIT.tree_type
AND TREES.menu_id = MEIT.parent_menu_id

now im trying to loop TREES.tree_level+1 10 times. does somebody knows how to get that withoud creating 10 view tables?

Thanks
Vishal

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  07:30:31  Show Profile  Reply with Quote
I didn't quite follow the logic you are trying to implement. You may need a recursive query if the results of each level is used to compute the results of the next level. The following can be used if you just want to run the same query ten times, but with n=1,2,..10
.....
UNION ALL
SELECT MEIT.client,
       MEIT.tree_type,
       TREES.tree_level + n  AS tree_level,
       MEIT.menu_id,
....
FROM   
	(
		VALUES (1) ,(2),(3),(4),(5),(6),(7),(8),(9),(10)
	)s(n)
	CROSS JOIN 
	(
		uvimenuitem MEIT
		AND TREES.menu_id = MEIT.parent_menu_id 
....
    )

Edited by - sunitabeck on 11/05/2012 07:30:50
Go to Top of Page

vpawa
Starting Member

3 Posts

Posted - 11/05/2012 :  07:59:13  Show Profile  Reply with Quote
I need it to compute the next tree level. I tought the most easy way is to loop the query, but im not getting the result what im looking for with the query. Do you hav a suggestion how to calulate the different levels?

thanks

quote:
Originally posted by sunitabeck

I didn't quite follow the logic you are trying to implement. You may need a recursive query if the results of each level is used to compute the results of the next level. The following can be used if you just want to run the same query ten times, but with n=1,2,..10
.....
UNION ALL
SELECT MEIT.client,
       MEIT.tree_type,
       TREES.tree_level + n  AS tree_level,
       MEIT.menu_id,
....
FROM   
	(
		VALUES (1) ,(2),(3),(4),(5),(6),(7),(8),(9),(10)
	)s(n)
	CROSS JOIN 
	(
		uvimenuitem MEIT
		AND TREES.menu_id = MEIT.parent_menu_id 
....
    )


Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  08:06:23  Show Profile  Reply with Quote
I must admit that I didn't follow the logic you are trying to implement. What I posted earlier was a wild guess, and I am not surprised it didn't work. Can you post a simplified version (with may be fewer columns) and some sample data that is in your input tables and the output you are trying to get?
Go to Top of Page

vpawa
Starting Member

3 Posts

Posted - 11/05/2012 :  08:29:42  Show Profile  Reply with Quote
Can i get your mailadress so i can send you some more information.

quote:
Originally posted by sunitabeck

I must admit that I didn't follow the logic you are trying to implement. What I posted earlier was a wild guess, and I am not surprised it didn't work. Can you post a simplified version (with may be fewer columns) and some sample data that is in your input tables and the output you are trying to get?

Go to Top of Page

karthik0805
Starting Member

14 Posts

Posted - 11/06/2012 :  02:41:59  Show Profile  Reply with Quote
DECLARE @A INT
SET @A=1

SELECT MEIT.client, MEIT.tree_type, 0 AS tree_level, MEIT.menu_id, MEIT.parent_menu_id, MEIT.menu_type,
MEIT.licence_ref, MEIT.icon_type, MEIT.title_nl, MEIT.title_en, MEIT.title_fr, MEIT.title_de,
CONVERT(NVARCHAR, RIGHT(N'0000' +
CONVERT(NVARCHAR, ROW_NUMBER() OVER (PARTITION BY MEIT.client, MEIT.tree_type
ORDER BY CASE WHEN MEIT.menu_type = 5 THEN 5 WHEN MEIT.menu_type = 6 THEN 6 ELSE 1 END,
MEIT.sequence_no, MEIT.title_no)),4)) AS tree_sequence
FROM uvimenuitem MEIT
WHERE MEIT.parent_menu_id = N'!'
UNION ALL
WHILE @A<=10
BEGIN

SELECT MEIT.client, MEIT.tree_type, TREES.tree_level+@A AS tree_level, MEIT.menu_id, MEIT.parent_menu_id, MEIT.menu_type,
MEIT.licence_ref, MEIT.icon_type, MEIT.title_nl, MEIT.title_en, MEIT.title_fr, MEIT.title_de,
CONVERT(NVARCHAR, TREES.tree_sequence +
RIGHT(N'0000' +
CONVERT(NVARCHAR, ROW_NUMBER() OVER (PARTITION BY MEIT.client, MEIT.tree_type
ORDER BY CASE WHEN MEIT.menu_type = 5 THEN 5 WHEN MEIT.menu_type = 6 THEN 6 ELSE 1 END,
MEIT.sequence_no, MEIT.title_no)),4)) AS tree_sequence
FROM uvimenuitem MEIT
INNER JOIN tree_structure TREES
ON TREES.client = MEIT.client
AND TREES.tree_type = MEIT.tree_type
AND TREES.menu_id = MEIT.parent_menu_id
SET @A=@A+1
END
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/11/2012 :  18:30:00  Show Profile  Reply with Quote
quote:
Originally posted by vpawa

Hi,

Im trying to loop my query but dont know how to do. can somebody help me to improve the query?

I have created the following query.

SELECT MEIT.client, MEIT.tree_type, 0 AS tree_level, MEIT.menu_id, MEIT.parent_menu_id, MEIT.menu_type,
MEIT.licence_ref, MEIT.icon_type, MEIT.title_nl, MEIT.title_en, MEIT.title_fr, MEIT.title_de,
CONVERT(NVARCHAR, RIGHT(N'0000' +
CONVERT(NVARCHAR, ROW_NUMBER() OVER (PARTITION BY MEIT.client, MEIT.tree_type
ORDER BY CASE WHEN MEIT.menu_type = 5 THEN 5 WHEN MEIT.menu_type = 6 THEN 6 ELSE 1 END,
MEIT.sequence_no, MEIT.title_no)),4)) AS tree_sequence
FROM uvimenuitem MEIT
WHERE MEIT.parent_menu_id = N'!'
UNION ALL
SELECT MEIT.client, MEIT.tree_type, TREES.tree_level+1 AS tree_level, MEIT.menu_id, MEIT.parent_menu_id, MEIT.menu_type,
MEIT.licence_ref, MEIT.icon_type, MEIT.title_nl, MEIT.title_en, MEIT.title_fr, MEIT.title_de,
CONVERT(NVARCHAR, TREES.tree_sequence +
RIGHT(N'0000' +
CONVERT(NVARCHAR, ROW_NUMBER() OVER (PARTITION BY MEIT.client, MEIT.tree_type
ORDER BY CASE WHEN MEIT.menu_type = 5 THEN 5 WHEN MEIT.menu_type = 6 THEN 6 ELSE 1 END,
MEIT.sequence_no, MEIT.title_no)),4)) AS tree_sequence
FROM uvimenuitem MEIT
INNER JOIN tree_structure TREES
ON TREES.client = MEIT.client
AND TREES.tree_type = MEIT.tree_type
AND TREES.menu_id = MEIT.parent_menu_id

now im trying to loop TREES.tree_level+1 10 times. does somebody knows how to get that withoud creating 10 view tables?

Thanks
Vishal



You probably need to use an rCTE (Recursive CTE) for this. Books Online explains how to use one. The following URL explains how to traverse a simple tree in an ordered fashion.
http://www.sqlservercentral.com/articles/T-SQL/72503/


--Jeff Moden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000