How to fix ORA-00905 missing keyword with case expression

Question

I have problem with case in the line I marked it below.

I need it to be shown like

debits | credits | total debits | total credits

The SQL query is:

SELECT cv_amount,
       case when cv_amount > 0 then cv_amount else 0 end as debits, 
       case when cv_amount < 0 then cv_amount*-1 else 0 end as credits,
       sum(case when cv_amount > 0 then cv_amount else 0)--- ERROR SHOWEN HERE--- end as d, 
       sum(case when cv_amount < 0 then cv_amount*-1 else 0) end as c
FROM dof
where currency_code = 368 
  AND to_DATE('05/05/19', 'DD/MM/YY') and to_DATE('05/05/19', 'DD/MM/YY')
group by cv_amount

And **which** exact error is shown? What have you tried to solve it? — Nico Haase, May 06 '19 at 07:45
Looks like an Oracle error code. Are you sure you're using MySQL?!? — jarlh, May 06 '19 at 07:46
why don't you show your expected output and provide some sample data — Fahmi, May 06 '19 at 07:48
Your query seems have some errors: wrong column use for `aggregate functions` for `GROUP BY`, wrong condition in `WHERE` clause (`to_date` part). — Pham X. Bach, May 06 '19 at 07:48

score 0 · Accepted Answer · answered May 06 '19 at 08:04

There is a syntax error in the following two lines

sum(case when cv_amount > 0 then cv_amount else 0) end as d, 
sum(case when cv_amount < 0 then cv_amount*-1 else 0) end as c

the end should be with in the parentheses, so the query wil be:

sum(case when cv_amount > 0 then cv_amount else 0 end) as d, 
sum(case when cv_amount < 0 then cv_amount*-1 else 0 end) as c

score 0 · Answer 2 · answered May 06 '19 at 10:44

You don't really need case for this:

select cv_amount,
       greatest(cv_amount, 0) as debits,
       abs(least(cv_amount, 0)) as credits,
       sum(greatest(cv_amount, 0)) as d,
       sum(abs(least(cv_amount, 0))) as c
from dof
where currency_code = 368 and
      <something goes here> = date '2019-05-05'
group by cv_amount;

The bigger issues are that the where clause doesn't make sense. And the aggregation by cv_amount doesn't really make sense.

How to fix ORA-00905 missing keyword with case expression

2 Answers2