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.
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?SELECTRQ_REQ_ID,SYS_CONNECT_BY_PATH(RQ_REQ_NAME, '\') AS "Path"FROM REQSTART WITH RQ_FATHER_ID = 3086CONNECT 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 |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
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? |
|
|
|
|
|
|
|