个语句用oracle怎么写

[ydhxtf]

SELECT id=identity(int,1,1),* into #temp_audits FROM audits ORDER BY time

最好能一条实现，要不多条也可以

[ydhxtf]

现在问题是这样，好像×里面包括零长度的字段，我用其他表是可以成功的，audits会报这个错，那该怎么解决呢

SQL> create global temporary table temp_audits
2 on commit preserve rows
3 as
4 select * from audits;
select * from audits
*
ERROR at line 4:
ORA-01723: zero-length columns are not allowed

[cool_hnu]

create temporary table temp_audits 
as 
(select * , row_number() over(order by time) id from audits)

[ydhxtf]

[Quote=引用 8 楼 ydhxtf 的回复:]
现在问题是这样，好像×里面包括零长度的字段，我用其他表是可以成功的，audits会报这个错，那该怎么解决呢

SQL> create global temporary table temp_audits
2  on commit preserve rows
3  as
4  select * from audits;
select * from audits
*
ERROR at line 4:
ORA-01723: zero-length columns are not allowed
[/Quote]
我找到原因是因为audits这张表有一个字段定义的时候用NULL，那么如何解决啊

[ydhxtf]

我的目的就是想给audits这张表按时间排序然后增加一个id列放在临时表中

[dawugui]

[Quote=引用楼主 ydhxtf 的帖子:]
SELECT id=identity(int,1,1),* into #temp_audits FROM audits ORDER BY time

最好能一条实现，要不多条也可以
[/Quote]

select * , row_number() over(order by time) id from audits

[ydhxtf]

谢谢ls的，不过怎样把select的内容放进新的临时表呢

[Andy__Huang]

oracle的临时表这样用的
create temporary table tb
as
SELECT * FROM audits ORDER BY time
on commit presever rows;
或
create temporary table tb
as
SELECT * FROM audits ORDER BY time
on commit delete rows;

[ydhxtf]

那么组合起来是不是可以这样写
create temporary table temp_audits
as
select * , row_number() over(order by time) id from audits

[cosio]

[Quote=引用 5 楼 ydhxtf 的回复:]
那么组合起来是不是可以这样写
create temporary table temp_audits
as
select * , row_number() over(order by time) id from audits
[/Quote]

OK的

[ydhxtf]

好像语法有问题啊

SQL> create global temporary table temp_audits
2 as
3 select *,row_number() over(order by time) id from audits;
select *,row_number() over(order by time) id from audits
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

[fxhyhyfzj]

路过学习中