时间戳

首页 > 解决方案 > SQL 查询错误 REGEXP_INSTR 表达式中带有括号

sql - SQL 查询错误 REGEXP_INSTR 表达式中带有括号

问题描述

我的查询有问题，正则表达式中有括号。我的编辑器将表达式中的括号与表达式之外的另一个括号相关联。

我想知道这是否导致我收到 ORA-00907：缺少右括号错误，或者是否是其他原因？

WITH Stats AS
(
    SELECT 
        column1, column2
    FROM TABLE1
    UNION ALL
    SELECT
        column1, column2
    FROM TABLE2
),
Newest AS
(
    SELECT s.*
    ROW_NUMBER() OVER
    (   
        PARTITION BY
        column1, column2
        REPLACE(REPLACE('%(param)s', '5', '6'), '7', '8')
        ORDER BY colum2 DESC
    )   PRIORITY
    CASE WHEN REGEXP_INSTR('%(param)s', '/(//') 
    > 0 AND REGEXP_LIKE(column1, '%(param)s') 
    THEN 'Y' END PARAM_MATCH,
    CASE WHEN REGEXP_INSTR('%(param)s', '/(//') 
    = 0 AND column1 LIKE '%(param)s' THEN 'Y' 
    END LIKE_MATCH,
    FROM Stats s
    WHERE (REGEXP_INSTR('%(param)s', '/(//') > 0 
    AND REGEXP_LIKE(column1,'%(param)s')) OR
    (REGEXP_INSTR('%(param)s', '/(//') = 0 AND 
    REGEXP_LIKE'%(param)s')) OR
    '%(param)s' IS NULL
)

标签： sqloracleoracle-sqldeveloper

解决方案

数以万计的错误；在评论中指出：

WITH Stats AS
(
    SELECT 
        column1, column2
    FROM TABLE1
    UNION ALL
    SELECT
        column1, column2
    FROM TABLE2
),
Newest AS
(
    SELECT s.*                                      --> missing comma
    ROW_NUMBER() OVER
    (   
        PARTITION BY
        column1, column2                            --> missing comma
        REPLACE(REPLACE('%(param)s', '5', '6'), '7', '8')
        ORDER BY colum2 DESC
    )   PRIORITY                                    --> missing comma
    CASE WHEN REGEXP_INSTR('%(param)s', '/(//') 
    > 0 AND REGEXP_LIKE(column1, '%(param)s') 
    THEN 'Y' END PARAM_MATCH,
    CASE WHEN REGEXP_INSTR('%(param)s', '/(//') 
    = 0 AND column1 LIKE '%(param)s' THEN 'Y' 
    END LIKE_MATCH,                                 --> superfluous comma
    FROM Stats s
    WHERE (REGEXP_INSTR('%(param)s', '/(//') > 0 
    AND REGEXP_LIKE(column1,'%(param)s')) OR
    (REGEXP_INSTR('%(param)s', '/(//') = 0 AND 
    REGEXP_LIKE'%(param)s')) OR                     --> what is REGEXP_LIKE?
    '%(param)s' IS NULL
)
                                                    --> missing SELECT statement

就语法而言，这现在是正确的。再一次，就像您之前的问题一样：编写查询时要更加小心，因为这变得很荒谬。

WITH Stats
     AS (SELECT column1, column2 FROM TABLE1
         UNION ALL
         SELECT column1, column2 FROM TABLE2),
     Newest
     AS (SELECT s.*,
                ROW_NUMBER ()
                OVER (
                   PARTITION BY column1,
                                column2,
                                REPLACE (REPLACE ('%(param)s', '5', '6'),
                                         '7',
                                         '8')
                   ORDER BY colum2 DESC)
                   PRIORITY,
                CASE
                   WHEN     REGEXP_INSTR ('%(param)s', '/(//') > 0
                        AND REGEXP_LIKE (column1, '%(param)s')
                   THEN
                      'Y'
                END
                   PARAM_MATCH,
                CASE
                   WHEN     REGEXP_INSTR ('%(param)s', '/(//') = 0
                        AND column1 LIKE '%(param)s'
                   THEN
                      'Y'
                END
                   LIKE_MATCH
           FROM Stats s
          WHERE    (    REGEXP_INSTR ('%(param)s', '/(//') > 0
                    AND REGEXP_LIKE (column1, '%(param)s'))
                OR (    REGEXP_INSTR ('%(param)s', '/(//') = 0
                    AND REGEXP_LIKE ('???', '%(param)s'))
                OR '%(param)s' IS NULL)
SELECT *
  FROM newest;

推荐阅读