하다보면. A테이블의 1개 당 B테이블의 전부를 Join 할 필요가 있다.
( ex 1월 부터 12월 까지의 월 테이블에 매출 정보를 Join 해야 할 경우 )
이때는 Cross Join 을 사용하면 된다.
Join 명명 없이 그냥 Old Style로 , 로 테이블을 엮으면 앞 테이블 기준으로 뒤 테이블의 데이터를 묶어 준다.
select mon,locode, replace(substr(mon,1,1),’0′,”)||substr(mon,2,1) as months
from (
SELECT * FROM
(
SELECT ’01’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’02’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’03’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’04’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’05’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’06’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’07’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’08’ AS mon FROM sysibm.sysdummy1 union all
SELECT ’09’ AS mon FROM sysibm.sysdummy1 union ALL
SELECT ’10’ AS mon FROM sysibm.sysdummy1 union ALL
SELECT ’11’ AS mon FROM sysibm.sysdummy1 union ALL
SELECT ’12’ AS mon FROM sysibm.sysdummy1
) ia,
( SELECT lo_code AS locode FROM POS_ASP_USER_COMPNO WHERE ID_CODE = ‘dadafc01’ ) ib
ORDER BY ib.locode, ia.mon
) a
이렇게 처리..
