> 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
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
