我写的条SQL,但是运行时出错了,问题是:以下"红字"部份我必须采用变量的形式,运行出错的SQL在以下第一段,(第二段是在第一段基础上变:红字部份改为了常量,就不会出错了.不知道第一条语句有什么问题,请助各位帮助!)--运行出错
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') AS SSSQ_Q,to_date(BCM,'YYYY.MM.DD') AS SSSQ_Z
From TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) A
where to_date('2008.06.01','YYYY.MM.DD') Between SSSQ_Q AND SSSQ_Z--运行正常
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') as SSSQ_Q,
to_date(BCM,'YYYY.MM.DD') as SSSQ_Z from TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) AAA
where to_date('2008.06.01','YYYY.MM.DD') Between to_date('2008.06.01','YYYY.MM.DD') AND to_date('2008.06.30','YYYY.MM.DD')
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') AS SSSQ_Q,to_date(BCM,'YYYY.MM.DD') AS SSSQ_Z
From TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) A
where to_date('2008.06.01','YYYY.MM.DD') Between SSSQ_Q AND SSSQ_Z--运行正常
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') as SSSQ_Q,
to_date(BCM,'YYYY.MM.DD') as SSSQ_Z from TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) AAA
where to_date('2008.06.01','YYYY.MM.DD') Between to_date('2008.06.01','YYYY.MM.DD') AND to_date('2008.06.30','YYYY.MM.DD')
--运行出错
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') AS SSSQ_Q,to_date(BCM,'YYYY.MM.DD') AS SSSQ_Z
From TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) A
where to_date('2008.06.01','YYYY.MM.DD') Between SSSQ_Q AND SSSQ_Z
所以需要修改你给出的值,并且需要给出date类型的值。
例子如下:
SELECT * FROM v$session s WHERE s.LOGON_TIME
BETWEEN &START AND &END;在提示中给出:sysdate-1和sysdate,就会给出结果。
1900/01/01 2222/12/31
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') as SSSQ_Q,
to_date(BCM,'YYYY.MM.DD') as SSSQ_Z from TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) AAA
where to_date('2008.06.01','YYYY.MM.DD') Between to_date('2008.06.01','YYYY.MM.DD') AND to_date('2008.06.30','YYYY.MM.DD') 这条语句都可以运行成功,这条语句与运行出错的那一条就是: 红色的部份不同,一个是变量,一个是常量 --运行出错
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') AS SSSQ_Q,to_date(BCM,'YYYY.MM.DD') AS SSSQ_Z
From TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) A
where to_date('2008.06.01','YYYY.MM.DD') Between SSSQ_Q AND SSSQ_Z
SELECT TNCS01.DM,
TNCS01.DMMC,
TO_DATE(TNCS01.JKM, 'YYYY.MM.DD') AS SSSQ_Q,
TO_DATE(TNCS01.BCM, 'YYYY.MM.DD') AS SSSQ_Z
FROM TNCS01
WHERE TNCS01.DMLB = 'NSSBBD'
AND EXISTS (SELECT SBLX_DM
FROM TNSB01
WHERE NSRDZDAH = 500105000001247173
AND TNCS01.DM = SBLX_DM
AND ((TO_DATE('2008.06.01', 'YYYY.MM.DD') BETWEEN
YXQ_Q AND YXQ_Z) OR YXQ_Z IS NULL))
AND '2008/06/01' BETWEEN SSSQ_Q AND SSSQ_Z;
建议改成如下试试:
SELECT TNCS01.DM,
TNCS01.DMMC,
TO_DATE(TNCS01.JKM, 'YYYY.MM.DD') AS SSSQ_Q,
TO_DATE(TNCS01.BCM, 'YYYY.MM.DD') AS SSSQ_Z
FROM TNCS01
WHERE TNCS01.DMLB = 'NSSBBD'
AND EXISTS (SELECT SBLX_DM
FROM TNSB01
WHERE NSRDZDAH = 500105000001247173
AND TNCS01.DM = SBLX_DM
AND ((TO_DATE('2008.06.01', 'YYYY.MM.DD') BETWEEN
YXQ_Q AND YXQ_Z) OR YXQ_Z IS NULL))
AND '2008.06.01' BETWEEN JKM AND BCM;
select * from (
select DM,DMMC,to_date(JKM,'YYYY.MM.DD') AS SSSQ_Q,to_date(BCM,'YYYY.MM.DD') AS SSSQ_Z
From TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
) A
where to_date('2008.06.01','YYYY.MM.DD') Between SSSQ_Q AND SSSQ_Z修改成为: select DM,DMMC,to_date(JKM,'YYYY.MM.DD') AS SSSQ_Q,to_date(BCM,'YYYY.MM.DD') AS SSSQ_Z
From TNCS01 where DMLB='NSSBBD' and dm in (select SBLX_DM from TNSB01 where NSRDZDAH=500105000001247173 and
((to_date('2008.06.01','YYYY.MM.DD') Between YXQ_Q and YXQ_Z) or YXQ_Z is null))
and to_date('2008.06.01','YYYY.MM.DD') Between JCK AND BCM