As documented, recursive table expressions treat all columns as varchar. Is
there any way to put the required casts in a subexpression, rather than
repeating them throughout the expression? Seems like it needs another table
expression, but there doesn't appear to be a way to nest them.
For example:
with recursive t(i, j, v) as (
select 1407352, 1407948, value from codenew where id = (1407948 +
1407352) / 2
union all
(select (cast(i as int) + cast(j as int)) / 2, j, value from t inner
join codenew on id = ((cast(i as int) + cast(j as int)) / 2 + cast(j as
int)) / 2 where v < 'E2-A150' and cast(i as int) < cast(j as int) union all
select i, (cast(i as int) + cast(j as int)) / 2, value from t inner
join codenew on id = (cast(i as int) + (cast(i as int) + cast(j as int)) /
2) / 2 where v > 'E2-A150' and cast(i as int) < cast(j as int))
) select * from t
This is pretty hairy even without all the cast() calls. It would be nice to
factor them out into a subexpression, but I haven't found a way to do that.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
