Question

SQL语句提示缺失右括号，求高人解答

select * from
(select rownum RN, t.* from
(
SELECT T1.meta_id,T1.ext_attr1,T1.ext_attr36,T1.ext_attr39,
substr(ltrim (T1.path,'C00/') ,1,instr(ltrim (T1.path,'C00/') ||'/','/',1,1)-1) as ext_attr37,
case when (substr(ltrim (T1.path,'C00/') ,instr(ltrim (T1.path,'C00/') ||'/','/',1,1)+1,instr(ltrim (T1.path,'C00/') ||'/','/',1,2)-instr(ltrim (T1.path,'C00/') ||'/','/',1,1)-1)) is null then '/' else substr(ltrim (T1.path,'C00/') ,instr(ltrim (T1.path,'C00/') ||'/','/',1,1)+1,instr(ltrim (T1.path,'C00/') ||'/','/',1,2)-instr(ltrim (T1.path,'C00/') ||'/','/',1,1)-1) end as ext_attr38,
T1.meta_cn_name,T1.meta_en_name,T1.note,T1.ext_attr2, T1.ext_attr3, T1.ext_attr4, T1.ext_attr5,
T1.ext_attr6, T1.ext_attr7, T1.ext_attr8, T1.ext_attr9, T1.ext_attr10, T1.ext_attr11,
T1.ext_attr12, T1.ext_attr13, T1.input_staff_id, T1.check_ind, T1.check_dttm, T1.check_staff_id, T1.check_out_ind,
T1.check_out_dttm, T1.check_out_staff_id, T1.end_dttm, T1.ver_id, T1.path, T1.doc_id
from am_meta_entity_info T1
where 1=1 and T1.inst_cate_cd in('C07100') and to_Date(T1.START_DTTM,'yyyy-mm-dd') >= to_date('2015-06-01','yyyy-mm-dd') and to_Date(T1.START_DTTM,'yyyy-mm-dd') <= to_date('2015-06-23','yyyy-mm-dd'))) and T1.check_ind ='Y'
and T1.oper_type!='3'
and T1.end_dttm like '2999-12-31 00:00:00%'
order by ext_attr1
) t where rownum < 21 ) where RN > 0
错误出现的地点是“ and T1.check_ind ='Y'”这句的and上

Answer 1

try this:

/* replace next sql script's context:
LPOS1 ==> instr(ltrim(T1.path,'C00/') ||'/','/',1,1)
LPOS2 ==> instr(ltrim(T1.path,'C00/') ||'/','/',1,2)
*/

select * from (
    select rownum RN, t.*
    from (
        SELECT T1.meta_id
            ,T1.ext_attr1
            ,T1.ext_attr36
            ,T1.ext_attr39
            ,substr(
                ltrim(T1.path,'C00/'),
                1,
                LPOS1-1
                ) as ext_attr37
            ,case when substr(
                            ltrim(T1.path,'C00/'),
                            LPOS1+1,
                            LPOS2-LPOS1-1
                            ) is null
                  then '/'
             else substr(
                        ltrim(T1.path,'C00/'),
                        LPOS1+1,
                        LPOS2-LPOS1-1)
             end as ext_attr38
            ,T1.meta_cn_name
            ,T1.meta_en_name
            ,T1.note
            ,T1.ext_attr2
            ,T1.ext_attr3
            ,T1.ext_attr4
            ,T1.ext_attr5
            ,T1.ext_attr6
            ,T1.ext_attr7
            ,T1.ext_attr8
            ,T1.ext_attr9
            ,T1.ext_attr10
            ,T1.ext_attr11
            ,T1.ext_attr12
            ,T1.ext_attr13
            ,T1.input_staff_id
            ,T1.check_ind
            ,T1.check_dttm
            ,T1.check_staff_id
            ,T1.check_out_ind
            ,T1.check_out_dttm
            ,T1.check_out_staff_id
            ,T1.end_dttm
            ,T1.ver_id
            ,T1.path
            ,T1.doc_id 
        from am_meta_entity_info T1
        where 1=1
        and T1.inst_cate_cd in('C07100')
        and to_Date(T1.START_DTTM,'yyyy-mm-dd')
         >= to_date('2015-06-01','yyyy-mm-dd')
        and to_Date(T1.START_DTTM,'yyyy-mm-dd')
         <= to_date('2015-06-23','yyyy-mm-dd')
        and T1.check_ind ='Y'
        and T1.oper_type!='3'
        and T1.end_dttm like '2999-12-31 00:00:00%'
        order by ext_attr1
    ) t
    where rownum < 21
) where RN > 0

追问

还是不对哦：

追答

T1.START_DTTM 是varchar or datetime?
datetime 字段的话试试看：
and T1.START_DTTM >= to_date('2015-06-01','yyyy-mm-dd')
and T1.START_DTTM <= to_date('2015-06-23','yyyy-mm-dd')

追问

是varchar，而且数据库里面的是到秒的

怎么去掉秒以后的精度啊？

追答

既然字段是varchar, 参数也是varchar, 就直接比较吧。
大于等于给定的开始日期没有问题；
小于等于给定的结束日期需要截取前10位字符串。

Answer 2

这个：to_date('2015-06-23','yyyy-mm-dd'))) and T1.check_ind ='Y'

改为：to_date('2015-06-23','yyyy-mm-dd') and T1.check_ind ='Y'

追问

我改过来了，但是报“ORA-01830: 日期格式图片在转换整个输入字符串之前结束”