All Apps and Add-ons

Why am I encountering a problem executing ORACLE subquery in DB Connect?

Adam_Marx
Explorer

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
0 Karma

Adam_Marx
Explorer

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,

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.