Forum OpenACS Q&A: ACSpg3.2 SQL changes: u.user_id(+) to what?

A number of sql statements are upsetting PostgreSQL 7. Many of them have a common element: (+) at the end of a field name. Here is one full statement along with the error code.

Error: Ns_PgExec: result status: 7 message: ERROR: parser: parse error at or near "(" Notice: nsd.db: sql(localhost::acspg3): select c.category_id, ch.tree_level - 1 as indent, c.category, c.category_type, link_id, gl.url, link_title, gl.creation_time, gl.last_approval_change, gl.approved_p, u.first_names, u.last_name from categories c, general_links gl, users u, (select child_category_id, rownum as tree_rownum, level as tree_level from category_hierarchy start with parent_category_id is null connect by prior child_category_id = parent_category_id) ch where c.category_id = ch.child_category_id and exists (select 1 from site_wide_category_map swm where gl.link_id = swm.on_what_id and swm.on_which_table = 'general_links' and swm.category_id = c.category_id) and u.user_id(+) = gl.approval_change_by and gl.approved_p is NULL and gl.creation_time > sysdate() - 30 order by ch.tree_rownum, link_title

Is this the problem? Any way I can help? Is there a better place for me to discuss this?

What you're seeing here is an outer join. The idea is relatively involved, so I recommend reading up on Philip's SQL guide at photo.net/sql. We're porting a bunch of these already (check out the porting guide in the manual page), however this one isn't ported because of the "connect by" which we haven't been able to solve yet... Another Oracle construct.