I have CTE that is been creating from DSL.values:
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
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
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:
DSL.field("id", String.class)
“Id” is needed to be wraped by DSL.name()
DSL.field(DSL.name("id"), String.class)