0

On HP Quality Center 11.0:

I need to make a recursive read of the labels in the "Subject" branch for a set of defects. I can tell that the server is app. not an Oracle one as the sys_connect_by_path "cheat" does not work. Instead I'm stuck with recursive SQL which I, for lack of brain power, cannot complete.

The principle is: From the table bg_bug, get an item and list recursively the set of entries in all_lists.al_description until NULL.

Sort of:

retrieve all items in the BUG table get its parent and print the linked contents of all_lists.al_description check if the parent has a parent and if it does, get that parent and print the linked contents of all_lists.al_description, concatenating it with the string retrieved for its child repeat until orphan.

I found this in a different thread:

with t1 (parent, child) as (select * from all_lists t where t.al_father_id = '2') select * from t1

which cannot be executed as it contains "invalid statements". I gather from that thread that the "with" statement is disallowed when using SQL in Quality Center.

Can anyone help, please?

kelstrup
  • 1
  • 1
  • 3

1 Answers1

2

You need to wrap the statement with a select statement so

select * from 
(
with t1 (parent, child) as (select * from all_lists t where t.al_father_id = '2') select * from t1
)
nmc
  • 8,724
  • 5
  • 36
  • 68
best_guess
  • 21
  • 2