1

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
Community
  • 1
  • 1
asoundmove
  • 1,292
  • 3
  • 14
  • 28
  • 1
    So what is the DBMS behind QC –  Nov 29 '10 at 20:25
  • I have no idea right now. But before I access the DB directly, I'd like to use the built-in query mechanism as a lay-user (from the dashboard module provided by the QC web interface). This isolates me from the DB and also allows me to share queries with other users without any special access or priviledges. – asoundmove Nov 29 '10 at 20:37
  • 1
    I was asking because the usage of recursive CTE is not supported by all DBMS (or versions). PostgreSQL added support for that with version 8.4, SQL Server with 2005 and Oracle with 11gR2 (but you can always use CONNECT BY with Oracle) and MySQL does not support it at all. So you should show us the real error message. Then it would be clear if this is a QC problem or a database problem –  Nov 29 '10 at 22:18
  • Thanks, I updated my post with error messages. I will get the version info tomorrow. Will try CONNECT BY. – asoundmove Nov 29 '10 at 22:32
  • Wow, I like "CONNECT BY". I need more work to get the results presented the way I want them, but by and large I`m getting there. Thanks. – asoundmove Nov 29 '10 at 22:52

1 Answers1

1

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.

asoundmove
  • 1,292
  • 3
  • 14
  • 28