(select code,name,archives_datum_type,create_User,
to_char(create_Date,'yyyy-mm-dd') as create_Date,modify_user,
to_char(modify_date,'yyyy-mm-dd') as modify_date,order_num,modify_Flag
from t_archives_datum where modify_Flag<>'D')
union
(select '代码' as code,'资料名称' as name,'资料类别' as archives_datum_type,
'创建人' as create_User,'创建日期' as create_Date,'修改人' as modify_user,
'修改日期' as modify_date,'排序码' as order_num,'操作标志' as modify_Flag
from t_archives_datum)
order by to_number(order_num) asc
---------------------------------------------
问题描述:
java.sql.SQLException: ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目
to_char(create_Date,'yyyy-mm-dd') as create_Date,modify_user,
to_char(modify_date,'yyyy-mm-dd') as modify_date,order_num,modify_Flag
from t_archives_datum where modify_Flag<>'D')
union
(select '代码' as code,'资料名称' as name,'资料类别' as archives_datum_type,
'创建人' as create_User,'创建日期' as create_Date,'修改人' as modify_user,
'修改日期' as modify_date,'排序码' as order_num,'操作标志' as modify_Flag
from t_archives_datum)
order by to_number(order_num) asc
---------------------------------------------
问题描述:
java.sql.SQLException: ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目
to_char(create_Date,'yyyy-mm-dd') as create_Date,modify_user,
to_char(modify_date,'yyyy-mm-dd') as modify_date,order_num,modify_Flag
from t_archives_datum where modify_Flag<>'D'
union
select '代码' as code,'资料名称' as name,'资料类别' as archives_datum_type,
'创建人' as create_User,'创建日期' as create_Date,'修改人' as modify_user,
'修改日期' as modify_date,'排序码' as order_num,'操作标志' as modify_Flag
from t_archives_datum
order by order_num asc
java.sql.SQLException: ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目
不是提示你Order by 这边有问题么;把to_number()去掉就好了啊
(select code,name,archives_datum_type,create_User,
to_char(create_Date,'yyyy-mm-dd') as create_Date,modify_user,
to_char(modify_date,'yyyy-mm-dd') as modify_date,
to_number(order_num),modify_Flag
from t_archives_datum where modify_Flag<>'D')
union
(select '代码' as code,'资料名称' as name,'资料类别' as archives_datum_type,
'创建人' as create_User,'创建日期' as create_Date,'修改人' as modify_user,
'修改日期' as modify_date,to_number('排序码') as order_num,'操作标志' as modify_Flag
from t_archives_datum)
order by order_num asc
select * from (
(select code,name,archives_datum_type,create_User,
to_char(create_Date,'yyyy-mm-dd') as create_Date,modify_user,
to_char(modify_date,'yyyy-mm-dd') as modify_date,order_num,modify_Flag
from t_archives_datum where modify_Flag<>'D')
union all
(select '代码' as code,'资料名称' as name,'资料类别' as archives_datum_type,
'创建人' as create_User,'创建日期' as create_Date,'修改人' as modify_user,
'修改日期' as modify_date,'排序码' as order_num,'操作标志' as modify_Flag
from t_archives_datum)
) TAB_A
order by to_number(order_num) asc
------------------------------------------
代码 资料名称 类别 创建人 创建日期 修改人 修改日期 排序码 修改标志
10090 行政处罚书及相关材料 103 廖×× 2006-08-18 莫×× 2006-08-23 90 U
------------------------------------------
而且客户要求可以自己更改排序码,使各个行能按自己的意愿排序,所以order_num必须要用to_number转换,然后才能order by
后来发觉两个表union后不能直接就order by,像 Marguess(留候),xiaoxiao1984(笨猫儿^_^) 这样总是报错查不出数据。wiler(@_@)的思路是正确的,但是我调了很久也查不出数据。最后我唯有放弃了用union方法产生一个表头的方法,直接用下面的方法产生表头
public ArrayList getRowName(ArrayList data){
ArrayList alReturn = new ArrayList();
ArrayList alRowName = new ArrayList();
alRowName = data;
alRowName.add("代码");
alRowName.add("资料名称");
alRowName.add("类别");
...............;
alRowName.add("修改日期");
alRowName.add("排序码");
alRowName.add("修改标志");
alReturn.add(0,alRowName);
return alReturn;
}
很感谢大家,是大家启发了我用第二种方法实现表头的方法。