I'm running into a problem when executing a subquery in DB Connect.
When the query is executing through SQL Developer it executes fine but when executed in SQL Explorer in DB Connect I get an error message "java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis".
I've narrowed it down to essentially the subquery portion of the query. Is this a known issue and are there any workarounds?
SELECT * FROM (
SELECT c."Name" AS ItemName, LTRIM(p."Name",'_') AS ItemProperty, TO_CHAR(p."Value") AS Value,
CASE c."Type"
WHEN 2 THEN 'View'
WHEN 8 THEN 'Canvas'
END AS Type
FROM "CATALOGITEM" c, "PROPERTY" p
WHERE
p."ItemID" = c."ID"
AND LENGTH(p."Value") > 0 --check for blank CLOB
AND c."Name" = 'AL'
AND c."Type" = 2
OR c."Type" = 8
AND p."Name" = 'DateModified'
OR p."Name" = 'DateAccessed'
OR p."Name" = 'UserAccessed'
OR p."Name" = 'AccessCount'
OR p."Name" = 'Database'
) Tmp
Just a follow up in case someone else runs into this.. removing the comment "--check for blank CLOB" fixed the issue.
AND LENGTH(p."Value") > 0 --check for blank CLOB
Adam,