I have CTE that is been creating from DSL.values:
JavaScript
x
final RowN[] rowNS = tasksId
.stream()
.map(x -> DSL.row(x.toString()))
.toArray(RowN[]::new);
CommonTableExpression<Record1<String>> cte = DSL
.name("cte")
.fields("id")
.as(DSL
.select(
DSL.field("id", String.class)
)
.from(
DSL.values(rowNS).as("temp", "id")
)
);
Then i am trying use it to get needed values from DB
JavaScript
dslContext
.with(
cte
)
.select(
DSL.field(TaskStateTable.taskId.getColumnName(), String.class),
DSL.field(TaskStateTable.state.getColumnName(), String.class)
)
.from(
DSL.table(tableNameProperties.getFullNameTableTaskState())
)
.join(
cte
)
.on(
DSL.field(tableNameProperties.getFullNameTableTaskState() + "." + TaskStateTable.taskId.getColumnName())
.eq(cte.field("id"))
)
.fetchMap(
DSL.field(TaskStateTable.taskId.getColumnName(), String.class),
DSL.field(TaskStateTable.state.getColumnName(), String.class)
)
But i have only the next error
JavaScript
jOOQ; uncategorized SQLException for SQL [with cte(id) as (select id from ((select null id where 1 = 0) union all (select * from (values(cast(? as varchar))) temp)) temp) select task_id, state from rtm.task_state join cte on rtm.task_state.task_id = cte.id]
I suppose it happens because of (select * from (values(cast(? as varchar))
How to fix it? Other methods without cte work fine.
Advertisement
Answer
The problem was there:
JavaScript
DSL.field("id", String.class)
“Id” is needed to be wraped by DSL.name()
JavaScript
DSL.field(DSL.name("id"), String.class)