내가 본 스파게티 쿼리 레전드

[익명(115.21)]

select companySeq, locNo, bmsCode, contseq

   , levyYear || '-' || levyMonth as levyYearMonth

     , DONGCODE || '-' || FLRFLR || '-' || FLRZONE || '-' || FRRNO || '-' || URRNOSEQ as frrno

     , custCompany

     , custBrandname

   , nvl(max(amount1), 0) as amount1

   , nvl(max(uncolAmt1), 0) as uncolAmt1

   , nvl(max(interest1), 0) as interest1

   , nvl(max(sum1), 0) as sum1

   , nvl(max(amount2), 0) as amount2

   , nvl(max(uncolAmt2), 0) as uncolAmt2

   , nvl(max(interest2), 0) as interest2

   , nvl(max(sum2), 0) as sum2

   , nvl(max(amount3), 0) as amount3

   , nvl(max(uncolAmt3), 0) as uncolAmt3

   , nvl(max(interest3), 0) as interest3

   , nvl(max(amount3), 0) + nvl(max(uncolAmt3), 0) + nvl(max(interest3), 0) as sum3

   , nvl(max(amount4), 0) as amount4

       , nvl(max(uncolAmt4), 0) as uncolAmt4

   , nvl(max(interest4), 0) as interest4

   , nvl(max(amount4), 0) + nvl(max(uncolAmt4), 0) + nvl(max(interest4), 0) as sum4

       , nvl(max(uncolBalance), 0) as uncolBalance

from

(

select LR.companySeq as companySeq, LR.locNo as locNo, LR.bmsCode as bmsCode, LR.contseq

   , LR.levyYear, LR.levyMonth

     , LR.DONGCODE, LR.FLRFLR, LR.FLRZONE, LR.FRRNO, LR.URRNOSEQ

     , CUS.CUSTCOMPANY as custCompany

     , CUS.CUSTEPRESIDENT as custBrandname

   , LR.rentTMoney+rentVat as amount1

   , LR.rentMinApAmt as uncolAmt1

   , (f_day_delayamt(LR.COMPANYSEQ, LR.LOCNO, LR.BMSCODE, '5', '1', nvl(LR.RENTTMONEY+LR.RENTVAT, 0),

      LR.RENTYYMMDD, decode(LR.RENTIYYMMDD, '0000-00-00', LR.RENTYYMMDD, LR.RENTIYYMMDD))

      + f_day_delayamt(LR.COMPANYSEQ, LR.LOCNO, LR.BMSCODE, '5', '1', nvl(LR.RENTTMONEY+LR.RENTVAT, 0) - nvl((select sum(RENTRIMONEY)

            from lechrentrcpt

      where levyYear = '2010' and levyMonth = '05'

      and RENTRIYYMMDD <= '2010'||'-'||'06'||'-'||'07'  and contseq = LR.contseq

          ), 0),

        decode(LR.RENTIYYMMDD, '0000-00-00', LR.RENTYYMMDD, LR.RENTIYYMMDD), '2010'||'-'||'06'||'-'||'07')

      + LR.RENTMINAPDELAY

      + f_day_delayamt(LR.COMPANYSEQ, LR.LOCNO, LR.BMSCODE, '5', '1', nvl(LR.RENTMINAPAMT, 0),

        TO_CHAR(LAST_DAY(TO_DATE('2010'||'05', 'YYYYMM')), 'YYYY-MM-DD'),

        decode((select max(RENTRIYYMMDD) from lechrentrcpt

    where contseq = LR.CONTSEQ

      and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

      and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

    and rentrdelaymoney = (select RENTIDELAYMONEY from lechrent

       where contseq = LR.CONTSEQ

       and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

                                               and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       ))

    , NULL, '2010'||'-'||'06'||'-'||'07'

    , (select max(RENTRIYYMMDD) from lechrentrcpt

       where contseq = LR.CONTSEQ

         and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

         and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       and rentrdelaymoney = (select RENTIDELAYMONEY from lechrent

          where contseq = LR.CONTSEQ

          and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

          and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

          ))))) as interest1

   , ((f_day_delayamt(LR.COMPANYSEQ, LR.LOCNO, LR.BMSCODE, '5', '1', nvl(LR.RENTTMONEY+LR.RENTVAT, 0),

      LR.RENTYYMMDD, decode(LR.RENTIYYMMDD, '0000-00-00', LR.RENTYYMMDD, LR.RENTIYYMMDD))

      + f_day_delayamt(LR.COMPANYSEQ, LR.LOCNO, LR.BMSCODE, '5', '1', nvl(LR.RENTTMONEY+LR.RENTVAT, 0) - nvl((select sum(RENTRIMONEY)

            from lechrentrcpt

      where levyYear = '2010' and levyMonth = '05'

      and RENTRIYYMMDD <= '2010'||'-'||'06'||'-'||'07'  and contseq = LR.contseq

          ), 0),

        decode(LR.RENTIYYMMDD, '0000-00-00', LR.RENTYYMMDD, LR.RENTIYYMMDD), '2010'||'-'||'06'||'-'||'07')

      + LR.RENTMINAPDELAY

      + f_day_delayamt(LR.COMPANYSEQ, LR.LOCNO, LR.BMSCODE, '5', '1', nvl(LR.RENTMINAPAMT, 0),

        TO_CHAR(LAST_DAY(TO_DATE('2010'||'05', 'YYYYMM')), 'YYYY-MM-DD'),

        decode((select max(RENTRIYYMMDD) from lechrentrcpt

    where contseq = LR.CONTSEQ

      and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

      and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

    and rentrdelaymoney = (select RENTIDELAYMONEY from lechrent

       where contseq = LR.CONTSEQ

       and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

       and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       ))

    , NULL, '2010'||'-'||'06'||'-'||'07'

    , (select max(RENTRIYYMMDD) from lechrentrcpt

       where contseq = LR.CONTSEQ

         and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

         and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       and rentrdelaymoney = (select RENTIDELAYMONEY from lechrent

          where contseq = LR.CONTSEQ

          and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

          and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

          )))))

      + LR.RENTTMONEY

      + LR.RENTVAT

      + LR.RENTMINAPAMT) as sum1

   , 0 as amount2

   , 0 as uncolAmt2

   , 0 as interest2

   , 0 as sum2

       ,(

     select sum(RENTRIMONEY) from lechrentrcpt

    where levyYear = '2010' and levyMonth = '05'

  and RENTRIYYMMDD <= '2010'||'-'||'06'||'-'||'07' and contseq = LR.contseq

    ) as amount3

       , (

     select sum(RENTRIMONEY) from lechrentrcpt

      where RENTRIYYMMDD between '2010'||'-'||'06'||'-01' and '2010'||'-'||'06'||'-'||'07' and contseq = LR.contseq

      and levyMonth != '05'

    ) as uncolAmt3

   , (

     select sum(RENTRDELAYMONEY) from lechrentrcpt

      where RENTRIYYMMDD between '2010'||'-'||'06'||'-01' and '2010'||'-'||'06'||'-'||'07' and contseq = LR.contseq

) as interest3

   , 0 as sum3

   , 0 amount4

       , 0 as uncolAmt4

   , 0 as interest4

   , 0 as sum4

       , 0 as uncolBalance

from LECHRENT LR,

         LEBACUSTOMER CUS

where LR.CUSTCODE = CUS.CUSTCODE

  and LR.LEVYYEAR = '2010'

  and LR.LEVYMONTH = '05'

union

select LV.companySeq as companySeq, LV.locNo as locNo, LV.bmsCode as bmsCode, LV.contseq

   , LV.levyYear, LV.levyMonth

     , LV.DONGCODE, LV.FLRFLR, LV.FLRZONE, LV.FRRNO, LV.URRNOSEQ

     , CUS.CUSTCOMPANY as custCompany

     , CUS.CUSTEPRESIDENT as custBrandname

   , 0 as amount1

   , 0 as uncolAmt1

   , 0 as interest1

   , 0 as sum1

   , LV.SumMoney as amount2

   , LC.mccMinapamt as uncolAmt2

   , (f_day_delayamt(LC.COMPANYSEQ, LC.LOCNO, LC.BMSCODE, '4', '1', nvl(LC.MCCSUMMONEY, 0),

      LC.MCCYYMMDD, decode(LC.MCCIYYMMDD, '0000-00-00', LC.MCCYYMMDD, LC.MCCIYYMMDD))

      + f_day_delayamt(LC.COMPANYSEQ, LC.LOCNO, LC.BMSCODE, '4', '1', nvl(LC.MCCSUMMONEY, 0) - nvl((select sum(CCRIMONEY)

          from lechtenantrcpt

    where levyYear = '2010' and levyMonth = '05'

    and CCRIYYMMDD <= '2010'||'-'||'06'||'-'||'07' and contseq = LC.contseq

        ), 0),

        decode(LC.MCCIYYMMDD, '0000-00-00', LC.MCCYYMMDD, LC.MCCIYYMMDD), '2010'||'-'||'06'||'-'||'07')

      + LC.MCCMINAPDELAY

      + f_day_delayamt(LC.COMPANYSEQ, LC.LOCNO, LC.BMSCODE, '4', '1', nvl(LC.MCCMINAPAMT, 0),

        TO_CHAR(LAST_DAY(TO_DATE('2010'||'05', 'YYYYMM')), 'YYYY-MM-DD'),

        decode((select max(CCRIYYMMDD) from lechtenantrcpt

    where contseq = LC.CONTSEQ

      and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

      and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

    and CCRBASEDELAY = (select MCCDELAYMONEY from lechmthtenantcost

       where contseq = LC.CONTSEQ

       and onwercode = LC.ONWERCODE

       and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

                                               and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       ))

    , NULL, '2010'||'-'||'06'||'-'||'07'

    , (select max(CCRIYYMMDD) from lechtenantrcpt

       where contseq = LC.CONTSEQ

         and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

         and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       and CCRBASEDELAY = (select MCCDELAYMONEY from lechmthtenantcost

          where contseq = LC.CONTSEQ

          and onwercode = LC.ONWERCODE

          and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

          and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

          ))))) as interest2

   , ((f_day_delayamt(LC.COMPANYSEQ, LC.LOCNO, LC.BMSCODE, '4', '1', nvl(LC.MCCSUMMONEY, 0),

      LC.MCCYYMMDD, decode(LC.MCCIYYMMDD, '0000-00-00', LC.MCCYYMMDD, LC.MCCIYYMMDD))

      + f_day_delayamt(LC.COMPANYSEQ, LC.LOCNO, LC.BMSCODE, '4', '1', nvl(LC.MCCSUMMONEY, 0) - nvl((select sum(CCRIMONEY)

          from lechtenantrcpt

    where levyYear = '2010' and levyMonth = '05'

    and CCRIYYMMDD <= '2010'||'-'||'06'||'-'||'07' and contseq = LC.contseq

        ), 0),

        decode(LC.MCCIYYMMDD, '0000-00-00', LC.MCCYYMMDD, LC.MCCIYYMMDD), '2010'||'-'||'06'||'-'||'07')

      + LC.MCCMINAPDELAY

      + f_day_delayamt(LC.COMPANYSEQ, LC.LOCNO, LC.BMSCODE, '4', '1', nvl(LC.MCCMINAPAMT, 0),

        TO_CHAR(LAST_DAY(TO_DATE('2010'||'05', 'YYYYMM')), 'YYYY-MM-DD'),

        decode((select max(CCRIYYMMDD) from lechtenantrcpt

    where contseq = LC.CONTSEQ

      and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

      and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

    and CCRBASEDELAY = (select MCCDELAYMONEY from lechmthtenantcost

       where contseq = LC.CONTSEQ

       and onwercode = LC.ONWERCODE

       and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

                                               and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       ))

    , NULL, '2010'||'-'||'06'||'-'||'07'

    , (select max(CCRIYYMMDD) from lechtenantrcpt

       where contseq = LC.CONTSEQ

         and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

         and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

       and CCRBASEDELAY = (select MCCDELAYMONEY from lechmthtenantcost

          where contseq = LC.CONTSEQ

          and onwercode = LC.ONWERCODE

          and levyyear = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 1, 4)

          and levymonth = substr(TO_CHAR(ADD_MONTHS(TO_DATE('2010'||'05','YYYYMM'), -1), 'YYYY-MM'), 6, 2)

          )))))

      + LC.MCCSUMMONEY

      + LC.MCCMINAPAMT) as sum2

   , 0 as amount3

   , 0 as uncolAmt3

   , 0 as interest3

   , 0 as sum3

   , (

     select sum(ccrbasemoney) from lechtenantrcpt

    where levyYear = '2010' and levyMonth = '05'

      and CCRIYYMMDD <= '2010'||'-'||'06'||'-'||'07' and contseq = LC.contseq

    ) as amount4

       , (

     select sum(ccrbasemoney) from lechtenantrcpt

      where CCRIYYMMDD between '2010'||'-'||'06'||'-01' and '2010'||'-'||'06'||'-'||'07' and contseq = LC.contseq

      and levyMonth != '05'

    ) as uncolAmt4

   , (

     select sum(ccrbasedelay) from lechtenantrcpt

      where CCRIYYMMDD between '2010'||'-'||'06'||'-01' and '2010'||'-'||'06'||'-'||'07' and contseq = LC.contseq

) as interest4

   , 0 as sum4

       , 0 as uncolBalance

from LECHMTHTENANTCOSTVIEW LV,

LECHMTHTENANTCOST LC,

LEBACUSTOMER CUS

where LV.ONWERCODE = LC.ONWERCODE

  and LV.CONTSEQ = LC.CONTSEQ

  and LV.LEVYYEAR = LC.LEVYYEAR

  and LV.LEVYMONTH = LC.LEVYMONTH

  and LC.CUSTCODE = CUS.CUSTCODE

    and LV.LEVYYEAR = '2010'

    and LV.LEVYMONTH = '05'

)

group by companySeq, locNo, bmsCode, contseq, levyYear, levyMonth, DONGCODE, FLRFLR, FLRZONE, FRRNO, URRNOSEQ, custCompany, custBrandname

order by dongcode, flrflr, flrzone, frrno, urrnoseq, frrno, levyyear, levymonth, contseq

이게 사내에서 쓰는 프로그램 정보 하나 조회하는 쿼리임

[익명(49.171)]

현업은 뭔가 다르긴하구나...

[오토충(211.193)]

ㅋㅋㅋㅋ

[익명(223.62)]

시작한지 얼마 안된 백수인데 이렇게 돼있으면 알아볼수나 있음? db 공부하면 이렇게 좃같이 복잡한거 다루게되는거냐?

[익명(115.21)]

아니 제목처럼 스파게티 코드임.. 전담 선임이 병신같이 짜놔서 한없이 길어지는거

[익명(106.102)]

CTE도 없이 저 지랄을 해놨다고?

[익명(223.39)]

바로 해고

[익명(211.219)]

시발

[익명(211.219)]

대체 얼마나 많은걸 가져온다고 저지랄이 나냐 국비들을때 강사도 현업가면 4~5줄은 기본이라는데 저건 줄일수있는 방법 있음?

[익명(117.111)]

Sql쿼리는 단순하게 하고 로직상에서 처리해야 뭐가 보이지 시발