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!
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
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
vehicles. Else the qualification
vehicles. would not make sense.
Use a "searched"
CASE for a decision between two alternatives.
You can also use
COUNT for your particular case. The online reference again:
If you specify
COUNTreturns the number of rows where
expris not null.
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls.
COUNTnever returns null.
So you need the difference:
Select COUNT(*) - COUNT(vehicles.mileage) AS NO_MILEAGE, COUNT(*) - COUNT(vehicles.price) AS NO_PRICE From [data];