Syntax error using WITH clause CTEs in Framework Manager

[shawnmandel]

I'm trying to use a Common Table Expression WITH clause in Framework Manager (FM).  My data source query subject SQL looks like the following:

 

;WITH CTE_BRANCH as ( SELECT * FROM [gosales].[BRANCH] )

SELECT * FROM [gosales].[BRANCH]

 

At this point, I'm not even referencing the CTE in my main select.  However, I still need it to work. This works perfectly in SQL Server. However, FM generates the following error:

 

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'.
UDA-SQL-0115 Inappropriate SQL request.
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Deferred prepare could not be completed.
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Incorrect syntax near ')'. (SQLSTATE=42000, SQLERRORCODE=102)
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Incorrect syntax near ';'. (SQLSTATE=42000, SQLERRORCODE=102)

 

I've tried everything--even moving around the ";", changing to other SQL modes such as Cognos or Pass-through, but all to no avail.   

FM simply does not accept WITH clause CTEs.   I'm shocked and frustrated.

Anyone else have this issue and if so, any way to get around it?

[MFGF]

I'm trying to use a Common Table Expression WITH clause in Framework Manager (FM).  My data source query subject SQL looks like the following:

 

;WITH CTE_BRANCH as ( SELECT * FROM [gosales].[BRANCH] )

SELECT * FROM [gosales].[BRANCH]

 

At this point, I'm not even referencing the CTE in my main select.  However, I still need it to work. This works perfectly in SQL Server. However, FM generates the following error:

 

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'.
UDA-SQL-0115 Inappropriate SQL request.
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Deferred prepare could not be completed.
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Incorrect syntax near ')'. (SQLSTATE=42000, SQLERRORCODE=102)
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Incorrect syntax near ';'. (SQLSTATE=42000, SQLERRORCODE=102)

 

I've tried everything--even moving around the ";", changing to other SQL modes such as Cognos or Pass-through, but all to no avail.   

FM simply does not accept WITH clause CTEs.   I'm shocked and frustrated.

Anyone else have this issue and if so, any way to get around it?

Hi,

What is the problem you're trying to solve with this approach? Generally you'd leave each query subject as a simple "Select * from <table>", and the Cognos query engine will generate the relevant runtime query - using CTE WITH clause usually - when any item(s) from this query subject are used in the query of a report. Why do you need to have a WITH clause CTE hard-coded within a query subject? Can you explain?

MF.

[AnalyticsWithJay]

Hi,

CTEs work fine in FM. I just tested the following recursive CTE against Oracle and I had no problems:

Code Select Expand


with cte (dt) as (
  select add_months(trunc(sysdate), -1) as dt
  from   dual
  union all
  select cte.dt+1
  from   cte
  where  cte.dt+1 < sysdate
)
  select * from cte

SQL Type is set to Pass-Through.

Why do you have a semi colon before your WITH clause? Take it out completely - it's not necessary and is likely what's causing your issue.

If it's still occurring after removing the semi colon, could you please paste the Cognos SQL and Native SQL for your example above? What version of Cognos are you using?