用的是oracle9的数据库
1. SELECT SYSDATE-7.5/24 FROM DUAL
这是一个很简单的语句,运行正常2. SELECT SYSDATE-TO_NUMBER( select keyvalue FROM sdb_tb_report_config
where linkname='ACCT' and keytype='HOUR' )
FROM DUAL
报错ORA-00936 其中,select keyvalue FROM sdb_tb_report_config where linkname='ACCT' and keytype='HOUR' 的返回结果是7.5简言之,想实现SELECT SYSDATE-?/24 FROM DUAL 这样一个功能,但?是在数据库的表中配置的,怎么写SQL语句啊?
1. SELECT SYSDATE-7.5/24 FROM DUAL
这是一个很简单的语句,运行正常2. SELECT SYSDATE-TO_NUMBER( select keyvalue FROM sdb_tb_report_config
where linkname='ACCT' and keytype='HOUR' )
FROM DUAL
报错ORA-00936 其中,select keyvalue FROM sdb_tb_report_config where linkname='ACCT' and keytype='HOUR' 的返回结果是7.5简言之,想实现SELECT SYSDATE-?/24 FROM DUAL 这样一个功能,但?是在数据库的表中配置的,怎么写SQL语句啊?
DECLARE
v_keyvalue sdb_tb_report_config.keyvalue%TYPE;
rs DATE;
BEGIN
select keyvalue INTO v_keyvalue FROM sdb_tb_report_config
where linkname='ACCT' and keytype='HOUR';
SELECT SYSDATE-TO_NUMBER( v_keyvalue ) /24 INTO rs FROM DUAL;
Dbms_Output.put_line(rs);
END;
--一条SQL 可以这样:
SELECT SYSDATE- (select TO_NUMBER(keyvalue) FROM sdb_tb_report_config
where linkname='ACCT' and keytype='HOUR' )
FROM DUAL
where linkname='ACCT' and keytype='HOUR' )
FROM DUAL;
WHERE linkname='ACCT' AND keytype='HOUR' ) )
FROM DUAL;