[답변] 쿼리를 줄여봤습니다. 0 1 3,916

by 마농 group by decode [2008.06.18 13:42:51]


SELECT b.c_code
     , b.dept_code
     , f_tec_getdeptname('2000',dept_code) dept_code_2000
     , f_tec_getdeptname('3000',dept_code) dept_code_3000
     , COUNT(*) cnt_total
     , NVL(SUM(a.summ_char_amou),0) amt_total
     , COUNT(DECODE(a.mana_proc_stat,'800',1)) cnt_sign
     , NVL(SUM(DECODE(a.mana_proc_stat,'800',a.summ_char_amou)),0) amt_sign
     , COUNT(DECODE(a.mana_proc_stat,'200',1)) cnt_note
     , NVL(SUM(DECODE(a.mana_proc_stat,'200',a.summ_char_amou)),0) amt_note
     , COUNT(DECODE(a.mana_proc_stat,'800',1,'200',1)) cnt_sign_note
     , NVL(SUM(DECODE(a.mana_proc_stat,'800',a.summ_char_amou,'200',a.summ_char_amou)),0) amt_sign_note
     , COUNT(DECODE(b.yn_acc,'Y',1)) cnt_acc
     , NVL(SUM(DECODE(b.yn_acc,'Y',a.summ_char_amou)),0) amt_acc
     , COUNT(CASE WHEN a.mana_proc_stat IN ('100','550','900') THEN 1 END) cnt_nosign
     , NVL(SUM(CASE WHEN a.mana_proc_stat IN ('100','550','900') THEN a.summ_char_amou END),0) amt_nosign
  FROM tex_master a
     , tex_pre_master b
 WHERE a.mana_bill_numb = b.mana_bill_numb
--   AND a.head_docu_date >= '20080101'
--   AND a.head_docu_date <= '20080131'
 GROUP BY b.c_code, b.dept_code
 ORDER BY dept_code

by 스물일곱 [2008.06.18 14:11:38]
마농님 감사합니다.
그런데..지금. 이렇게 짧아진 코드...막..이해가 되지 않네요...ㅎㅎ;;
오라클 정말..어렵네요....SELECT, DELETE, UPDATE만 할줄 알았는데..힘드네요..오라클.......
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입