语句一,进行行列转换时 ,当运行下面语句时报错“ORA-01467:分类(sort)关键字过长”,这主要是item_value这个字段里是字符类型,可能排序比较时占用过多内存了,这种情况可以将MAX()函数里的值先转换为 整数,0或1 ,将decode移动到max()里面,具体看语句二,或者用语句三的方式转换。
语句一:约20个字段的结果集
select decode(MAX(case when item_id=144 then item_value else null end) ,'JAVA',1,0)EDGE_STATUS,
decode( MAX(case when item_id=146 then item_value else null end) ,'A',1,0)FLASH_STATUS,
MAX(case when item_id=150 then item_value else null end) BARRECOG_STATUS,
..........
DECODE(MAX(case when item_id=154 then item_value else null end),'B',0,1) MOBILEEMAIL_STATUS
from STATIC_INFO_CHILD b, STATIC_INFO a
where a.MODEL_PARAM_ID = b.MODEL_PARAM_ID
group by manu_id ,model_id,sw_version
语句二:约20个字段的结果集
select manu_id,model_id,sw_version FW_VERSION,
MAX(case when item_id=136 then nvl(substr(item_value,1,10),'0000-00-00') else null end) LAUNCH_DATE,
MAX(case when item_id=157 then decode(UPPER(item_value),'JAVA',1,0) else null end) java_avail,
。。。。。。..。。。。
MAX(case when item_id=138 then DECODE(UPPER(item_value),'YES',1,0) else null end) PIM_AVAIL,
MAX(case when item_id=139 then DECODE(UPPER(item_value),'YES',1,0) else null end) DM_AVAIL,
MAX(case when item_id=55 then 0 else null end) CAM_AVAIL,
MAX(case when item_id=8 then DECODE(UPPER(item_value),'YES',1,0) else null end) IS_COLOR_SCR,
from STATIC_INFO_CHILD b, STATIC_INFO a
where a.MODEL_PARAM_ID = b.MODEL_PARAM_ID
group by manu_id ,model_id,sw_version
语句三:(利用表关联方式 )
SELECT t.fw_version_id,t0.LAUNCH_DATE,t1.WAP_AVAIL,t2.GPRS_AVAIL, ........t6.CAM_AVAIL,t7.IS_COLOR_SC.....,
from (select distinct fw_version_id from dmio.dmi_static_info) t,
(select fw_version_id, NVL(a.attribute_value,'00000000')LAUNCH_DATE from dmio.dmi_static_info a where a.attribute_id=136 and length(a.attribute_value)<=10) t0,
(select fw_version_id, decode(upper(a.attribute_value),'YES',1,'NO',0,0)WAP_AVAIL from dmio.dmi_static_info a where a.attribute_id=101) t1,
...........
(select fw_version_id, decode(upper(a.attribute_value),'NO',0,'RELOAD',2,'DOWNLOAD',3,0)STREAMINGTV_STATUS from dmio.dmi_static_info a where a.attribute_id=149) t20
WHERE t.fw_version_id=t0.fw_version_id(+)
and t.fw_version_id=t1.fw_version_id(+)
and t.fw_version_id=t2.fw_version_id(+)
and t.fw_version_id=t3.fw_version_id(+)
and t.fw_version_id=t4.fw_version_id(+)
..........
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/297293/viewspace-557231/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/297293/viewspace-557231/