0

I have an SQL Server ALM project. I need a query that extracts all requirements with their full path, which can up to four levels including the requirement name.

Since I'm on SQL Server I'm unable to use the tools supplied by Oracle (which make this quite easy). The query must run from within ALM.

All I have so far is this:

SELECT distinct RQ_REQ_ID AS "Int Req ID",
REQ.RQ_REQ_NAME AS "Req ID",
REQ.RQ_REQ_REVIEWED as "Req Status",
REQ.RQ_REQ_STATUS AS "Req Coverage"
FROM REQ
WHERE RQ_TYPE_ID != 1
ORDER BY RQ_REQ_NAME

Can anyone please complete the statement so it would contain the full requirement path?

Thanks

kelstrup
  • 1
  • 1
  • 3

1 Answers1

0

Okay, I'm at work, its the holidays and I've finished up my projects, and yeah I've seen this as an oracle query and there's some simplicity but I worked in SQL for a bit. This is a total hack query and its written in oracle but the concept is the same. 

SELECT distinct RQ_REQ_ID AS "Int Req ID",
 r.RQ_REQ_NAME AS "Req ID",
 r.RQ_REQ_REVIEWED as "Req Status",
 r.RQ_REQ_STATUS AS "Req Coverage",
 r.rq_req_path,
 (select rq_req_name from req where rq_req_path = substr(r.rq_req_path, 1, 3))
 || '/' ||
 (select rq_req_name from req where rq_req_path = substr(r.rq_req_path, 1, 6))
 || '/' ||
 (select rq_req_name from req where rq_req_path = substr(r.rq_req_path, 1, 9))
FROM REQ r
WHERE RQ_TYPE_ID != 1
ORDER BY RQ_REQ_NAME

You'll want & instead of || and you may have to do some cast/convert operations on the return. The reason I thought of doing it this way is that crawling the rq_req_path and iterating down is kind of...frowned upon in SQL Server.

What I did was found the max length of RQ_REQ_PATH and just added n concatenations. Afterwards, it would be easy to strip out the extra /.

I'm totally positive there's a better, cleaner way of doing it but in case anyone else is looking at this, this should be a starting point and if its a one-off report, this works fine.

Mike Savino
  • 1
  • hey, thanks for providing the code. I can't run it, however, since ALM apparently does not recognize the substr keyword. (It is not identified by blue text as are the others). Was the query meant to run from within ALM? – kelstrup Dec 19 '14 at 12:05
  • Yeah, [try here for substring in sql server](http://msdn.microsoft.com/en-us/library/ms187748.aspx). This ran in my environment, which is oracle. Unfortunately, I don't have a SQL Server environment to test in. Concept is still the same, get the max length, know its in 3s. Again, a total hack. Could figure out a better way and don't show this to any DBA or they'll probably yell at you lol. – Mike Savino Jan 05 '15 at 15:07