CODEWITHSUNDEEP
sqloracle-database
Heisenberg
Heisenberg

Reputation: 5299

How to avoid `missing expression` error in sql

I'd like to count number of each product by following sql

but I suffered following error..

ORA-00936: Missing Expression

Where is wrong with my sql.

If someone has opinion,please let me know.

Thanks

select t.customer,
       count(case when left(t.product,2)='AD' then 1 end) as A,
       count(case when left(t.product,1)='F' then 1 end) as B,
       count(case when left(t.product,1)='H' then 1 end) as C,
  from table t
  left join table2 t2
    on t.customer = t2.customer
 where t2.type='VLI'
 group by t.customer

Upvotes: 0

Views: 67

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I would suggest LIKE:

select t.customer,
       sum(case when t.product like 'AD%' then 1 else 0 end) as A,
       sum(case when t.product like 'F%' then 1 else 0 end) as B,
       sum(case when t.product like 'H%' then 1 else 0 end) as C
from table t left join
     table2 t2
     on t.customer = t2.customer and t2.type='VLI'
group by t.customer;

Notes:

  • With like, you don't have to worry about an argument to a substring function matching the length of the matched string. I learned about the issues of inconsistencies there a long, long, long time ago.
  • I prefer sum() in this case over count(), but that is mostly aesthetic.
  • You are using a left join, but filtering in the where clause. This turns the join into an inner join. Either change the join to inner join or move the condition to the on clause (as this does).

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

Oracle doesn't have LEFT() function while MySQL does, use SUBSTR() instead. And remove the comma, which's typo , at the end of the fourth line

SELECT t.customer,
       COUNT(CASE
               WHEN SUBSTR(t.product, 1, 2) = 'AD' THEN
                1
             END) AS A,
       COUNT(CASE
               WHEN SUBSTR(t.product, 1, 1) = 'F' THEN
                1
             END) AS B,
       COUNT(CASE
               WHEN SUBSTR(t.product, 1, 1) = 'H' THEN
                1
             END) AS C
  FROM yourtable t
  LEFT JOIN table2 t2
    ON t.customer = t2.customer
 WHERE t2.type = 'VLI'
 GROUP BY t.customer

Upvotes: 2

Anton Tokmakov
Anton Tokmakov

Reputation: 710

You have extra comma in row count(case when left(t.product,1)='H' then 1 end) as C,. Delete the last comma and this error will go away.

Upvotes: 0

Related Questions