为什么我不能在select语句中使用CTE呢?

I have a Mule application running a DB query which runs perfectly well (albeit a little bit slow). It's details are:

我有一个Mule应用程序运行一个DB查询，它运行得非常好(尽管有点慢)。细节是:

Mule 3.7.2 EE
Mule 3.7.2章EE
Anypoint Studio 5.4.1
Anypoint工作室5.4.1之前
jdk1.7.0_51
jdk1.7.0_51
Using oracle ojdbc6 driver
使用oracle ojdbc6司机

Now when I create a database connection using the ojdbc6 driver, without the use of a Common Table Expression (CTE) the query runs fine (at 2.5 secs).

现在，当我使用ojdbc6驱动程序创建数据库连接时，不使用公共表表达式(CTE)，查询运行良好(在2.5秒)。

When I utilise my CTE, the query fails with the error:

当我使用CTE时，查询失败，错误如下:

org.mule.api.MessagingException: Query type must be one of '[SELECT, STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.IllegalArgumentException). Message payload is of type: NullPayload

org.mule.api。MessagingException:查询类型必须是“[SELECT, store_procedure dure_call]”之一，但是是“DDL”(java.lang.IllegalArgumentException)。消息有效负载类型为:null有效负载

Clearly at some point the Mule EE server thinks that I'm trying to execute DDL when I'm not.

显然，在某些情况下，Mule EE服务器认为我正在尝试执行DDL，而实际上我没有。

The new query, using the CTE, works perfectly fine (at .250 sec) in SQL Developer, so I know that it's not my query.

使用CTE的新查询在SQL Developer中运行良好(在.250秒)，因此我知道它不是我的查询。

Why can't I use the CTE (WITH) statement in Mule?

为什么我不能在Mule中使用CTE (WITH)语句?

Is this a known issue? Am I doing something wrong?

这是一个已知的问题吗?我做错什么了吗?

Query (simplified) before CTE:

SELECT '1' AS COLUMN_ONE, 'X' AS COLUMN_X
FROM DUAL

Query (simplified) after CTE:

CTE后查询(简体):

WITH TBL AS
(
    SELECT '1' AS COLUMN_ONE, 'X' AS COLUMN_X
    FROM DUAL
)
SELECT * FROM TBL

2 个解决方案

#1

As of 2016-04-27 the CTE statement is not supported by Mule.

截至2016-04-27年，Mule不支持CTE声明。

I had to dig deep for this one.

我不得不深挖这个。

I tweeted this question and tagged @MuleSoft and @MuleDev
- To which I got the reply:
  
  if you haven't already we'd also suggest posting this in the forums - http://forums.mulesoft.com
  
  如果你还没有，我们也建议你在论坛http://forums.mulesoft.com上发布
I then posted the issue on the Mulesoft Forums
- To no avail
- 都无济于事
然后我在Mulesoft的论坛上发布了这个问题，但毫无效果
I then logged a support ticket with Mulesoft (case number 00107313)
- To which I got the reply:
  
  WITH is not currently supported by Mule ESB. Enhancement request(SE-987) has been raised but not implemented yet.
  Engineering team work on JIRAs based on the priorities. If this JIRA is urgent to you, please provide the following information then I will escalate the JIRA for you
  
  Mule ESB目前不支持WITH。增强请求(SE-987)已被提出，但尚未实现。工程团队根据优先级在JIRAs上工作。如果这个JIRA对你是紧急的，请提供以下信息，然后我将升级JIRA给你

#2

For now you can resolve this writing an alternative select statement as in

现在，您可以解决这个问题，编写另一个select语句

Sql - alternative to WITH ... AS

Sql -替代…作为

It does not make sense and is very unlucky for Mule not to support CTE

这是没有意义的，是非常不幸的骡子不支持CTE

#1