SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Connect by Path?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 10/31/2012 :  12:35:35  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 10/31/2012 :  13:18:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Yak Posting Veteran

97 Posts

Posted - 10/31/2012 :  14:21:51  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 10/31/2012 :  14:27:46  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Yak Posting Veteran

97 Posts

Posted - 10/31/2012 :  15:06:24  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 10/31/2012 :  15:37:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Austria
2 Posts

Posted - 04/05/2013 :  08:35:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000