运行sql后提示缺失右括号问题

Question

select loginCount, activeCount from ( select count(1) activeCount from PW_DOC_RECORD a left join PW_EMP p on a.USER_ID = p.EMP_ID where EXISTS( select 1 from PW_DOC_RECORD b AND b.DOC_RECORD_CTIME like '%'||?||'%' AND b.DOC_RECORD_TYPE = ? and a.USER_ID = b.USER_ID ) and p.EMP_SEX = ? AND a.DOC_RECORD_CTIME like '%'||?||'%' AND a.DOC_RECORD_TYPE = ? AND a.DOC_TYPE = ? ) active, ( select count(1) loginCount from PW_DOC_RECORD c where 1=1 AND c.DOC_RECORD_CTIME like '%'||?||'%' AND c.DOC_RECORD_TYPE = ? ) login

运行上述sql提示nested exception is java.sql.SQLSyntaxErrorException: ORA-00907: 缺失右括号

Answer 1

语法错误一大堆，括号不成对，缺where子句，like后面的内容不在引号里面
不一一列举了
告诉你个简单的办法去捋清楚自己的sql：
不管写多复杂的嵌套，要嵌套之前先补个完整的（）在那放着，把外层的select写完整了，再研究内部的select
就这样一层一层的写，不确定的可以先放放，但是结构不要破坏了，回头再补
最忌讳先写半个括号，然后剩下半个括号不知道该写在那一层，那就全乱套了
引号也一样，先把正反引号写全了，然后再在引号之间写内容，不要先写一个引号

Answer 2

仔细检查一下括号的配对情况啊

Answer 3

缺不缺右括号我倒是没看出来，但是你这个like语句，好像不大对，按道理应该是'%||?||%'

Answer 4

有点复杂 我给整理一下你自己看

SELECT  loginCount, activeCount 
FROM  
( SELECT  count(1) activeCount 
    FROM  PW_DOC_RECORD a LEFT  JOIN  PW_EMP p 
        ON  a.USER_ID = p.EMP_ID WHERE  
            EXISTS( SELECT  1 FROM  PW_DOC_RECORD b 
                    AND b.DOC_RECORD_CTIME LIKE  '%'||?||'%' 
                    AND b.DOC_RECORD_TYPE = ? 
                    AND  a.USER_ID = b.USER_ID ) 
                    AND  p.EMP_SEX = ? 
                    AND a.DOC_RECORD_CTIME LIKE  '%'||?||'%' 
                    AND a.DOC_RECORD_TYPE = ? 
                    AND a.DOC_TYPE = ? ) active, 
                        ( SELECT  count(1) loginCount FROM  PW_DOC_RECORD c 
                               WHERE  1=1 
                               AND c.DOC_RECORD_CTIME LIKE  '%'||?||'%' 
                               AND c.DOC_RECORD_TYPE = ? ) 
                                   loginselect loginCount, activeCount FROM 
                                        ( SELECT  count(1) activeCount FROM  PW_DOC_RECORD a 
                                            LEFT  JOIN  PW_EMP p on a.USER_ID = p.EMP_ID 
                                                WHERE  EXISTS
                                                    ( SELECT  1 FROM  PW_DOC_RECORD b 
                                                        AND b.DOC_RECORD_CTIME like '%'||?||'%' 
                                                        AND b.DOC_RECORD_TYPE = ? and a.USER_ID = b.USER_ID ) 
                                                        AND p.EMP_SEX = ? 
                                                        AND a.DOC_RECORD_CTIME LIKE  '%'||?||'%' 
                                                        AND a.DOC_RECORD_TYPE = ? AND a.DOC_TYPE = ? ) active, 
                                                            ( SELECT  count(1) loginCount FROM  PW_DOC_RECORD c 
                                                                WHERE  1=1 
                                                                AND c.DOC_RECORD_CTIME like '%'||?||'%' 
                                                                AND c.DOC_RECORD_TYPE = ? ) login

Answer 5

SELECT
    login.loginCount,
    active.activeCount 
FROM
    (
    SELECT
        count( 1 ) activeCount 
    FROM
        PW_DOC_RECORD a
        LEFT JOIN PW_EMP p ON a.USER_ID = p.EMP_ID 
    WHERE
        EXISTS (
        SELECT
            1 
        FROM
            PW_DOC_RECORD b          
            AND b.DOC_RECORD_CTIME LIKE '%' ||?|| '%' 
            AND b.DOC_RECORD_TYPE = ? 
            AND a.USER_ID = b.USER_ID 
        ) 
        AND p.EMP_SEX = ? 
        AND a.DOC_RECORD_CTIME LIKE '%' ||?|| '%' 
        AND a.DOC_RECORD_TYPE = ? 
        AND a.DOC_TYPE = ? 
    ) active,
    (
    SELECT
        count( 1 ) loginCount 
    FROM
        PW_DOC_RECORD c 
    WHERE
        1 = 1 
        AND c.DOC_RECORD_CTIME LIKE '%' ||?|| '%' 
    AND c.DOC_RECORD_TYPE = ? 
    ) login