SQL Function

# 세로(ROW)로 출력되는 데이터를 가로(COLUMN)형태로 출력하기

WITH before_v as    --선행 작업 목록
( select table_id, 
         job_id, 
      CONDITION, 
     -- rank() over ( partition by CONDITION order by CONDITION) rank  /* CONDITION 순번*/
  ORDER_ rank 
  from emuser.DEF_LNKI_P ),
 after_v as             --후행 작업 목록
( select table_id, 
         job_id, 
      CONDITION
      --rank() over ( partition by CONDITION order by CONDITION) rank  /* CONDITION 순번 */
   --ORDER_ rank  
  from emuser.DEF_LNKO_P )  
select table_id
      ,job_id
   ,application
   ,group_name
   ,job_name
   ,description
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 1) as one_before
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 2) as two_before
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 3) as three_before
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 4) as four_before
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 5) as five_before
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 6) as six_before
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 7) as seven_before
   ,(select condition from before_v where table_id = a.table_id and job_id = a.job_id and rank = 8) as eight_before
   ,(select condition from after_v where table_id = a.table_id and job_id = a.job_id ) as one_after    
from emuser.def_job a

# DATE TYPE 연산 및 출력 SQL

/* 
   DATE 숫자끼리의 연산 결과를 보기 좋게 출력 하는 SQL 
   (DATE  연산의 결과는 1일 = 1 로 표시됨) 
*/

-- # TYPE 1: DATE 연산 출력 
SELECT TO_CHAR(FLOOR(  elapsed*24),'FM09')||':'|| 
       TO_CHAR(FLOOR(( elapsed*24 - FLOOR(elapsed*24))*60),'FM09')||':'|| 
       TO_CHAR(FLOOR(((elapsed*24 - FLOOR(elapsed*24))*60 - FLOOR((elapsed*24 - FLOOR(elapsed*24))*60))*60),'FM09') elapsed_time 
FROM  ( 
        SELECT (TO_DATE('20050120094512', 'YYYYMMDD hh24:mi:ss') -  TO_DATE('20050120000000', 'YYYYMMDD hh24:mi:ss')) elapsed 
        FROM dual 
      );

 

-- # TYPE 2: DATE 연산 출력      
select TO_CHAR (FLOOR    (elapsed * 24       ),        'FM09') || ':' || 
       to_char (floor(mod(elapsed * 24,     1) * 60 ), 'FM09') || ':' || 
       to_char (floor(mod(elapsed * 24 * 60,1) * 60 ), 'FM09') 
from ( 
        SELECT (TO_DATE('20050120093912', 'YYYYMMDD hh24:mi:ss') -  TO_DATE('20050120000000', 'YYYYMMDD hh24:mi:ss')) elapsed 
        FROM dual 
     );

 

-- # TYPE 2-1: DATE 연산 출력 (숫자로 시간 알아내기)     
select TO_CHAR (FLOOR    (elapsed * 24       ),        'FM09') || ':' || 
       to_char (floor(mod(elapsed * 24,     1) * 60 ), 'FM09') || ':' || 
       to_char (floor(mod(elapsed * 24 * 60,1) * 60 ), 'FM09') 
from ( 
        select '0.0181736111111111' elapsed from dual 
     );

 

-- # TYPE ETC: DATE 연산 출력 (시간으로 원래숫자 알아내기) 
select substrb(o_time,1,2) / 24            + 
       substrb(o_time,4,2) / 24 / 60       + 
       substrb(o_time,7,2) / 24 / 60 / 60 
from ( select '00:26:10' o_time from dual );

# DATE 구하기

SELECT YMD FROM (SELECT TO_CHAR(SYSDATE,'YYYY')||B.NO2||A.NO2 YMD,               TO_CHAR(LAST_DAY(TO_DATE(TO_CHAR(SYSDATE,'YYYY')||B.NO2,'YYYYMM')),'YYYYMMDD') AS LAST_YMD                FROM  COPY_T A, COPY_T B               WHERE  B.NO2 <= '12'                 AND  A.NO2 <= '31') WHERE YMD <= LAST_YMD