Subject Re: [firebird-support] question cte separately setysvar 2017-05-03T19:15:16Z
> 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

Hi again, Olaf!

First iteration creates 9 rows, three for each pos.
Second iteration takes the 3 with pos1 and pos2 and joins with the three
with pos2 and pos3, giving 9 more rows with pos2 and 9 with pos 3. Third
iteration takes those 9 with pos2 and joins with the three with pos3
creating 27 more rows. Hence, the CTE generates 3+3+3+9+9+27 rows, i.e.
54 rows. These 54 rows include plenty of duplicates, e.g. you have 13
identical rows with 3-90-4-37. How to solve it? I don't know, so far
you've only described your problem in a way that leaves lots of

My guess would be that you don't want WITH RECURSIVE at all, but are
simply looking for something like

select t1.kennzeichen || coalesce('-' || t2.kennzeichen,'') ||
coalesce('-' || t3.kennzeichen,'')
from t_l_prkom_grp_pos t1
left join t_l_prkom_grp_pos t2 on t1.id_kom = t2.id_kom and t1.pos + 1 =
t2.pos
left join t_l_prkom_grp_pos t3 on t1.id_kom = t3.id_kom and t1.pos + 2 =
t3.pos
where t1.pos = 1
and t1.id_kom = 2

If you're actually looking for something else, then please describe
exactly what result you're looking for. You did show two table
definitions and insert statements to fill these - it was great that you
supplied this, although you ought to also have checked that all fields
were defined (your definition of T_L_PRKOM_GRP_POS only contained five
fields, whereas your insert statements assumed nine fields to be
present). Please also show us the correct and complete output you want