问题说明,这句SQL在我用trandate和mchntname查询时没有任何问题,但是只按trandate查询时就会报错ORA-01722: invalid number
select distinct mchntname ,trandate,trantime,money, trandate||' '||trantime tranhole,'交易方式:'||way||' 交易状态:'||state abstract from (
(
select m.mchntname mchntname,a.trandate trandate,a.trantime trantime,to_number(NVL(a.money,0)) money,
decode(trim(d.stdiotrancode),'320007','建行转账','310004','建行转账','320008','支付宝转账','320009','财付通转账') way,
decode(trim(d.transtat),'0','交易初始状态','1','建行失败','2','建行成功','3','账务失败','4','交易成功') state
from m_merchant m,acct_trans_jnl a, d_transferjnl d
where m.mchntid=a.customacctno and a.customacctno=d.cusloginacct
)
union all
(
select m.mchntname mchntname ,
to_char(to_date(substr(a.trandate,0,10),'YYYY-MM-DD '),'YYYYMMDD ') trandate,
to_char(to_date(substr((a.trandate),11,18),'HH24:MI:SS'),'HH24MISS' )trantime,
to_number(NVL(a.money,0)) money,
decode(trim(a.tranChannel),'300000','手工入账','300001','邮政入账',
'320007','建行转账','320008','支付宝转账','300003','退款','300004','返利','300005','扣款','300002','其他入账',
'320009','财付通转账') way,
decode(a.mzflag,'0','入账成功','1','抹账成功','2','入账待审核','3','入账审核不通过','4','抹账待审核','5','抹账审核不通过') state
from m_merchant m, acct_rz_jnl a
where m.mchntid=a.customacctno
)
union all
(
select m.mchntname mchntname,d.trandate trandate,d.trantime trantime,to_number(NVL(d.retainfld1,0)) money,
decode(trim(d.recvflg),'0035','联通充值','0037','移动充值','0039','电信充值') way,
decode(trim(d.transtat),'0','交易初始状态','1','账务失败','2','账务成功','3','取卡密失败','4','取卡密成功',
'5','去运营商初始状态','6','运营商失败','7','运营商成功','8','交易成功') state
from m_merchant m,d_directadd d
where m.mchntid=d.cusloginacct
)
) t
where t.trandate='20110620' and t.mchntname='通融通';
select distinct mchntname ,trandate,trantime,money, trandate||' '||trantime tranhole,'交易方式:'||way||' 交易状态:'||state abstract from (
(
select m.mchntname mchntname,a.trandate trandate,a.trantime trantime,to_number(NVL(a.money,0)) money,
decode(trim(d.stdiotrancode),'320007','建行转账','310004','建行转账','320008','支付宝转账','320009','财付通转账') way,
decode(trim(d.transtat),'0','交易初始状态','1','建行失败','2','建行成功','3','账务失败','4','交易成功') state
from m_merchant m,acct_trans_jnl a, d_transferjnl d
where m.mchntid=a.customacctno and a.customacctno=d.cusloginacct
)
union all
(
select m.mchntname mchntname ,
to_char(to_date(substr(a.trandate,0,10),'YYYY-MM-DD '),'YYYYMMDD ') trandate,
to_char(to_date(substr((a.trandate),11,18),'HH24:MI:SS'),'HH24MISS' )trantime,
to_number(NVL(a.money,0)) money,
decode(trim(a.tranChannel),'300000','手工入账','300001','邮政入账',
'320007','建行转账','320008','支付宝转账','300003','退款','300004','返利','300005','扣款','300002','其他入账',
'320009','财付通转账') way,
decode(a.mzflag,'0','入账成功','1','抹账成功','2','入账待审核','3','入账审核不通过','4','抹账待审核','5','抹账审核不通过') state
from m_merchant m, acct_rz_jnl a
where m.mchntid=a.customacctno
)
union all
(
select m.mchntname mchntname,d.trandate trandate,d.trantime trantime,to_number(NVL(d.retainfld1,0)) money,
decode(trim(d.recvflg),'0035','联通充值','0037','移动充值','0039','电信充值') way,
decode(trim(d.transtat),'0','交易初始状态','1','账务失败','2','账务成功','3','取卡密失败','4','取卡密成功',
'5','去运营商初始状态','6','运营商失败','7','运营商成功','8','交易成功') state
from m_merchant m,d_directadd d
where m.mchntid=d.cusloginacct
)
) t
where t.trandate='20110620' and t.mchntname='通融通';
解决方案 »
- 寻求*.docx,*.xlsx,*.pptx文件下载后变成.zip文件的解决办法
- 做了一个小的登录程序,运行的时候跳出这样的错误,有熟悉的人帮我看看呢
- <s:i18n name="ApplicationResources"> 是什么意思
- 请问统计报表数据反查 数据库怎么设计合理无耦合性
- 关于JSValidation(验证框架)
- 各位高手进来看看,BS系统用户数量限制问题。==========
- 关于静态变量--求高手,高手请进
- 请问各位大虾,目前流行什么开发模式啊,struts?spring?jsf?
- 关于下拉框的问题
- 拦截器如何获取struts.xml的配置信息
- 网站流量统计 急!!!!!!!!
- 繁体乱码问题,急!!!
而其他 数据 有不符合规则的存在 所以日期型的最好用Date保存 函数也多 方便用 存字符串就会晕倒各种稀奇古怪的问题