Orcale - Missing Right Paranthesis-CodePudding

I am trying to run an oracle query using, Case when and Sum together, It gives me an error saying I am missing the right paranthesis.

The query below,

SELECT  c.Customer_name,
        CASE WHEN SUM(status = 'DELIVERED') = count(*) THEN 'A'
            WHEN SUM(status = 'DELIVERED') > 0  THEN 'B'
            WHEN SUM(status = 'SUBMITTED') > 0  THEN 'C'
             ELSE 'No Match'
        END AS Match
FROM    Customer_Order c
group by c.Customer_name order by c.Customer_name;

Changed my query to look like this and it says, I am missing keyword

SELECT  c.Customer_name,
        CASE WHEN SUM(c.status) = 'DELIVERED' = (count(c.status)) THEN 'A'
            WHEN SUM(c.status) = 'DELIVERED' > 0  THEN 'B'
            WHEN SUM(c.status) = 'SUBMITTED' > 0  THEN 'C'
             ELSE 'No Match'
        END AS Match
FROM    Customer_Order c
group by c.Customer_name order by c.Customer_name;

CodePudding user response：

Ok, what I think you are trying to do includes counting all the cases where status = 'DELIVERED' and status = 'SUBMITTED' and then comparing those with something to determine what the outcome is. But you can't sum a conditional expression like status = 'DELIVERED' so you need to do it in two steps: one to make a number out of the conditional and one to compare the sum with something.

Step 1: To count the cases where status = 'DELIVERED' you need a column that is 1 when the status is DELIVERED and 0 when it is not (and the same with 'SUBMITTED'). That way you have numbers to sum. So the data we want to run your query on is:

SELECT *,
  CASE WHEN status = 'SUBMITTED' THEN 1 ELSE 0 END AS Is_submitted,
  CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END AS Is_delivered
FROM Customer_order

Step 2: Now we can use your query on the results of Step 1:

SELECT
  c.Customer_name,
  CASE WHEN SUM(c.delivered) = count(*) THEN 'A'
       WHEN SUM(c.delivered) > 0 THEN 'B'
       WHEN SUM(c.submitted) > 0 THEN 'C'
       ELSE 'No Match'
  END AS X
FROM (the query above) as c
GROUP BY c.Customer_name
ORDER BY c.Customer_name;

Substituting Step 1 into Step 2 we get the actual query:

SELECT
  c.Customer_name,
  -- SUM(c.Is_delivered),
  -- SUM(c.Is_submitted),
  -- count(*),
  CASE WHEN SUM(c.delivered) = count(*) THEN 'A'
       WHEN SUM(c.delivered) > 0 THEN 'B'
       WHEN SUM(c.submitted) > 0 THEN 'C'
       ELSE 'No Match'
  END AS X
FROM (
  SELECT *,
    CASE WHEN status = 'SUBMITTED' THEN 1 ELSE 0 END AS submitted,
    CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END AS delivered
  FROM Customer_order
) as c
GROUP BY c.Customer_name
ORDER BY c.Customer_name;

Try it here:

https://www.db-fiddle.com/f/mSdREEgU1mT1pvczG2kZgm/1

CodePudding user response：

You appear to be trying to count true comparions; unlike some other languages, in SQL a true value is not the same as 1 and a false value is not 0 so you need to use a CASE expression to convert the comparison to something that can be summed (or counted).

In this case, you can use conditional aggregation:

SELECT Customer_name,
       CASE
       WHEN COUNT(CASE status WHEN 'DELIVERED' THEN 1 END) = COUNT(status)
       THEN 'A'
       WHEN COUNT(CASE status WHEN 'DELIVERED' THEN 1 END) > 0
       THEN 'B'
       WHEN COUNT(CASE status WHEN 'SUBMITTED' THEN 1 END) > 0
       THEN 'C'
       ELSE 'No Match'
       END AS Match
FROM   Customer_Order
group by Customer_name
order by Customer_name;