> 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 guesswork for anyone that
tries to help you.
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 from your query/stored procedure given the data you've
already supplied, you may supply some further textual information, but what is
most important is the actual desired result set.
HTH,
Set
Hello set,
this construct can have more than three layers, that is my problem and
therefore I would like to use a recursive cte.
I have found a solution, I have limited the first iteration with "and pos = 1".
I had also create a new field with the Id oft the previous category - in this
case the previous category id oft he first position is empty and I have take
this for a criteria.
I think, this can work for all conditions.
Thank you for your help 😊
with best regards
Olaf
