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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
