N0701 N0702 N0703 N0704 N0705 N0706 N0707 N0708 N0709 N0710 N0711
------- ----------- ----- ---------------- ---------------------------------------- ----- ----- ----- -------- ----------- -----
0310195 2010-1-1 A0 12.3900 B2901014095434a1.00X 0 6922 1 20:00:35 2010-1-1 031我想把N0702 和N0709 合成一个日期因为要查的条件是:
1月4号10:00 到 11:00的据
N0702是月日年
N0709是时分秒有几种办法?
N0702 DATE Y 收款日期
select * from nn07@store01 where to_date(NO702||' '||NO709, 'yyyy-mm-dd hh24:mi:ss')
between to_date('2010-01-04 10:00:00','yyyy-mm-dd hh24:mi:ss') and
to_date('2010-01-04 11:00:00','yyyy-mm-dd hh24:mi:ss')
ORA-00904: "NO709": invalid identifier
to_date('2010-01-04 11:00:00','yyyy-mm-dd hh24:mi:ss')
select * from nn07@store01 where to_date(to_char(N0702)||' '||N0709, 'yyyy-mm-dd hh24:mi:ss')
between to_date('2010-01-04 10:00:00','yyyy-mm-dd hh24:mi:ss') and
to_date('2010-01-04 11:00:00','yyyy-mm-dd hh24:mi:ss')ORA-01861: literal does not match format string
ORA-02063: preceding line from STORE01
select * from nn07@store01 where to_date(to_char(N0702,'yyyy-mm-dd')||' '||N0709, 'yyyy-mm-dd hh24:mi:ss')
between to_date('2010-01-04 10:00:00','yyyy-mm-dd hh24:mi:ss') and
to_date('2010-01-04 11:00:00','yyyy-mm-dd hh24:mi:ss')ORA-01861: literal does not match format string
ORA-02063: preceding line from STORE01
where n0702=to_date('2010-01-09','yyyy-mm-dd') and n0706<>'2'
group by substr(n0709,1,2)
select * from (
select n0702 日期,substr(n0709,1,2) 时间,sum(n0704) 交易金额,count(distinct n0701) 交易次数,count(distinct n0711) 开机数 from nn07@store01
where n0702 between to_date('2010-01-16','yyyy-mm-dd') and to_date('2010-01-22','yyyy-mm-dd') and n0706<>'2'
group by substr(n0709,1,2),n0702
) order by 日期