Hi Luís!

I guess you meant T1.CPN1 and not T1.CPN in the first line of your second SELECT statement?

I'm still on Firebird 1.5 myself, but that doesn't prevent me from being curious about the recursive statements of Firebird 2.1, so I'll guess on a potential solution. It cannot be done with one single recursive statement, since you then would end up in an endless loop when e.g. CPN A found CPN1 C and CPN1 C then found CPN A and so on. However, I would expect two separate recursive statements that are then UNIONed to do the trick:

with recursive tmp1(PartNumber, Company)
as
(select t1.cpn1. t1.cname1
from tbcreferenceunlinked t1
where t1.cname1 = 'C1'
union
select t2.cpn, t2.cname
from tbcreferenceunlinked t2
where t2.cpn1 = tmp1.PartNumber
and t2.cname1 = tmp1.Company),

with recursive tmp2(PartNumber, Company)
as
(select t1.cpn, t1.cname
from tbcreferenceunlinked t1
where t1.cname = 'C1'
union
select t2.cpn1, t2.cname1
from tbcreferenceunlinked t2
where t2.cpn = tmp2.PartNumber
and t2.cname = tmp2.Company)

select * from tmp1
union
select * from tmp2

Note that I do not use Fb 2.1 myself (and only rarely UNION) and have never tried even to prepare such a statement! Hence, don't be surprised if you get an error at prepare time.

As others have told you, this is a new feature of Firebird 2.1, for older versions you have to write a stored procedure.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Luis Carlos Junges
Sent: 26. juni 2008 21:12
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Recursive SQL statement Help

Hey,
I have one doubt about how to build a recursive SQL statement


I have a table like this:

CREATE
TABLE TBCREFERENCEUNLINKED(CPN VARCHAR(30), CNAME VARCHAR(30),
SALESCOMMENTS BLOB SUB_TYPE TEXT, CPN1 VARCHAR(30), CNAME1 VARCHAR(30),
SALESCOMMENTS1 BLOB SUB_TYPE TEXT,PRIMARY KEY(CPN,CNAME,CPN1,CNAME1) );

cpn = first company part
cname = first company name
cpn1 = second company part
cname1= second company name

I need to group all entries according to Company Name (cname ou cname1) given by user

Example:
For a table with the following data:

cpn | cname | cpn1 | cname1
A | C1 | C | C2
D | C4 | A | C1
F | C6 | E | C5
A | C1 | F | C6
C | C3 | B | C2

The user chooseCOMPANY NAME like 'C1'
(I select all PART NUMBERS which COMPANY NAME is 'C1')

SELECT
DISTINCT T1.CPN AS PartNumber, T1.CNAME FROM TBCREFERENCEUNLINKED T1
WHERE T1.CNAME='C1' UNION SELECT DISTINCT T2.CPN1, T2.CNAME1 FROM
TBCREFERENCEUNLINKED T2 WHERE CNAME1='C1';

Answer:
A | C1
K | C1

Now i need see all others refcerences for each part number retrieved above(A e K):
Only for A here:
SELECT
DISTINCT T1.CPN AS PartNumber, T1.CNAME1 AS Company FROM
TBCREFERENCEUNLINKED T1 WHERE T1.CNAME='C1' AND T1.CPN='A' UNION SELECT
DISTINCT T2.CPN,T2.CNAME FROM TBCREFERENCEUNLINKED T2 WHERE
T2.CNAME1='C1' AND T2.CPN1='A'

Answer:
C | C2
D | C4
F | C6

Well, now i need to know how to retrieve others number which are recursive.The final answer for COMPANY
NAME='C1' and PART NUMBER='A' should be:

Answer:
C | C3
D | C4
F | C6
B | C2
E | C5

Any help of how to it with SQL statement?

Best Regards,

---
Luís Carlos Dill Junges ©