Question

我在做查询的plsql查询的时候报错 ora -01722错误，下面是我的查询语句

这个是我的语句有大神帮看下吗？
select C.GLLSH,
C.ZYLSH,
C.YZLSH,
C.BRBSLB,
C.YZGLFYLSH,
C.CZR,
'1' as choose,
' 0 ' as reupload,
c.YZFY_DRSC,
c.fphm
from (select (case
when a.sbxh is not null then
to_char(a.sbxh)
else
'-'
end) as GLLSH,
(case
when b.input0 is not null then
b.input0
else
'-'
end) as ZYLSH,
(a.cfsb) as YZLSH,
'1' as BRBSLB,
(case
when a.cfsb is not null then
to_char(a.cfsb)
else
'-'
end) AS YZGLFYLSH,
(case
when (select ygxm
from gy_ygdm
where d.fygh = gy_ygdm.ygdm
and rownum = '1') is not null then
(select ygxm from gy_ygdm where d.fygh = gy_ygdm.ygdm)
else
'-'
end) as CZR,
(select fyrq from ms_cf01 where ms_cf01.cfsb = a.cfsb) as FYRQ,
a.YZFY_DRSC,
e.mzlb,
e.brxz,
d.fphm
from ms_cf02 a,
suphisv3.v_zr_mzdj b,
ms_cf01 d,
ms_ghmx e,
ms_mzxx f,
WJW_DRSC_MZ_FPHM g
where d.cfsb = a.cfsb
and d.brid = b.patient_id
and d.fphm = f.fphm
and e.sbxh = f.ghgl
and f.fphm = g.fphm) c
where c.fyrq >= to_date('20160401 00:00:00','yyyymmdd hh24:mi:ss')
and c.fyrq <= to_date('20160430 00:00:00','yyyymmdd hh24:mi:ss')
and c.YZFY_DRSC is null
and c.mzlb = '1'
and c.brxz = '1002'
group by C.GLLSH,
C.ZYLSH,
C.YZLSH,
C.BRBSLB,
C.YZGLFYLSH,
C.CZR,
c.YZFY_DRSC,
c.fphm

Answer 1

我仔细看了一下你的条件 最终外面的查询where结果是where null>8 and 0>0 那么这个条件肯定会报错的，不是条件合在一起报错 。
第二条语句 没有指定HAMDSN is null ，所以没有问题

第三条语句 虽然指定了HAMDSN is null ， 但面的表和外面的表没有关系，所以也没有问题
注：sql语句如果没有特指的话 后面的where会自动过滤掉了null（因为默认情况下 null不参与运算的 这个楼主应该知道） 所以就不会报错

追问

我自己找到原因了。是d.brid = b.patient_id这个关联的字段有null

Answer 2

表结构贴一下啊