.....
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 
....
    )

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 
....
    )

---

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?

---

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

---