******这条SQL为什么不能执行，解决立即给分*****

[Ranma_True]

表结构如下：
create table T1
(
SID VARCHAR2(10),
SNAME VARCHAR2(20),
VAL1 VARCHAR2(20),
VAL2 NUMBER,
VAL3 DATE
)
create table T2
(
SID VARCHAR2(10),
VAL1 DATE
)
表T1内容如下：
1 Insert into t1 values ('a','aa','aa1',1,to_date('2003-03-03','yyyy-mm-dd'))
2 Insert into t1 values ('b','bb','bb1',1,to_date('2003-04-03','yyyy-mm-dd'))
3 Insert into t1 values ('c','cc','cc1',1,to_date('2003-05-03','yyyy-mm-dd'))
4 Insert into t1 values ('d','dd','dd1',1,to_date('2003-06-03','yyyy-mm-dd'))

表T2内容如下：
1 Insert into t2 values ('a',to_date('2003-03-01','yyyy-mm-dd'))
2 Insert into t2 values ('b',to_date('2003-04-01','yyyy-mm-dd'))
3 Insert into t2 values ('c',to_date('2003-05-01','yyyy-mm-dd'))

Q1.Oracle9不支持以下语句？
select sid as tID,sname as tNAME from t1
where tid='b'
如果我要表示选择sid='b'的记录，难道必须用where sid='b'而不能用where tid='b'吗？
如果语句改为
select sid || sname as tCbo from t1
想从中找出sid || sname='bbb'的记录难道也只能用where sid || sname='bbb',而不能用where tCbo='bbb'吗？
如果是更复杂的复合字段呢？

Q2.经典错误ora-03113
执行以下语句
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'
先报ora-03113 通信通道的文件结束，再报ora-03114 未连接到oracle，将where f='d'改为where tcbo='d'，错误消失。

以下是我的试验结果。
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：正常执行。

[Ranma_True]

to hlooo(天穹飞雨) ：
可我的服务器在本地机上，而且通过plsql developer执行其他sql语句没问题，再执行这条就出问题了，然后再执行其他的sql也没问题。

[dinya2003]

Q1.Oracle9不支持以下语句？
A1:oracle不支持别名条件.

Q2
ORA-03113 end-of-file on communication channel

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.

In addition, this message could occur when any of the following statements/commands have been issued:

ALTER SYSTEM KILL SESSION ... IMMEDIATE
ALTER SYSTEM DISCONNECT SESSION ... IMMEDIATE
SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL
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 Oracle Support Services.


ORA-03114 not connected to ORACLE

Cause: A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may happen if communication trouble causes a disconnection. In addition, this message could occur when ALTER SYSTEM KILL SESSION or ALTER SYSTEM DISCONNECT SESSION were issued with the IMMEDIATE qualifier because, in those cases, the client's connection to the database is terminated without waiting for the client to issue a request.

Action: Try again. If the message recurs and the program is user written, check the program.

[bzszp]

在同一层的where里面使用别名是不可以的，只能通过写列名，或者嵌套

[jackjingsg]

只有一个问题：oracle里面不能在用一语句的条件中直接调用字段别名，因为你的表只认识字段名，这个与oracle的执行机制有关。

[Ranma_True]

这些我的文档上也有，可和我的语句对不上号呀，我的数据库在本地机上。
数据库连接绝对没问题。

[Ranma_True]

关键是第二个问题

[jackjingsg]

是这样的，你的别名ｆ其结果只可能是'是'或者'否'，并不是sid,因此会报错；
你在子查询中定义了别名tCbo，这是在主查询中就可以调用tCbo作为字段的替代名了。

[hlooo]

ORA-03113：end-of-file on communication channel

产生原因：通讯不正常结束，从而导致通讯通道终止

解决方法：1>.检查是否有服进程不正常死机，可从alert.log得知

2>.检查sql*Net Driver是否连接到ORACLE可执行程序

3>.检查服务器网络是否正常，如网络不通或不稳定等

4>.检查同一个网上是否有两个同样名字的节点

5>.检查同一个网上是否有重复的IP地址

[Ranma_True]

下面的继续呀

[洪十二]

select * from(select sid || sname as tCbo from t1) where tCbo='bbb'

[qiansl]

太长了。

[Ranma_True]

up