H2DB WITH子句 [英] H2DB WITH clause

问题描述

我正在使用以下sql编写方法的单元测试

I'm writing a unit test for a method with the following sql

WITH    temptab(
i__id ,  i__name, i__effective, i__expires, i__lefttag, i__righttag,
hier_id, hier_dim_id, parent_item_id, parent_hier_id, parent_dim_id, 
ancestor, h__id, h__name, h__level, h__effective, h__expires, rec_lvl)  
AS ( 
  SELECT 
  item.id          as i__id,        
  item.name        as i__name,        
  item.effectivets   as i__effective,        
  item.expirests     as i__expires,       
  item.lefttag     as i__lefttag,     
  item.righttag    as i__righttag,       
  hier_id,        hier_dim_id,        
  parent_item_id,      
  parent_hier_id,     
  parent_dim_id,        1          as ancestor,    
  hier.id    as h__id,        hier.name  as h__name,   
  hier.level as h__level,        hier.effectivets as h__effective,    
  hier.expirests as h__expires,        1 as rec_lvl FROM   metro.item item,      
   metro.hierarchy hier  WHERE  item.id    = 'DI'     AND hier_id    = '69'     AND hier_dim_id= '36'  AND hier.id    =item.hier_id
 )
SELECT  
  i__id,    i__name,    i__effective,    i__expires,    i__lefttag,  
  i__righttag,    hier_id,    hier_dim_id,    parent_item_id,  
  parent_hier_id,    parent_dim_id,    ancestor,   
  h__id,    h__name,    h__level,    h__effective,    h__expires 
FROM temptab 

此查询返回空数据集，但我希望有1行.

This query returns empty dataset, but I expect 1 row.

数据是正确的，因为类似的简单查询不带with子句也可以正常工作.

The data are correct, as similar simple query without with clause works fine.

我调查了问题，发现了
在H2DB中使用WITH-CLAUSE进行子查询但是该解决方案没有帮助.

I investigated the problem and I've found the
Sub Query with WITH-CLAUSE in H2DB but that solution did not help.

那么，有谁知道H2如何支持with子句?

So, does anyone know how H2 supports with clause?

提前感谢您的时间.

推荐答案

现在受支持 http://www.h2database.com/html/grammar.html 也用于非递归查询.

Its supported now http://www.h2database.com/html/grammar.html For non-recursive queries also.

这篇关于H2DB WITH子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！