안뿌꾸
80
2019-07-12 18:17:55
2
118

일관성 없는 데이터 유형 CHAR이 필요하지만 NUMBER 임



SELECT
       CASE WHEN GROUPING(cust_no) = 1 AND GROUPING(acco_no) = 1 THEN '총합계'
          WHEN GROUPING(cust_no) = 0 AND GROUPING(acco_no) =1 THEN '계좌합계'
          ELSE 'cust_no' END cust_no


     , CASE GROUPING(acco_no) WHEN 1 THEN COUNT(acco_no)||'' ELSE acco_no END acco_no


     , sum(bal_loan) bal_loan
     , sum(am_loan_int) am_loan_int
     , sum(am_loan_i_dly) am_loan_i_dly


  FROM (SELECT lc.cust_no cust_no
       , ms.acco_no acco_no
       , ms.bal_loan bal_loan
       , lc.am_loan_int am_loan_int
       , lc.am_loan_i_dly am_loan_i_dly
      FROM a_savems ms, l_loancalc lc
     WHERE lc.cust_no IN ((SELECT cust_no
                           FROM (SELECT cust_no
                                      , id_loan
                                      , count(*)
                                   FROM a_savems
                                  WHERE id_loan NOT IN ('GJ','##')
                                    AND basic_date = '20190331'
                                  GROUP BY cust_no, id_loan
                                 )
                            GROUP BY cust_no
                            HAVING count(*) > 2
                            )
                           )
       AND ms.aclg_no = lc.aclg_no
       AND ms.basic_date = lc.proc_date
       AND ms.basic_date = '20190331'
     ORDER BY ms.cust_no
 )
GROUP BY ROLLUP (cust_no, acco_no);


저런 오류가 떠서 case문 else의 cust_no을 'cust_no'으로 주어서 출력은 성공했는데

결과값이 원하는 값이 안나오네요 


원하는 값

고객번호계좌번호잔액이자연체료
1000063618128-01-13-0263881708377234141113058927952434
1000063618128-01-44-000451829673000
1000063618128-01-44-0004519458880000
1000063618128-01-44-00236901397500
1000063618128-01-73-00000193164320026541848480
1000063618128-01-76-049123330000000045610679647608
1000063618128-90-44-000012412264000
계좌합계        7건13298313811251404702928000042
1000070223128-01-44-0026981173565300
1000070223128-01-44-002724616929200
1000070223128-01-44-0035700187617100
1000070223128-01-73-00000351000000002935109550
1000070223128-02-13-0165986267998522513613262493973075951
1000070223128-02-76-00953809999840501609955663163454
계좌합계        6건378375039132647928673973239405
총합계       13건511358177245161975694901239447



0
0
  • 답변 2

  • 안뿌꾸
    80
    2019-07-12 18:18:28 작성 2019-07-12 18:18:52 수정됨

    // 내가 나온 값


    cust_no 128-01-13-0263881 708377234 141113058 927952434
    cust_no 128-01-44-0004518 296730 0 0
    cust_no 128-01-44-0004519 4588800 0 0
    cust_no 128-01-44-0023690 13975 0 0
    cust_no 128-01-76-0491233 300000000 456106796 47608
    cust_no 128-90-44-0000124 122640 0 0
    계좌합계 6 1013399379 597219854 928000042
    cust_no 128-01-44-0026981 1735653 0 0
    cust_no 128-01-44-0027246 169292 0 0
    cust_no 128-01-44-0035700 1876171 0 0
    cust_no 128-01-73-0000035 100000000 293510955 0
    cust_no 128-02-13-0165986 2679985225 1361326249 3973075951
    cust_no 128-02-76-0095380 999984050 1609955663 163454
    계좌합계 6 3783750391 3264792867 3973239405
    총합계 12 4797149770 3862012721 4901239447


    0
  • XeO3
    428
    2019-07-12 22:00:31

    cust_no를 TO_CHAR (number)로 변환해주면 될듯합니다.


    SELECT
           CASE WHEN GROUPING(cust_no) = 1 AND GROUPING(acco_no) = 1 THEN '총합계'
              WHEN GROUPING(cust_no) = 0 AND GROUPING(acco_no) =1 THEN '계좌합계'
              ELSE TO_CHAR(cust_no) END cust_no
    --이하생략



    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.