select sid as tID,sname as tNAME from t1 where tid='b'楼上的兄弟,这种语句我在asa 9,ase 12.5,sql server2000上都执行成功。
to welyngj(不做老实人):select sid || sname as tCbo, (case when exists(Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm')='200303') then '是' else '否' end) as f from t1单独执行没问题,加上select * from ()出的问题。
在sqlserver2000上运行:select phone,zip z from pubs..authors where z=94025 服务器: 消息 207,级别 16,状态 3,行 1 列名 'z' 无效。
to welyngj(不做老实人): 不好意思,select sid as tID,sname as tNAME from t1 where tid='b'是我搞错了,这个语句在sql server2000上执行的确出错了,我没经过试验就说没问题,是我弄错了,不过在asa9上确实没错,ase我没环境,但和asa属同一家,应该也没错。 我把我的试验结果贴上来。
1. select sid as tID,sname as tNAME from t1 where tid='b' Oracle9:"tid",无效的标志符。 Asa9:正常执行。 Sql Server2000:列名 'tid' 无效。 2. select sid || sname as tCbo, (case when exists(Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm')='200303') then '是' else '否' end) as f from t1 Oracle9:正常执行。select sid + sname as tCbo, (case when exists(Select sid From t2 Where sid=t1.sid And year(val1)=2003 and month(val1)=03) then '是' else '否' end) as f from t1 Asa9:正常执行。 Sql Server2000:正常执行。 3. select * from ( select sid || sname as tCbo, (case when exists(Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm')='200303') then '是' else '否' end) as f from t1 ) Oracle9:正常执行。select * from ( select sid + sname as tCbo, (case when exists(Select sid From t2 Where sid=t1.sid And year(val1)=2003 and month(val1)=03) then '是' else '否' end) as f from t1 ) tt Asa9:正常执行。 Sql Server2000:正常执行。 4. select * from ( select sid || sname as tCbo, (case when exists(Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm')='200303') then '是' else '否' end) as f from t1 ) where f='d' Oracle9:先报ora-03113 通信通道的文件结束,再报ora-03114 未连接到oracle,将where f='d'改为where tcbo='d',错误消失。 select * from ( select sid + sname as tCbo, (case when exists(Select sid From t2 Where sid=t1.sid And year(val1)=2003 and month(val1)=03) then '是' else '否' end) as f from t1 ) tt where f='d' Asa9:正常执行。 Sql Server2000:正常执行。
Q1.--在order by 里面可用,在where里面不能用的 Q2.--这个问题我也遇到过,暂时还没解决,估计f包含null
ORA-03113:end-of-file on communication channel 产生原因:通讯不正常结束,从而导致通讯通道终止 解决方法:1>.检查是否有服进程不正常死机,可从alert.log得知 2>.检查sql*Net Driver是否连接到ORACLE可执行程序 3>.检查服务器网络是否正常,如网络不通或不稳定等 4>.检查同一个网上是否有两个同样名字的节点 5>.检查同一个网上是否有重复的IP地址
to hlooo(天穹飞雨) : 可我的服务器在本地机上,而且通过plsql developer执行其他sql语句没问题,再执行这条就出问题了,然后再执行其他的sql也没问题。
针对:select sid as tID,sname as tNAME from t1 where tid='b' 可以写成 select * from (select sid as tID,snmae as tNAME from t1) where tid='b' 第二个问题可以写成: select * from ( select sid || sname as tCbo, (case when exists (Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm') ='2003 03' ) then '是' else '否' end) as f from t1 ) t2 where t2.f='d'
你要明白你的f结果集是什么,你把汉字与字母比较,oracle怎么可以认识?
to rolandzhang() : 第二个问题按你的写法仍未解决。 to jackjingsg(飞翔的精灵) : 汉字和字母不都是字符串吗?有什么不能比较的,即使都改成字母或汉字,也是出一样的问题。
----错误码解释如下: Cause An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8, two task, software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down. Action If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of customer support. --你一直都认识不到你所应用f的结果集是什么
第二个问题可以写成: select * from ( select sid || sname as tCbo, (case when exists (Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm') ='2003 03' ) then '是' else '否' end) as f from t1 ) t2 where t2.f='是'
to jackjingsg(飞翔的精灵): 老兄,你把你的代码试验成功后再给我好吗? 你给我的代码出了一样的问题。 我的环境是win2000prosp4+oracle9.2.0.1.0,客户端和服务器都在一台机器上, 服务器进程也没死。
是case when语句的问题,应该是一个bug,:) 我把上面的语句改写成decode,实现相同的功能,没有任何问题 SQL> select * from ( 2 select tcbo,decode(nvl(sign,0),1,'是','否') f from ( 3 select sid||sname as tCbo, 4 (Select 1 From t2 5 Where sid=t1.sid And to_char(val1,'yyyymm')='200303' 6 ) as sign from t1) 7 ) 8 where f='是';TCBO F ------------------------------ -- aaa 是SQL>
请修改语句。
where tid='b'楼上的兄弟,这种语句我在asa 9,ase 12.5,sql server2000上都执行成功。
(case when exists(Select sid From t2
Where sid=t1.sid And to_char(val1,'yyyymm')='200303')
then '是' else '否' end) as f
from t1单独执行没问题,加上select * from ()出的问题。
服务器: 消息 207,级别 16,状态 3,行 1
列名 'z' 无效。
不好意思,select sid as tID,sname as tNAME from t1
where tid='b'是我搞错了,这个语句在sql server2000上执行的确出错了,我没经过试验就说没问题,是我弄错了,不过在asa9上确实没错,ase我没环境,但和asa属同一家,应该也没错。
我把我的试验结果贴上来。
select sid as tID,sname as tNAME from t1
where tid='b'
Oracle9:"tid",无效的标志符。
Asa9:正常执行。
Sql Server2000:列名 'tid' 无效。
2.
select sid || sname as tCbo,
(case when exists(Select sid From t2
Where sid=t1.sid And to_char(val1,'yyyymm')='200303')
then '是' else '否' end) as f
from t1
Oracle9:正常执行。select sid + sname as tCbo,
(case when exists(Select sid From t2
Where sid=t1.sid And year(val1)=2003 and month(val1)=03)
then '是' else '否' end) as f
from t1
Asa9:正常执行。
Sql Server2000:正常执行。
3.
select * from
(
select sid || sname as tCbo,
(case when exists(Select sid From t2
Where sid=t1.sid And to_char(val1,'yyyymm')='200303')
then '是' else '否' end) as f
from t1
)
Oracle9:正常执行。select * from
(
select sid + sname as tCbo,
(case when exists(Select sid From t2
Where sid=t1.sid And year(val1)=2003 and month(val1)=03)
then '是' else '否' end) as f
from t1
) tt
Asa9:正常执行。
Sql Server2000:正常执行。
4.
select * from
(
select sid || sname as tCbo,
(case when exists(Select sid From t2
Where sid=t1.sid And to_char(val1,'yyyymm')='200303')
then '是' else '否' end) as f
from t1
)
where f='d'
Oracle9:先报ora-03113 通信通道的文件结束,再报ora-03114 未连接到oracle,将where f='d'改为where tcbo='d',错误消失。
select * from
(
select sid + sname as tCbo,
(case when exists(Select sid From t2
Where sid=t1.sid And year(val1)=2003 and month(val1)=03)
then '是' else '否' end) as f
from t1
) tt
where f='d'
Asa9:正常执行。
Sql Server2000:正常执行。
Q2.--这个问题我也遇到过,暂时还没解决,估计f包含null
可我的服务器在本地机上,而且通过plsql developer执行其他sql语句没问题,再执行这条就出问题了,然后再执行其他的sql也没问题。
select * from (select sid as tID,snmae as tNAME from t1) where tid='b'
第二个问题可以写成:
select * from
( select sid || sname as tCbo,
(case when exists
(Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm')
='2003 03' )
then '是'
else '否' end) as f
from t1
) t2
where t2.f='d'
第二个问题按你的写法仍未解决。
to jackjingsg(飞翔的精灵) :
汉字和字母不都是字符串吗?有什么不能比较的,即使都改成字母或汉字,也是出一样的问题。
Cause An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8, two task, software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down.
Action
If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of customer support. --你一直都认识不到你所应用f的结果集是什么
select * from
( select sid || sname as tCbo,
(case when exists
(Select sid From t2 Where sid=t1.sid And to_char(val1,'yyyymm')
='2003 03' )
then '是'
else '否' end) as f
from t1
) t2
where t2.f='是'
老兄,你把你的代码试验成功后再给我好吗?
你给我的代码出了一样的问题。
我的环境是win2000prosp4+oracle9.2.0.1.0,客户端和服务器都在一台机器上,
服务器进程也没死。
我的别的语句执行都没问题,就这个语句出问题了。
我把上面的语句改写成decode,实现相同的功能,没有任何问题
SQL> select * from (
2 select tcbo,decode(nvl(sign,0),1,'是','否') f from (
3 select sid||sname as tCbo,
4 (Select 1 From t2
5 Where sid=t1.sid And to_char(val1,'yyyymm')='200303'
6 ) as sign from t1)
7 )
8 where f='是';TCBO F
------------------------------ --
aaa 是SQL>