0

Is there a better way to this?I am Trying to extract everything under a specific folder in QC

SELECT A.AL_FATHER_ID, A.AL_ITEM_ID, A.AL_DESCRIPTION as Folder
FROM All_LISTS A
where A.AL_DESCRIPTION=@folderName@

union
SELECT B.AL_FATHER_ID, B.AL_ITEM_ID, B.AL_DESCRIPTION as Folder
FROM All_LISTS B
where B.AL_FATHER_ID = (select A.AL_ITEM_ID from ALL_LISTS A where A.AL_DESCRIPTION=@folderName@)

union
SELECT B.AL_FATHER_ID, B.AL_ITEM_ID, B.AL_DESCRIPTION as Folder
FROM All_LISTS B
where B.AL_FATHER_ID in (select C.AL_ITEM_ID from ALL_LISTS C where C.AL_FATHER_ID= (select A.AL_ITEM_ID from ALL_LISTS A where A.AL_DESCRIPTION=@folderName@))

union
SELECT B.AL_FATHER_ID, B.AL_ITEM_ID, B.AL_DESCRIPTION as Folder
FROM All_LISTS B
where B.AL_FATHER_ID in (select D.AL_ITEM_ID from ALL_LISTS D where D.AL_FATHER_ID in (select C.AL_ITEM_ID from ALL_LISTS C where C.AL_FATHER_ID= (select A.AL_ITEM_ID from ALL_LISTS A where A.AL_DESCRIPTION=@folderName@)))

union
SELECT B.AL_FATHER_ID, B.AL_ITEM_ID, B.AL_DESCRIPTION as Folder
FROM All_LISTS B
where B.AL_FATHER_ID in (select E.AL_ITEM_ID from ALL_LISTS E where E.AL_FATHER_ID in(select D.AL_ITEM_ID from ALL_LISTS D where D.AL_FATHER_ID in (select C.AL_ITEM_ID from ALL_LISTS C where C.AL_FATHER_ID= (select A.AL_ITEM_ID from ALL_LISTS A where A.AL_DESCRIPTION=@folderName@))))

union
SELECT B.AL_FATHER_ID, B.AL_ITEM_ID, B.AL_DESCRIPTION as Folder
FROM All_LISTS B
where B.AL_FATHER_ID in (select F.AL_ITEM_ID from ALL_LISTS F where F.AL_FATHER_ID in (select E.AL_ITEM_ID from ALL_LISTS E where E.AL_FATHER_ID in(select D.AL_ITEM_ID from ALL_LISTS D where D.AL_FATHER_ID in (select C.AL_ITEM_ID from ALL_LISTS C where C.AL_FATHER_ID= (select A.AL_ITEM_ID from ALL_LISTS A where A.AL_DESCRIPTION=@folderName@)))))

Thanks for your help

Eextreme
  • 1
  • 1
  • What do you mean by "better way"? What do you mean with "extract everything"? You want to enumerate folders and tests under a root folder? Does is need to be done in SQL, or can you use the QC OTA interface? That one has a NodeByPath () method, returning a node which has a FindTests method. That one returns a list of items under the node. Would be much easier. – TheBlastOne Nov 28 '13 at 08:48

1 Answers1

0

If you really are bound to SQL, you might be in good luck and can use Oracle 11gR2's recursive subquery factoring clause, see http://technology.amis.nl/2009/09/01/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it/.

Otherwise, you might want to do what you do now in an iterative way. That won´t make things simpler, but more general (and possibly more elegant) than the solution you posted, because that one is limited to a fixed number of hierarchy levels.

TheBlastOne
  • 4,291
  • 3
  • 38
  • 72