分页查询sql
select * from ( select page_.*, rownum rn from (select * from (
select r.rowid,
--select ROWIDTOCHAR(r.rowid) as rid,
r.patient_id,
p.name,
d.dept_code,
d.dept_name,
r.admission_date_time
from medrec.transfer r ,medrec.pat_master_index p,dept_dict d
where r.patient_id = p.patient_id and r.dept_stayed = d.dept_code
) where 1=1 order by admission_date_time desc) page_ where rownum <= 10 ) where rn > 0
ORA-01446: 无法使用distinct, group by 等子句从试图中选择rowid或采样
ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc
把rowid 改个名就可以了
rowid转json时又报错
org.codehaus.jackson.map.JsonMappingException: No serializer found for class java.io.ByteArrayInputStream and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationConfig.Feature.FAIL_ON_EMPTY_BEANS) ) (through reference chain: java.util.HashMap["rows"]->java.util.ArrayList[0]->org.springframework.util.LinkedCaseInsensitiveMap["RID"]->oracle.sql.ROWID["stream"])
把rowid转字符串
oracle函数(rowid值转换字符串)
SELECT ROWIDTOCHAR(rowid) FROM DUAL;
关键就是这句 select ROWIDTOCHAR(r.rowid) as rid