我在sql里面建了一个链接服务器,用来查询oracle库的表。select * from openquery(select * from Table_临时表)为什么这样查询oracle临时表的是,临时表没有数据返回。可是现在临时明明有数据存在的。而且如果不用sql的链接服务器来查询,直接用链接字符串oracleconnection 来查询临时表,也没有数据返回。但是我在plsql里面查询都是可以查到数据的。这是什么原因?怎么样才能查询到数据,用连接字符串直接查询的话?
调试欢乐多
2、如果建立的是普通表,那么查询不到数据则可能是未commit;
在plsql里面运行没问题。
在程序里调用这个sql语句。有错误。
如果把每个语句后面的;去掉的话 提示ORA-00933: SQL command not properly ended
如果每个语句后面的;不去掉的话 提示ORA-00911: invalid character各位高手帮忙看看是什么问题。我找了好久没找到原因
Delete From Temp_Table;
Commit;
insert into Temp_Table select B.ITEM_CLASS,B.QUANTITY,B.COSTS,B.UNITS_CODE,B.INP_NO,B.ITEM_NO,B.ITEM_CODE,B.ITEM_NAME,B.GENERAL_NAME,B.ITEM_SPEC,B.UNITS
,B.AMOUNT,B.UNIT_PRICE,B.CHARGES,B.CLASS_ON_INP_RCPT,B.DRUG_SIGN,B.BILLING_DATE_TIME,B.INSURANCE_CODE
,B.INSURANCE_PAY,P.CHARGE_TYPE,P.PERSON_STATUS,
--下面根据条件判断
(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN p.PERSON_STATUS in ('06','07','08','09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END) AS PAY_IN,
(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END) AS PAY_OUT,B.INP_NO AS OrderINP_NO
from SUPHISV3.V_CONN_INP_BILL_DETAIL B, SUPHISV3.V_CONN_INP_PATS_VISIT P
where B.INP_NO=P.INP_NO and B.BILLING_DATE_TIME > to_date('2011-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND B.BILLING_DATE_TIME < to_date('2011-05-05 00:00:00','yyyy-mm-dd hh24:mi:ss');
insert into Temp_Table select'小计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END),(B.INP_NO||B.UNITS_CODE) AS OrderINP_NO
from SUPHISV3.V_CONN_INP_BILL_DETAIL B, SUPHISV3.V_CONN_INP_PATS_VISIT P
where B.INP_NO=P.INP_NO and B.BILLING_DATE_TIME > to_date('2011-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND B.BILLING_DATE_TIME < to_date('2011-05-05 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by B.INP_NO,B.UNITS_CODE;insert into Temp_Table select'合计',0,0,'','',0,'','','','',''
,0,0,0,'','','','',0,'',''
,SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN 0
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN CHARGES
ELSE
B.INSURANCE_PAY * B.CHARGES
END
END),
SUM(CASE P.CHARGE_TYPE
WHEN '01' THEN CHARGES
ELSE
CASE
WHEN p.PERSON_STATUS IN ('06','07','08','09') THEN 0
ELSE
(1 - B.INSURANCE_PAY) * B.CHARGES
END
END),999999999999999999999999 AS OrderINP_NO
from SUPHISV3.V_CONN_INP_BILL_DETAIL B, SUPHISV3.V_CONN_INP_PATS_VISIT P
where B.INP_NO=P.INP_NO and B.BILLING_DATE_TIME > to_date('2011-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND B.BILLING_DATE_TIME < to_date('2011-05-05 00:00:00','yyyy-mm-dd hh24:mi:ss');Select * From temp_table t Order By t.orderinp_no;
where B.INP_NO=P.INP_NO and B.BILLING_DATE_TIME > to_date('2011-05-01
里面有 > 在程序调用的时候是不是不认识,要把这个用转义字符吧
试试把这个用转义字符
还有你的程序调用是java调用,还是oracle数据库调用