Search code examples
sqlreportora-00933hp-quality-center

Is it possible to make a recursive SQL query - with HP Quality Center?

This question has already been asked in stackoverflow and elsewhere:

  1. Is it possible to make a recursive SQL query ?
  2. Requêtes récursives avec les CTE - Exemples avec SQLServer 2003

But I would like to make this work for a report query in HP Quality-Center 10.

Anybody with insights into such technicalities?

A simple check:

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

QC reports "Quality Center cannot run the query because it contains invalid statements".

Whereas with the following intentional typo

select * from all_lists t wher t.al_father_id = '2'

QC reports "The SQL query ... has failed to run, with the following error: Failed SQL ... [Mercury][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended."

So I guess it answers two questions:

  1. the server is Oracle (though it does not give the version)
  2. with is not allowed through the QC interface
Solution

  • Ok, got it:

    select      SYS_CONNECT_BY_PATH (al_description,' :: ')
from        all_lists
start with  al_father_id = '2'
connect by  prior al_item_id = al_father_id

    This is the solution to my query. Thanks to a_horse_with_a_name for comments that motivated me to search deeper.

    Fab.

    I'm still open to comments for improvement or further trips and tricks of the trade - such as: where can I find a reference for the CONNECT BY related functions like SYS_CONNECT_BY_PATH. I'm sure it has a few friends I'd be keen to learn about.