Single table - joined with "AS"

[jhoetzl]

I have the following SQL that works fine against MS-SQL Server but I can't for the life of me translate it to work on Pervasive.

SELECT
F10."id",
F10."type" As "Type",
F10."ref" As "Reference",
F12."related",
F12."status",
SWF."id" as SWFTid,
SWF."ref" as "Other Reference",
FROM
F10 INNER JOIN (F10 SWF JOIN F12 ON SWF."id" = F12."related") ON F10."id" = F12."f12id"

Scenario - table 1 (f10) with id field.
Lookup the id field in another table (F12), and come back to table 1 and get the corresponding record.

I need to make it run agains a pvsw2000i SP4 set of tables.
The ddf's are all fine.

PCC seems to choke on the inner join lookups to the same table.

Is there any way to get this working.

Thank you!

 

[mirtheil]

Well, what's the exact error?
FROM
F10 INNER JOIN (F10 SWF JOIN F12 ON SWF."id" = F12."related") ON F10."id" = F12."f12id"
The only thing that jumps at me is the part above that reads:
... JOIN (F10 SWF JOIN F12...
You've got two tables before the "JOIN". I don't know that PSQL supports that.
You might change it to
... JOIN (SWF JOIN F12) ...

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com