Hi, some collected answers to the join problem
> > I now remeber why I used the (+) syntax, the FROM syntax does > not work. > I have tried to rewrite my example using the ANSI syntax but > I can't get > 7.3.00.25 > to accept more than one join at the time. > > SELECT A.o,A.a,B.b,C.c FROM A LEFT JOIN B USING (o) LEFT JOIN > C USING (o) > gives -3014 Syntax error or access violation;-3014 POS(53) > Invalid end of > SQL statement. > > Also SELECT A.o,A.a,B.b,C.c FROM A LEFT JOIN B USING (o), C > gives two rows > with all columns set. I was expecting a NULL in the second row. > We reworked the ansi join syntax. The error with more than one join within one select should be fixed. But we also droped the USING (<columnname>) syntax. So your statement should look like SELECT A.o,A.a,B.b,C.c FROM A LEFT JOIN B ON A.o = B.o LEFT JOIN C ON A.o = C.o The documentation on sapdb.org will be updated soon. > > This is a somewhat "strange" syntax. Is the (+) part of any SQL-Standard? > No, as far as I know, but it is or was the ORACLE way to write outer joins. With recent releases ORACLE supports full ansi syntax for outer joins and we are working on that topic, too. By now there still might be some bugs and for sure there are missing a lot of things. > Can someone please explain why these joins give different results. > > // First create some tables > // > CREATE TABLE A ( o FIXED(10) KEY NOT NULL,a FIXED(10) ) > // > CREATE TABLE B ( o FIXED(10) NOT NULL,b VARCHAR(32) , FOREIGN KEY (o) > REFERENCES A (o) ON DELETE CASCADE) > // > CREATE TABLE C ( o FIXED(10) NOT NULL,c VARCHAR(32) , FOREIGN KEY (o) > REFERENCES A (o) ON DELETE CASCADE) > > > // Insert some data > INSERT INTO A SET o = 1, a = 1 > // > INSERT INTO B SET o = 1, b = 'String1' > // > INSERT INTO A SET o = 2, a = 2 > // > INSERT INTO C SET o = 2, c = 'String2' > > // First select, work as expected > SELECT A.o,A.a,B.b,C.c FROM A,B,C WHERE A.o = B.o (+) AND A.o = C.o (+) > > // Second select. Gives two rows where b and c is NULL! > SELECT A.o,A.a,B.b,C.c FROM C,B,A WHERE A.o = B.o (+) AND A.o = C.o (+) > > Is this a bug or do I need to order by tables in the FROM > statement depending on how i do my join? > I'll have a closer look to your select because I agree that it looks like a bug. In general I can say that the result of an outer join with more than two tables in SAP DB depends on the order of the tables in the from clause. Because SAP DB executes the outer join like "(A left outer join B) left outer join C" or if you change the order in the from clause "(C inner join B) right outer join A". We know that this is not the best way but it's caused by our join execution engine and a redesign will last a while Kind regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
