Thread: Syntax problem for a newbie
Dear all, I have a syntax problem but I don't find the clue. Actually I adapt an mySQL query to a postgreSQL but I got a message error that I can't interpret. SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as subpath,l.id-1 as level FROM graph_path g INNER JOIN term AS t1 INNER JOIN term AS t2 ON (t2.id = g.term2_id) INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id AND g.distance+1 >= l.id) WHERE t2.name = 'blood_coagulation' and g.term1_id=1 ORDER BY g.id, subpath; ERROR: syntax error at or near "WHERE" at character 284 LINE 7: WHERE t2.name = 'blood_coagulation' and g.term1_id=1 Let me know if you need details concerning the tables but I don't think it is necesseray. Hopefully I did a newbie mistake. Thanks in advance, Fred
"Fred" <frederic.fleche@gmail.com> writes: > I have a syntax problem but I don't find the clue. > Actually I adapt an mySQL query to a postgreSQL but I got a message > error that I can't interpret. > SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as > subpath,l.id-1 as level > FROM graph_path g > INNER JOIN term AS t1 > INNER JOIN term AS t2 ON (t2.id = g.term2_id) > INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id > AND g.distance+1 >= l.id) > WHERE t2.name = 'blood_coagulation' and g.term1_id=1 > ORDER BY g.id, subpath; You're short an ON condition: there has to be an ON for every JOIN. Or turn the first INNER JOIN into a CROSS JOIN, so it doesn't need an ON. Does MySQL really accept that as-is? (Standards compliance was never their strong point :-() regards, tom lane
Fred wrote: > Dear all, > > I have a syntax problem but I don't find the clue. > Actually I adapt an mySQL query to a postgreSQL but I got a message > error that I can't interpret. > > SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as > The thing that stands out to me is the syntax for the substring function, see: http://www.postgresql.org/docs/8.1/static/functions-string.html Hope this helps, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote: > "Fred" <frederic.fleche@gmail.com> writes: > > I have a syntax problem but I don't find the clue. > > Actually I adapt an mySQL query to a postgreSQL but I got a message > > error that I can't interpret. > > > SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as > > subpath,l.id-1 as level > > FROM graph_path g > > INNER JOIN term AS t1 > > INNER JOIN term AS t2 ON (t2.id = g.term2_id) > > INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id > > AND g.distance+1 >= l.id) > > WHERE t2.name = 'blood_coagulation' and g.term1_id=1 > > ORDER BY g.id, subpath; > > You're short an ON condition: there has to be an ON for every JOIN. > Or turn the first INNER JOIN into a CROSS JOIN, so it doesn't need an ON. > > Does MySQL really accept that as-is? (Standards compliance was never > their strong point :-() > Yes, MySQL (4.1.14) quite happily accepts that as-is... -- Russ
Russ Brown <pickscrape@gmail.com> writes: > On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote: >> "Fred" <frederic.fleche@gmail.com> writes: >>> SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as >>> subpath,l.id-1 as level >>> FROM graph_path g >>> INNER JOIN term AS t1 >>> INNER JOIN term AS t2 ON (t2.id = g.term2_id) >>> INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id >>> AND g.distance+1 >= l.id) >>> WHERE t2.name = 'blood_coagulation' and g.term1_id=1 >>> ORDER BY g.id, subpath; >> >> Does MySQL really accept that as-is? (Standards compliance was never >> their strong point :-() > Yes, MySQL (4.1.14) quite happily accepts that as-is... [ tries it... ] Hm, 5.0.x is no better. This is really bad, because it shows that they completely misimplemented the JOIN syntax. The above query is ambiguous because it's not clear which JOIN each ON is supposed to go with. Per spec, you can write something like FROM a JOIN b JOIN c ON b_c_cond ON a_bc_cond which is supposed to be parenthesized as FROM a JOIN (b JOIN c ON b_c_cond) ON a_bc_cond so that the conditions are associated with the joins I named them after. If you parse things so that ON is optional then it's completely unclear which JOINs the ONs that are there are supposed to go with. And this matters, particularly for outer joins. mysql> select * from a join b left join c on (b1=c1) on (a1=b1); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versionfor the right syntax to use near 'on (a1=b1)' at line 1 Wonderful :-( regards, tom lane