Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Connect by Path?

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-10-31 : 12:35:35
I've found the following query and it was specified as being an Oracle query. What would the same thing look like for SQL Server?


SELECT
RQ_REQ_ID,
SYS_CONNECT_BY_PATH(RQ_REQ_NAME, '\') AS "Path"
FROM REQ
START WITH RQ_FATHER_ID = 3086
CONNECT BY PRIOR RQ_REQ_ID = RQ_FATHER_ID

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-31 : 13:18:31
You'd use a recursive Common Table Expression, there's an example here: http://msdn.microsoft.com/en-us/library/ms186243.aspx
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-10-31 : 14:21:51
Thanks.

I've found lots of these "CTE" examples and none work. The application I'm using (HP Quality Center) to run the SQL always just says "Quality Center cannot run the query because it contains invalid statements". I'm neither a data analysis of a DBA so my SQL knowledge is limited. I still haven't any idea how to accomplish this query in SQL Server.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-31 : 14:27:46
If HPQC is using SQL Server, it's either using a pre-2005 version, its databases are set to a compatibility level of 80 or less, or it's doing its own parsing of SQL before executing it. Can you connect to the SQL Server using Management Studio? If that works, check the version and compatibility level with the following:

select SERVERPROPERTY('ProductVersion'), name, cmptlevel from sys.sysdatabases
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-10-31 : 15:06:24
What I found out is that Quality Center (specifically, the "Excel Reports" feature in Dashboard where SQL is written and query results stored in Excel does *not* support CTE. This is a Quality Center restriction (don't know why). In any case, I have an alternate path (non-SQL) using the QC API to get to what I need. Thanks.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-31 : 15:37:36
If you're comfortable creating it, and doing so doesn't violate the license for HPQC, you can create a view with the CTE definition and then SELECT from that instead for future queries.
Go to Top of Page

nostalgia
Starting Member

2 Posts

Posted - 2013-04-05 : 08:35:20
quote:
Originally posted by planetoneautomation

What I found out is that Quality Center (specifically, the "Excel Reports" feature in Dashboard where SQL is written and query results stored in Excel does *not* support CTE. This is a Quality Center restriction (don't know why). In any case, I have an alternate path (non-SQL) using the QC API to get to what I need. Thanks.



I have the same problem. Can you explain how you solve this problem with (non-SQL) using the QC API?
Go to Top of Page
   

- Advertisement -