WHILE Loop?

[tqeonline]

Hello all this is Part 2 to my other post of how to work with Variables.

I have two queries

first one:

select rel_name, RF_PATH
FROM RELEASES, RELEASE_FOLDERS
where REL_ID = rf_id
and (RF_PATH LIKE 'AAAAAUAABA%' OR RF_PATH LIKE 'AAAAAUAADA%')

then for every RF_PATH I need to join

declare @REL_NAME nvarchar(500)
declare @RF_PATH nvarchar(500)

set @RF_PATH = --The RF_PATH from the previous query - ex.'AAAAAUAAAAAIAAA'

set @REL_NAME = (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,6)) + '>'
set @REL_NAME = @REL_NAME + (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,9)) + '>'
set @REL_NAME = @REL_NAME + (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,12)) + '>'
set @REL_NAME = @REL_NAME + (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,15))

Select @REL_NAME

so I need to loop through the first results adding the bottom column to the top.

Result being REL_NAME, RF_PATH, NEW_Column

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

I need some sample data. Your query with the fixed positioning makes it appear as though you are expecting a very rigid data structure.

a basic while loop is:

while(true)
begin
--like do stuff
end



A lack of experience doesn't prevent you from doing a good job.

 

[tqeonline]

i'm getting some where

declare @rel_id int
declare @rf_path nvarchar(50)
declare @rel_path nvarchar(100)

select @rel_id = min(rel_id) FROM RELEASES, RELEASE_FOLDERS
where REL_ID = rf_id
and (RF_PATH LIKE 'AAAAAUAABA%' OR RF_PATH LIKE 'AAAAAUAADA%') 

while @rel_id is not null
begin
	select @RF_PATH = rf_path from RELEASE_FOLDERS 
		where RF_ID = @rel_id
		
	set @rel_path = (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,6)) + '>'
	set @rel_path = @rel_path + (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,9)) + '>'
	set @rel_path = @rel_path + (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,12))
	
    select rel_name, rf_path, @rel_path  FROM RELEASES, RELEASE_FOLDERS
		where REL_ID = rf_id 
		and rel_id = @rel_id
		
    select @rel_id = min(rel_id) FROM RELEASES, RELEASE_FOLDERS
		where REL_ID = rf_id
		and (RF_PATH LIKE 'AAAAAUAABA%' OR RF_PATH LIKE 'AAAAAUAADA%')  
		and rel_id > @rel_id
end

now i'm to the point where i need the

select rel_name, rf_path, @rel_path  FROM RELEASES, RELEASE_FOLDERS
		where REL_ID = rf_id 
		and rel_id = @rel_id

so insert into a TEMP table then after it's done select that TEMP table

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

is there any form of ordering column on the table that has the rel_id and rf_path? Or is it really

1[tab]aaa
1[tab]aaaBBB
1[tab]aaaBBBccc

and you have to infer that because the length of the second one is longer, it is further in the tree?

Lod

A lack of experience doesn't prevent you from doing a good job.

 

[tqeonline]

the RF_PATH shows the directory structure.

so 'AAAAAUAAAAAIAAA' would be 5 layers deep
1. AAA
2. AAAAAU
3. AAAAAUAAA
4. AAAAAUAAAAAI
5. AAAAAUAAAAAIAAA

and what this does is goes and says what is 2, 3, 4 folder names (all I care about)

1. Releases
2. SQA
3. 2011
4. 06 JUL
5. Release Name

so what I'm doing is building a column that is "SQA>2011>06 JUL" so we can easily see where the path is.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

got it! - is there anyway to improve this or is there anything i'm doing wrong?

declare @rel_id int
declare @rf_path nvarchar(50)
declare @rel_path nvarchar(100)

create table #temp (
	RowID int Identity(1,1),
	Rel_ID int,
	Rel_Name nvarchar(100),
	Rel_Path nvarchar(100)
)

select @rel_id = min(rel_id) FROM RELEASES, RELEASE_FOLDERS
where REL_ID = rf_id
and (RF_PATH LIKE 'AAAAAUAABA%' OR RF_PATH LIKE 'AAAAAUAADA%') 

while @rel_id is not null
begin
	select @RF_PATH = rf_path from RELEASE_FOLDERS 
		where RF_ID = @rel_id
		
	set @rel_path = (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,6)) + '>'
	set @rel_path = @rel_path + (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,9)) + '>'
	set @rel_path = @rel_path + (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,12))
	
	insert into #temp (Rel_ID,Rel_Name,Rel_Path)
    select rel_id,rel_name, @rel_path  FROM RELEASES, RELEASE_FOLDERS
		where REL_ID = rf_id 
		and rel_id = @rel_id
		
    select @rel_id = min(rel_id) FROM RELEASES, RELEASE_FOLDERS
		where REL_ID = rf_id
		and (RF_PATH LIKE 'AAAAAUAABA%' OR RF_PATH LIKE 'AAAAAUAADA%')  
		and rel_id > @rel_id
end

select * from #temp 
drop table #temp

note: this does work and gives me exactly what I need, just wanted to know if i could make it better.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

Do you have a numbers table in your database?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

not that i am aware of.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

Can you add one? If you had a numbers table, then the query would be simpler.

Declare @Path VarChar(20)
Declare @Output VarChar(100)

Set @Path = 'AAAAAUAAAAAIAAA'
Set @Output = ''

Select  @Output = @Output + RF_PATH + '>'
From    Numbers	
        Inner Join RELEASE_FOLDERS
          On RELEASE_FOLDERS.RF_NAME = Left(@Path, Num * 3)
Where   Num <= Len(@Path)/3

Select @Output

This assumes a numbers table with a num column, like this:

Create Table Numbers(Num Int Identity(1,1) Primary Key)

Insert Into Numbers Default Values
Go 10000

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

Fix any syntax errors this may have and try it.
I didn't have source tables to play with.
I didn't address the path issue, but you would join on another derived table that did a row_number over the releases and order by the length of the rel path desc and just take the longest path.

Select b.rel_id, b.a + '>', b.b + '>', b.c + '>', b.d + '>', b.e
FROM(
	SELECT a.rel_id, 
	Case when RowNum = 1 THEN rel_name else '' end as A,
	Case when RowNum = 2 THEN rel_name else '' end as B,
	Case when RowNum = 3 THEN rel_name else '' end as C,
	Case when RowNum = 4 THEN rel_name else '' end as D,
	Case when RowNum = 5 THEN rel_name else '' end as E
	FROM
	(select ROW_NUMBER() OVER(Partition by rel_id ORDER BY len(rf_path)) as RowNum,
		rel_id, rel_name, rf_path
	FROM RELEASES r
	JOIN (
		SELECT DISTINCT rf_i.rf_id 
		FROM RELEASE_FOLDERS rf_i
		WHERE (rf.RF_PATH LIKE 'AAAAAUAABA%' OR rf.RF_PATH LIKE 'AAAAAUAADA%')) as rf_a ON
		r.rel_id = rf_a.id	
	JOIN RELEASE_FOLDERS rf ON
		r.REL_ID = rf.rf_id) as a ) as b

HTH,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.