我正在使用以下 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 行。
数据是正确的，因为类似的不带 with 子句的简单查询工作正常。
我调查了这个问题，发现了
H2DB 中带有WITH-CLAUSE 的子查询 https://stackoverflow.com/questions/14391406/sub-query-with-with-clause-in-h2db但该解决方案没有帮助。
那么，有人知道H2如何支持with子句吗？
在此先感谢您的时间。
