Sub lookup based off of Full folder path? Recursion? 1

[tqeonline]

Hello all - I need help (obviously since i'm posting).

I need to be able to show a db folder path given a "release". Any given release will have 5 or 6 layers above it.

example: Release > Department > Year > Month > Release and the way that the database shows it is via a "RF_PATH"

ex: AAAAAUAAAAAOAAAAAA every layer is 3 letters so
1. AAA
2. AAU
3. AAA
4. AAO
5. AAA
6. AAA

So to get the first layer I would do a query:

select RF_NAME where RF_PATH = 'AAA'

to get the next layer i would do

select RF_NAME where RF_PATH = 'AAAAAU'

So this is what I have:

SELECT REL_NAME, RF_PATH
FROM RELEASES, RELEASE_FOLDERS
WHERE REL_ID = RF_ID

where RF_PATH is the release path. What I need to do is then take that RF_PATH and break it down and return the entire folder path. The issue is that the length varies (could be 3 folders upto 6 folders)

Also I'd like the result to be in one column and to look like:

"Release > Department > Year > Month"

Any ideas?

- 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

 

[simian336]

Still confused, could you give us some data examples and expected results after the query?

Thanks

Simi

 

[tqeonline]

of course!

Here are the queries and their results:

SELECT REL_NAME, RF_PATH
FROM RELEASES, RELEASE_FOLDERS
WHERE REL_ID = RF_ID
AND REL_ID = 177
--Returns Rel_Name = "BB - CI Sprint 24"
--Returns RF_PATH AAAAAUAAAAAIAAA

SELECT RF_NAME, RF_PATH
FROM RELEASE_FOLDERS
WHERE RF_PATH = 'AAA'
--Returns RF_Name = "Releases"

SELECT RF_NAME, RF_PATH
FROM RELEASE_FOLDERS
WHERE RF_PATH = 'AAAAAU'
--Returns RF_Name = "SQA"

SELECT RF_NAME, RF_PATH
FROM RELEASE_FOLDERS
WHERE RF_PATH = 'AAAAAUAAA'
--Returns RF_Name = "Archived"

SELECT RF_NAME, RF_PATH
FROM RELEASE_FOLDERS
WHERE RF_PATH = 'AAAAAUAAAAAI'
--Returns RF_Name = "KTBR 2010"

SELECT RF_NAME, RF_PATH
FROM RELEASE_FOLDERS
WHERE RF_PATH = 'AAAAAUAAAAAIAAA'
--Returns RF_Name = "KTBR 2010 Q4"

so what I would want the single column to return is:

Releases>SQA>Archived>KTBR 2010>KTBR 2010 Q4"

As you can tell it takes the original RF_PATH and starts with the first 3 characters and does a query, then 6, 9, 12, 15...

- 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

 

[simian336]

so if I do...

select RF_NAME, RF_PATH
from RELEASE_FOLDERS

Then I will get?

RF_NAME, RF_PATH
'AAA', "Releases"
'AAAAAU', "SQA"
'AAAAAUAAA', "Archived"
'AAAAAUAAAAAI', "KTBR 2010"
'AAAAAUAAAAAIAAA', "KTBR 2010 Q4"


Simim

 

[tqeonline]

If i do this:

declare @REL_NAME nvarchar(50), @RF_PATH nvarchar(50)
SELECT @REL_NAME = REL_NAME, @RF_PATH = RF_PATH
FROM RELEASES, RELEASE_FOLDERS
WHERE REL_ID = RF_ID
AND REL_ID = 177

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

the result is:

col1 = 'BB - CI Sprint 24'
col2 = 'AAAAAUAAAAAIAAA'
col3 = 'Releases>SQA>Archived>KTBR 2010>KTBR 2010 QA'

But how would I make that one query and make it able to return one row?

- 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]

yes!

- 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]

also keep in mind that there might not always be 15 letters.

- 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]

actually I just looked at the data and for most of the needs its 12 in length.

- 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

 

[simian336]

Try.. Untested...

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

set @Rel_id=177

set @REL_NAME =(SELECT REL_NAME
FROM RELEASES
Join RELEASE_FOLDERS
on REL_ID = RF_ID
where REL_ID = @Rel_id)

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

print @RF_PATH

Simi

 

[tqeonline]

declare @REL_NAME nvarchar(50), @RF_PATH nvarchar(50), @Rel_id int

select @Rel_id = 177

set @REL_NAME =(SELECT REL_NAME
FROM RELEASES
Join RELEASE_FOLDERS    
on REL_ID = RF_ID
where REL_ID = @Rel_id)

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

print @RF_PATH

doesn't print anything. Also would this work if there were 30 REL_ID's?

- 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

 

[simian336]

simplified it some... tested it this time...

Simi


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

set @RF_PATH ='AAAAAUAAAAAIAAA'

set @REL_NAME = (Select RF_NAME from RELEASE_FOLDERS WHERE RF_PATH = LEFT(@RF_PATH,3)) + '>'
set @REL_NAME = @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

 

[tqeonline]

wow that is easy. So how would I implement that into a man release situation?

Example: i query all the current releases and now there are 30 different RF_PATH's.

Take example:

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

there are 20 rows returned. Each of which have an RF_PATH, I need to loop through and add this column to each one individually.

- 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