Justin123 Justin123 - 2 months ago 6
SQL Question

Using Case to sum NULL instances gives missing expression error

I'm attempting to generate a list of vehicles that don't have a price or mileage listed using the below query. When I attempt to run the query, I get an error "ORA-00936: missing expression", but can't seem to find out why. From other posts here, I can see that using IS NULL should be the appropriate term for the WHEN portion, but I am not seeing anything wrong with the query itself. Any help would be appreciated!

Select
SUM(CASE vehicles.mileage WHEN IS NULL THEN 1 ELSE 0 END) NO_MILEAGE,
SUM(CASE vehicles.price WHEN IS NULL THEN 1 ELSE 0 END) NO_PRICE
From
[data]

Answer

Simple syntax error:

Select
    SUM(CASE WHEN vehicles.mileage IS NULL THEN 1 ELSE 0 END) NO_MILEAGE,
    SUM(CASE WHEN vehicles.price   IS NULL THEN 1 ELSE 0 END) NO_PRICE
From
    [data];

This is assuming a table named vehicles in your FROM clause or a columns with an object or nested table type in [data] named vehicles. Else the qualification vehicles. would not make sense.

Use a "searched" CASE for a decision between two alternatives.

Details about "simple" and "searched" CASE in the Oracle online reference.


You can also use COUNT for your particular case. The online reference again:

If you specify expr, then COUNT returns the number of rows where expr is not null.

If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null.

So you need the difference:

Select 
    COUNT(*) - COUNT(vehicles.mileage) AS NO_MILEAGE,
    COUNT(*) - COUNT(vehicles.price)   AS NO_PRICE
From
    [data];
Comments