Link to home
Create AccountLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

I need a super simple CTE expression

I need a super simple CTE expression


I have to test a SQL environment we use to be sure it supports CTE's, which I assume it must. Anyway, I don't know what configuration was already done...


Please make me up the simplest CTE, and I can change the table name, column name, etc. Then I can see if there is some barrier.


Thanks

SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of curiouswebster

ASKER

Yes, it is a different thinking.

We use this tool:
https://www.synergex.com/docs/index.htm#search-%22common%20table%22

I only started using it a day ago, and have no experience with it.

From what I understand, it supports many SQL Server commands.
Thanks for the post
I have a different query which executes, but when I wrap it inside this:

WITH cte AS
(
      // working SQL query
)
SELECT * FROM cte;

This uses ODBC, by the way...

here is the error.

Are CTE's too new for ODBC?

4:12:47 PM<br />System.Data.Odbc.OdbcException (0x80131937): ERROR [HY000] [TOD][ODBC][GENESIS](pos: 1 '^WITH cte AS ...') - syntax error
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Try putting a semicolon in front of the cte.

Eg

;with cte as

Open in new window


Also what odbc driver are you using.


Lastly you can create a view or stored proc if the odbc driver doesn't support it and do a select on the view or exec stored proc as a work around.
You may use TRY - CATCH block to check the CTE availability (BTW, it is available since SQL Server 2005):
BEGIN TRY 
DECLARE @sql NVARCHAR(200) = 
N'DECLARE @xx INT; with test_cte( val ) as (
	select 1 as val	
)
select @xx = val 
from test_cte'

EXEC sp_executeSQL @sql
SELECT 'CTE available'
END TRY
BEGIN CATCH
  SELECT 'No CTE available'
END CATCH

Open in new window

It also works via ODBC independently on the driver used.
ODBC call from Visual FoxPro
lnH = sqlconnect()

TEXT TO lcSql 
BEGIN TRY 
 DECLARE @sql NVARCHAR(200) = 
 N'DECLARE @xx INT;
    with test_cte( val ) as (
   select 1 as val ) 
  select @xx = val from test_cte'
 
 EXEC sp_executeSQL @sql 
 SELECT 'CTE available' 
END TRY 
BEGIN CATCH 
 SELECT 'No CTE available' 
END CATCH
ENDTEXT

? SQLEXEC(lnH, lcSql)
= SQLDISCONNECT(lnH)

BROWSE LAST

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account