I have found, that in this case the first statement before union gets in the
case of three layers the same result than the second statement after this
(check with select 1 and select 2 for the first/second statement). How can I
realize, that this recursion bring me the same records just one time?

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Gesendet: Dienstag, 2. Mai 2017 16:36
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] question cte separately

 
Hello,

at the moment, I’m in struggle with a CTE:

for with recursive ok as
(select a.pos, b.kennzeichen, b.price, b.id from t_l_prkom_grp a inner join
t_l_prkom_grp_pos b on a.id = b.id_kom_grp where a.id_kom = :id_kom
union all
select c.pos, d.kennzeichen, d.price, d.id from t_l_prkom_grp c inner join
t_l_prkom_grp_pos d on c.id = d.id_kom_grp
inner join ok on c.pos = ok.pos+1
where c.id_kom = :id_kom order by c.pos)

I would like all combinations, it works fine, but the last records are
twice.

(pos 1,2,3 – each pos has in t_l_prkon_grp 3 records, combinations like
this. (kennzeichen) 00, 01, 02 for pos 1, 10, 11, 12 for pos 2, 20, 21, 22
für pos 3 = 00-10-20, 00-10-21, 00-10-22, 00-11-20…)

As of 02-12-22 (the last combination) it begins with 02-10-20 to 02-12-22,
then twice the last 02-12-20 to 02-12-22 too.

What can be wrong?

Best regards.

Olaf