--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> new_tbl2 is simply a second CTE table. There's nothing wrong in having several CTE tables after each other, you can even use WITH RECURSIVE and have some non-recursive CTEs amongst them.
>
> Why do you use gen_id(gen_new,0) and not gen_id(gen_new,1)?

yes, right on the spot I wasn't even aware that it was incremental values that produces my previous results all the prop_type is filled with increasing number. (1,2,3,....,17).
Really don't understand why generator doesn't stop in which block it's been called/executed in newtbl2 but continues to counting for other records as well.
Is that the intended functionallity of generator or some kind of malfunctioning or even bug?

> EXECUTE BLOCK
> as
> declare i int = 0;
> declare prof int;
> declare mat int;
> begin
> FOR SELECT DISTINCT elm_prof, elm_mat
> FROM ELEMENTS into :prof, :mat do
> begin
> i=i+1;
> UPDATE ELEMENTS
> SET PROP_TYPE = :i
> WHERE ELM_PROF = :prof
> AND ELM_MAT = :mat;
> end
> end

This seems most elegant and clean solution to me with variables,but SQL editor in FlameRobin throws error on (declare i int - 0); where it shouldn't
Can that be a FlameRobin editor SQL parsing mechanism fault?

I'll try with another editor IBEExpert

Thank you all in advance,

Regards,