오라클에 날린 쿼리
SELECT a.sysid
,NVL(MIN(DECODE(b.itemname, 'ABI', b.value)), 0) ABI
,NVL(MIN(DECODE(b.itemname, 'AOS', b.value)), 0) AOS
,NVL(MIN(DECODE(b.itemname, 'ASSEMBLYMATERIAL', b.value)), 0) ASSEMBLYMATERIAL
,NVL(MIN(DECODE(b.itemname, 'CARRIER', b.value)), 0) CARRIER
,NVL(MIN(DECODE(b.itemname, 'DIE1QTYOFWAFERTOORIGIN', b.value)), 0) DIE1QTYOFWAFERTOORIGIN
,NVL(MIN(DECODE(b.itemname, 'DIE1QTYOFWAFERTOUSE', b.value)), 0) DIE1QTYOFWAFERTOUSE
,NVL(MIN(DECODE(b.itemname, 'DIE2QTYOFWAFERTOORIGIN', b.value)), 0) DIE2QTYOFWAFERTOORIGIN
,NVL(MIN(DECODE(b.itemname, 'DIE2QTYOFWAFERTOUSE', b.value)), 0) DIE2QTYOFWAFERTOUSE
,NVL(MIN(DECODE(b.itemname, 'DIEBANK', b.value)), 0) DIEBANK
,NVL(MIN(DECODE(b.itemname, 'DIEREVISION', b.value)), 0) DIEREVISION
,NVL(MIN(DECODE(b.itemname, 'DIESIZEX', b.value)), 0) DIESIZEX
,NVL(MIN(DECODE(b.itemname, 'DIESIZEY', b.value)), 0) DIESIZEY
,NVL(MIN(DECODE(b.itemname, 'DROPSHIP', b.value)), 0) DROPSHIP
,NVL(MIN(DECODE(b.itemname, 'FABCODE', b.value)), 0) FABCODE
,NVL(MIN(DECODE(b.itemname, 'FG_MATERIAL_ID', b.value)), 0) FG_MATERIAL_ID
,NVL(MIN(DECODE(b.itemname, 'FLOWNUMBER', b.value)), 0) FLOWNUMBER
,NVL(MIN(DECODE(b.itemname, 'LEADFRAME', b.value)), 0) LEADFRAME
,NVL(MIN(DECODE(b.itemname, 'LEADFRAMETYPE', b.value)), 0) LEADFRAMETYPE
,NVL(MIN(DECODE(b.itemname, 'MARKCODE', b.value)), 0) MARKCODE
,NVL(MIN(DECODE(b.itemname, 'MARKDEVICE', b.value)), 0) MARKDEVICE
,NVL(MIN(DECODE(b.itemname, 'MARKINSTRUCT', b.value)), 0) MARKINSTRUCT
,NVL(MIN(DECODE(b.itemname, 'MRL', b.value)), 0) MRL
,NVL(MIN(DECODE(b.itemname, 'PACKAGELEADS', b.value)), 0) PACKAGELEADS
,NVL(MIN(DECODE(b.itemname, 'PACKAGEMARK', b.value)), 0) PACKAGEMARK
,NVL(MIN(DECODE(b.itemname, 'PKEG', b.value)), 0) PKEG
,NVL(MIN(DECODE(b.itemname, 'REV', b.value)), 0) REV
,NVL(MIN(DECODE(b.itemname, 'SECTION', b.value)), 0) SECTION
,NVL(MIN(DECODE(b.itemname, 'SLNUMBER', b.value)), 0) SLNUMBER
,NVL(MIN(DECODE(b.itemname, 'SPECIALINSTRUCTION', b.value)), 0) SPECIALINSTRUCTION
,NVL(MIN(DECODE(b.itemname, 'SPEED', b.value)), 0) SPEED
,NVL(MIN(DECODE(b.itemname, 'SYMBOLCODE', b.value)), 0) SYMBOLCODE
,NVL(MIN(DECODE(b.itemname, 'TEST', b.value)), 0) TEST
,NVL(MIN(DECODE(b.itemname, 'THICKNESSRQ', b.value)), 0) THICKNESSRQ
,NVL(MIN(DECODE(b.itemname, 'TOPBRANDLINE1', b.value)), 0) TOPBRANDLINE1
,NVL(MIN(DECODE(b.itemname, 'TOPBRANDLINE2', b.value)), 0) TOPBRANDLINE2
,NVL(MIN(DECODE(b.itemname, 'TOPBRANDLINE3', b.value)), 0) TOPBRANDLINE3
,NVL(MIN(DECODE(b.itemname, 'TOPBRANDLINE4', b.value)), 0) TOPBRANDLINE4
,NVL(MIN(DECODE(b.itemname, 'TOPBRANDLINE5', b.value)), 0) TOPBRANDLINE5
,NVL(MIN(DECODE(b.itemname, 'UNITPRICE', b.value)), 0) UNITPRICE
,NVL(MIN(DECODE(b.itemname, 'WAFERS', b.value)), 0) WAFERS
,NVL(MIN(DECODE(b.itemname, 'WAFERSIZE', b.value)), 0) WAFERSIZE
FROM awsappoassemblyinfo a, awsappoassemblyinfoext b
WHERE a.sysid = b.parentref
GROUP BY a.sysid
에러메시지
ORA-01467: sort key too long
01467. 00000 - "sort key too long"
*Cause:
*Action:
오라클 버전 : Oracle9i Enterprise Edition Release 9.2.0.7.0
운영체제 : window xp
제가 테스트해본결과 과다한 MIN함수사용 때문이라는 것을 확인해보았으나
MIN함수는 꼭 써야 하는 것이기에 저 에러메시지에 해결방법을 알고 싶습니다.
오라클을 회사서버에서 돌리고 있기에 버퍼크기는 8K에서 더 크게 할수가 없습니다.
좀 늦었지만 새해복 많이 받으세요~