一则ORA-00905: missing keyword 原因： SQL Select * into from oracle中应该为create table NewTable as select

Dear 丶 2023-10-08 09:51 56阅读 0赞

原SQL中的语句

Select 
     a.*,QMZS1,QMZS2,b.YSYWF as PYSYWF,b.zdsfbzje, 
     case when a.YSYWF>isnull(b.YSYWF,0) then '大于' when a.YSYWF=isnull(b.YSYWF,0) then '等于' when a.YSYWF<isnull(b.YSYWF,0) then '小于' end as SFDB,  
     case when a.OfficeCode=b.BBPERSON or BBPERSON is null then '否' else '是' end as IsNext 
     into sTmp20191120200730520 From CheckBBList20191120200723541 a  
     left join  (
     Select TypeName, QMZS1,QMZS2, BBPERSON, BSDWMC,YSYWF,zdsfbzje  From BB_Content1 a 
     Inner Join BB_Type b on a.TypeID=b.GUID  Where left(BGRQ, 4)='2018' and BBPERSON is not null and BBState='报备完成' 
     ) b on case when a.BBType= '经济责任审计' then '报备其他' else a.BBType end =b.TypeName  and a.BSDWMC=b.BSDWMC

ORACLE中应该修改为：create table NewTable as select \* from ATable;

create table sTmp20191120200730520 
    as select 
    a.*,QMZS1,QMZS2,b.YSYWF as PYSYWF,b.zdsfbzje,
    case when a.YSYWF>NVL(b.YSYWF,0) then '大于' when a.YSYWF=NVL(b.YSYWF,0) then '等于' when a.YSYWF<NVL(b.YSYWF,0) then '小于' end as SFDB,  
    case when a.OfficeCode=b.BBPERSON or BBPERSON is null then '否' else '是' end as IsNext  
    From CheckBBList20191120200723541 a   
    left join  ( 
    Select TypeName, QMZS1,QMZS2, BBPERSON, BSDWMC,YSYWF,zdsfbzje  From BB_Content1 a 
    Inner Join BB_Type b on a.TypeID=b.GUID  Where SUBSTR(BGRQ,1, 4)='2018' and BBPERSON is not null and BBState='报备完成' 
    ) b on case when a.BBType= '经济责任审计' then '报备其他' else a.BBType end =b.TypeName  and a.BSDWMC=b.BSDWMC;

**注：**

**Sql Server**

select \* into new\_tab from tab;

**Oracle**

create table new\_tab as select \* from tab;

**在Oracle中select \* into from不可以使用，请用**create table NewTable as select \* from ATable;**代替该功能！！！**