写了一个比较庞大的查询语句,然后我把查询的语句插入到一个新建的表中,但是在插入表中的数据后,问题出现了,我查询出来的是48条记录,但是插入到表中只有31条数据,其中部分数据插入后发生错误,检查SQL语句,没有问题,查询依旧是48条,但是插入后仍是31条,请问这是怎么回事!
解决方案 »
- 一个简单的SQL.
- 一个存储过程的问题
- 讨论讨论---ORACLE为什么不让存储过程直接返回结果集,却要返回游标的方式呢?
- sql查询
- 如何用语句写出某用户的所有表与表的结构??
- 新建的数据库的字符集和原卸载库的一样. IMP的时候为什么还提示字符集不对??
- 9i,怎样建一个自己的表空间?在SQL*PLUS怎么建?在console中能建吗?建库的时候好像有USER空间吗?但我在console中的方案中怎么没看到呢
- 请问在SQL怎么使用变量!
- weblogic 问题导致交易延时10多秒,并且导致不能打印日志,重启服务后就正常了
- 数据量大表inner join,有办法优化吗?
- 测试的SQL语句的执行时间 ,谢谢帮忙
- 请教oracle分组统计的问题!!!
insert into TBL_Trans_inter_data_ict
这个后面加的是查询语句,比较庞大,就不写了然后执行后,插入表中的数据条数和单查询的条数不对
(
DATATYPE INTEGER,
VOUCHID VARCHAR2(20),
VOUNO VARCHAR2(20),
PROFIT VARCHAR2(50),
BUSTYPE VARCHAR2(10),
SAP3 VARCHAR2(50),
TR_FENPEI VARCHAR2(50),
TR_SHORTTEXT VARCHAR2(50),
TR_LONGTEXT VARCHAR2(50),
DIRECT VARCHAR2(1),
SAPFJZ VARCHAR2(1),
VOUDATE DATE,
ISRETURNED VARCHAR2(1),
BOOKDATE DATE,
SUPCODE VARCHAR2(50),
ENTITY VARCHAR2(50),
EXPENSESACC VARCHAR2(50),
ACCTCODE VARCHAR2(50),
TR_CUR VARCHAR2(50),
TR_AMT NUMBER(20,2),
TR_LAMT NUMBER(20,2),
TR_UNOFFAMT NUMBER(20,2),
TR_LUNOFFAMT NUMBER(20,2),
TR_RATE NUMBER(20,6),
AGINGDATE DATE,
OFFGROUP VARCHAR2(50),
OFFDATE DATE,
ISDELETE VARCHAR2(1),
ISINVALID VARCHAR2(1),
ISOFFED VARCHAR2(1),
VOUUSER VARCHAR2(50),
FINTYPE VARCHAR2(3),
UPDATETIME DATE,
INTRATRANSACTION VARCHAR2(50),
BUSMAKEMAN VARCHAR2(20)
)
select 2 as datatype,'' as vouchid,'' as vouno ,lrzxbm as profit,ywfbbm as bustype,
'' as sap3,'' as TR_FenPei,'' as TR_ShortText,'' as TR_LongText, case when balanorient=1 then '1' else '0' end as Direct,
'0' as SAPFJZ,null as VouDate,''
as IsReturned ,null as BookDate ,ksbm as SupCode,corp.unitcode as Entity,'' expensesAcc,code as AcctCode,
currtypecode as TR_CUR,'' as TR_AMT,
'' as TR_LAMT, amount_yb as TR_UnOffAMT,amount_bb as TR_LUnOffAMT,
'' as TR_RATE,null as AgingDate,'' as OffGroup,null as OffDate,'' as IsDelete,'' as isInvalid,'' as IsOffed, '' as VouUser,'NC' as FinType,'' as UpdateTime,'' as IntraTransaction,'' as BusMakeMan
from (select substr(b.subjcode, 1, 6) as code,
tz.valuecode as ksbm,
tz.valuename as ksmc,
sum(debitamount) - sum(creditamount)
as amount_yb,
sum(localdebitamount) - sum(localcreditamount)
as amount_bb,
tz1.valuecode as ywfbbm,
tz1.valuename as ywfbmc,
tz2.valuecode as lrzxbm,
tz2.valuename as lrzxmc,
b.balanorient,
curr.currtypecode,
a.pk_corp
from (SELECT debitamount, creditamount, localdebitamount, localcreditamount, assid, pk_accsubj,pk_currtype,pk_corp
from gl_balance
where period = '00'
and year ='2010'
and nvl(dr, 0) = 0
and pk_corp ='1014'
and pk_glorgbook='0001V5100000000006A7'
union all
select
b.debitamount,
b.creditamount,
b.localdebitamount,
b.localcreditamount,
b.assid,
pk_accsubj,
b.pk_currtype,a.pk_corp
from gl_voucher a, GL_DETAIL b
where a.PK_VOUCHER = b.PK_VOUCHER
and nvl(a.dr, 0) = 0
and nvl(b.dr, 0) = 0
and a.period > '00'
and a.pk_corp = b.pk_corp
and a.pk_corp ='1014'
and a.year = '2010' and a.period <= '11'
and a.pk_glorgbook='0001V5100000000006A7'
and a.PREPAREDDATE <= '2010-11-05'
and (a.errmessage is null and (a.discardflag <> 'Y') or
a.errmessage is not null or (a.discardflag = 'Y'))
and a.pk_manager = 'N/A' and a.voucherkind <> 2
and a.voucherkind <> 255) a,
bd_accsubj b,
bd_currtype curr,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '00010000000000000073'
and nvl(dr, 0) = 0) tz,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000XWS'
and nvl(dr, 0) = 0) tz1, (SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000Y8E'
and nvl(dr, 0) = 0) tz2
where tz.freevalueid = a.assid
and tz1.freevalueid = a.assid
and tz2.freevalueid = a.assid
and b.pk_accsubj = a.pk_accsubj
and substr(b.subjcode, 1, 4) in ('1131', '2121', '1151','2131')
and curr.pk_currtype=a.pk_currtype
group by tz1.valuecode,
tz1.valuename,
tz.valuecode,
tz2.valuename,
tz2.valuecode,
tz.valuename,
b.balanorient,
curr.currtypecode,
substr(b.subjcode, 1, 6),
a.pk_corp) m,bd_corp corp
where m.amount_bb <> 0 and corp.pk_corp=m.pk_corp and substr(m.ksbm,1,4)='9999'
看差集的记录与你的插入表的数据类型 以及长度进行比较 看看