Home>

I want the following output result, but it comes out when ‘DECODE’ is not recognized as a built-in function name. What is the best way to avoid it?
T_CD SUM (NBIK)
41 120
45 80
47 960
41 80
43 980
49 79

SELECT
 T_CD, SUM (NBIK)
FROM FROM
(SELECT E_D, T_CD, DECODE (T_KB, '0', (NBIK), (NBIK) * -1) AS NBIK
 FROM R_MYI
 WHERE E_D = '20150721'
 AND E_FLG = '9'
 AND E_FLG = '0'
)
group by T_CD
order by T_CD

I rewrote it in case, but it is displayed if there is an inappropriate syntax near ‘)’.

SELECT T_CD, SUM (NBIK)
 FROM FROM
(SELECT
 E_D, T_CD,
 (CASE
  WHEN T_KB = 0 THEN NBIK
  ELSE (NBIK) * -1
  END
) AS NBIK
 FROM R_MYI
 WHERE E_D = '20150721'
 AND E_FRG = '9'
 AND E_FLG = '0'
)
 group by T_CD
 order by T_CD


environment
A5: SQL Mk-2

  • Answer # 1

    Please use CASE.

    By the wayNvlIs COALESCE.

    If you modify the SQL, it will be as follows.

    select t_cd, sum (nbik * case when t_kb = 0 then 1 else -1 end)
    from r_myi
    where e_d = '20150721'
      and e_flg = '9'
      and e_flg = '0'
    group by t_cd
    order by t_cd


    e_flg = '9' and e_flg = '0'Is inconsistent, so please review the description.

  • Answer # 2

    DECODE functionIsSQL ServerCannot be used with.
    CASE expressionPlease rewrite with.

    decode case convert