[Altibase]개요번호 정렬

이런 방법을 생각해 보았다.

SELECT DISTINCT CUST_BD_VIRTUAL_ID,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 1), 3, '0'), '000') lv1,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 2), 3, '0'), '000') lv2,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 3), 3, '0'), '000') lv3,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 4), 3, '0'), '000') lv4,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 5), 3, '0'), '000') lv5,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 6), 3, '0'), '000') lv6,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 7), 3, '0'), '000') lv7,
                NVL(LPAD(REGEXP_SUBSTR(CUST_BD_VIRTUAL_ID, '[^.]+', 1, 8), 3, '0'), '000') lv8
      FROM CUST_BD_INFO
      order by LV1, LV2, LV3, LV4, LV5, LV6, LV7, LV8

=> 레벨별로 3자리씩 만들고 빈자리는 000으로 채워서 정렬

단점은 레벨별로 동적으로 쿼리를 생성해야 하겠지..