select f.FILE_ID,f.FILE_NAME,f.END_DATE, f.CREATE_MAN_ID,f.CREATE_MAN_NAME,f.CREATE_TIME,COUNT(f.FILE_ID) billNum,
CASE
WHEN CAST(SYSDATE - TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS number)< 0
THEN '超过'||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),9,2)||'天'
||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),12,2)||'小时'
||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),15,2)||'分钟'
WHEN CAST(SYSDATE - TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS number)>0
tHEN substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),9,2)
||'天'||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),12,2)||'小时'
||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),15,2)||'分钟'
ELSE '0'
End remainDate
from TD_SKL_DEMANDFILE f,TD_SKL_DEMANDSUBMIT s
where f.FILE_ID=s.FILE_ID
group by f.FILE_ID,f.FILE_NAME,f.END_DATE, f.CREATE_MAN_ID,f.CREATE_MAN_NAME,f.CREATE_TIME
CASE
WHEN CAST(SYSDATE - TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS number)< 0
THEN '超过'||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),9,2)||'天'
||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),12,2)||'小时'
||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),15,2)||'分钟'
WHEN CAST(SYSDATE - TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS number)>0
tHEN substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),9,2)
||'天'||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),12,2)||'小时'
||substr(CAST(SYSDATE AS TIMESTAMP)-CAST(TO_DATE (f.END_DATE, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP),15,2)||'分钟'
ELSE '0'
End remainDate
from TD_SKL_DEMANDFILE f,TD_SKL_DEMANDSUBMIT s
where f.FILE_ID=s.FILE_ID
group by f.FILE_ID,f.FILE_NAME,f.END_DATE, f.CREATE_MAN_ID,f.CREATE_MAN_NAME,f.CREATE_TIME