如果所有的id均属number字段的话,这样: select a.id,decode(sign(a.b_id-b.id),0,b.name,decode(sign(a.c_id-c.id),0,c.name,a.name)) names from a,b,c;如果所有的id均属varchar2字符字段的话,这样: select a.id,decode(sign(to_number(a.b_id)-to_number(b.id)),0,b.name,decode(sign(to_number(a.c_id)-to_number(c.id)),0,c.name,a.name)) names from a,b,c;
如果所有的id均属number字段的话,这样: select a.id,decode(sign(a.b_id-b.id),0,b.name,decode(sign(a.c_id-c.id),0,c.name,a.name)) names from a,b,c;如果所有的id均属varchar2字符字段的话,这样: select a.id,decode(sign(to_number(a.b_id)-to_number(b.id)),0,b.name,decode(sign(to_number(a.c_id)-to_number(c.id)),0,c.name,a.name)) names from a,b,c;
select a.*,b.name bname,c.name cname from a,b,c where a.b_id= b.id(+) and a.c_id=c.id(+) 这是一个外连接的问题详细请参考有关书。
select a.id,a.name,nvl(b.name,'缺省值') as b.name,nvl(c.name,'缺省值') as c.name from a,b,c where a.b_id=b.id(+) and a.c_id=c.id(+)
select a.id,a.name,nvl(b.name,'缺省值') as b.name,nvl(c.name,'缺省值') as c.name from a,b,c where a.b_id=b.id(+) and a.c_id=c.id(+) 个人认为这句好!
同意: armu80830(此情可待), 已经无话可说了
谢谢大家!! 但是有个问题,这个语句在sql plus中是可以运行的,但用java调用却报错: :java.sql.SQLException: 无效的列名 我是这样写的: select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片') as movie.name,nvl(stb.stbIP, 0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))谢谢大家了!
select a.id,a.name,(select nvl(name,'缺省值') from b where id=b_id) b.name,(select nvl(c.name,'缺省值') from c where id=c_id) c.name from a若上面还是不行,那下载一个驱动吧: http://otn.oracle.com/software/tech/java/sqlj_jdbc/content.html 注意:注册用户
你在jDBC里面不能设置: stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
'select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片') as movie.name,nvl(stb.stbIP,0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))'可能是编译器方面的原因,改为如下,试一下: 'select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'||chr(39)||'未知影片'||chr(39)||') as movie.name,nvl(stb.stbIP, 0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='||chr(39)||'n'||chr(39)||' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))'
还是不行啊,报错:java.sql.SQLException: ORA-00907: missing right parenthesis我的oracle用的是8.17 jdbc用的是oracle自带的,
去掉(+)是否正常?如果去掉后正常,可以为这个查询建一个视图,java中查询视图。
去掉nvl , 保留(+)是可以正常的
在你的程序中把要执行的SQL语句打印出来看看,是不是写错了,八成是引号问题。
程序如下: stmt = myconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); String sql = "select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片') as movie.name,nvl(stb.stbIP, 0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))"; rs = stmt.executeQuery(sql); while (rs.next())报错如下: ORA-00923: FROM keyword not found where expected 如果我把sql改为: sql = "select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片'),nvl(stb.stbIP, 0) from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))";则报错如下: java.sql.SQLException: 无效的列名但是,这个sql语句在sqlplus中执行完全正常!!
select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片') as movie.name,nvl(stb.stbIP, 0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+)) 这句话在sqlplus中报错如下: ORA-00923: FROM keyword not found where expectedselect ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片'),nvl(stb.stbIP, 0) from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+)) 这句话在sqlplus中执行完全正常
String sql = "select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片') as movie_name,nvl(stb.stbIP, 0) as stb_stbIP from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))";
select a.id,decode(sign(a.b_id-b.id),0,b.name,decode(sign(a.c_id-c.id),0,c.name,a.name)) names from a,b,c;如果所有的id均属varchar2字符字段的话,这样:
select a.id,decode(sign(to_number(a.b_id)-to_number(b.id)),0,b.name,decode(sign(to_number(a.c_id)-to_number(c.id)),0,c.name,a.name)) names from a,b,c;
select a.id,decode(sign(a.b_id-b.id),0,b.name,decode(sign(a.c_id-c.id),0,c.name,a.name)) names from a,b,c;如果所有的id均属varchar2字符字段的话,这样:
select a.id,decode(sign(to_number(a.b_id)-to_number(b.id)),0,b.name,decode(sign(to_number(a.c_id)-to_number(c.id)),0,c.name,a.name)) names from a,b,c;
这是一个外连接的问题详细请参考有关书。
from a,b,c
where a.b_id=b.id(+) and a.c_id=c.id(+)
from a,b,c
where a.b_id=b.id(+) and a.c_id=c.id(+)
个人认为这句好!
已经无话可说了
:java.sql.SQLException: 无效的列名
我是这样写的:
select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片') as movie.name,nvl(stb.stbIP, 0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))谢谢大家了!
看有什么提示估计可能是你写错了字段名了
from a若上面还是不行,那下载一个驱动吧:
http://otn.oracle.com/software/tech/java/sqlj_jdbc/content.html
注意:注册用户
stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
'select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'||chr(39)||'未知影片'||chr(39)||') as movie.name,nvl(stb.stbIP, 0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='||chr(39)||'n'||chr(39)||' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))'
stmt = myconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String sql = "select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片') as movie.name,nvl(stb.stbIP, 0) as stb.stbIP from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))";
rs = stmt.executeQuery(sql);
while (rs.next())报错如下:
ORA-00923: FROM keyword not found where expected
如果我把sql改为:
sql = "select ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片'),nvl(stb.stbIP, 0) from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))";则报错如下:
java.sql.SQLException: 无效的列名但是,这个sql语句在sqlplus中执行完全正常!!
这句话在sqlplus中报错如下:
ORA-00923: FROM keyword not found where expectedselect ChargingBill.ID,ChargingBill.starttime,ChargingBill.endtime,ChargingBill.movieId,ChargingBill.rnId,ChargingBill.flux,ChargingBill.stopReason,ChargingBill.stbId,nvl(movie.name,'未知影片'),nvl(stb.stbIP, 0) from ChargingBill,movie,stb where (ChargingBill.status='n' and ChargingBill.movieId=movie.id(+) and ChargingBill.stbId=stb.stbId(+))
这句话在sqlplus中执行完全正常
也不行啊。。难道jdbc不支持nvl?
WHERE A.ID='查询条件'
AND A.B_ID=B.ID AND A.C_ID=C.ID
AND A.B_ID=B.ID OR A.C_ID=C.ID