SQL Developer查询昨日数据时遇Missing Expression Error求助

Hey there! Let's figure out why you're getting that "Missing Expression" error and fix your SQL query properly. There are a few syntax and logical issues in your original code, so let's break them down one by one:

Key Issues in Your Original Query #

• You forgot the FROM clause: The database has no idea which table to pull data from – this is a critical omission that's definitely causing part of the error.
• Incorrect clause order: Your WHERE statement is stuck right in the middle of your SELECT columns, which violates basic SQL syntax rules. Clauses need to follow the order: SELECT → FROM → WHERE → GROUP BY (when using aggregates like SUM).
• Misusing DISTINCT with aggregate functions: When you're using SUM to calculate totals, you don't need DISTINCT at the start of the query. Instead, you need a GROUP BY clause to group your results by the non-aggregated columns (in this case, CM_NAME and Booking_Date).
• Potential date mismatch: Using sysdate-1 will give you the current time minus 24 hours, not the entire previous calendar day. If your Booking_Date includes a time component, this might miss records from yesterday morning, or include records from today if the time is early enough.

Fixed Query #

Here's the corrected version with explanations embedded:

SELECT 
    REPLACE(CM_NAME, '/', ' ') AS CM_NAME,
    TO_CHAR(Booking_Date, 'MM/DD/YYYY') AS Booking_Date,
    SUM(Air_Revenue) AS TTL_AIRFARE,
    SUM(Room_Revenue) AS TTL_ROOM,
    SUM(Car_Revenue) AS TTL_CAR,
    SUM(Activity_Revenue) AS TTL_ACTIVITY,
    0 AS TTL_CRUISE,
    SUM(Air_Revenue + Room_Revenue + Car_Revenue + Activity_Revenue) AS TTL_BOOKING
FROM 
    your_table_name -- Replace this with your actual table name!
WHERE 
    TRUNC(Booking_Date) = TRUNC(SYSDATE) - 1 -- Truncates time to match full yesterday
GROUP BY 
    REPLACE(CM_NAME, '/', ' '),
    TO_CHAR(Booking_Date, 'MM/DD/YYYY')

What Changed? #

1. Added the FROM clause with a placeholder – make sure to swap your_table_name for the real name of the table you're querying.
2. Moved the WHERE clause to its correct position, and used TRUNC() on both dates to ignore the time component, ensuring we capture all records from the entire previous day.
3. Removed DISTINCT and added a GROUP BY clause that includes every non-aggregated column in the SELECT statement. This tells the database how to group rows before calculating the sums.
4. Formatted the query with line breaks for readability – this makes it way easier to catch syntax errors later on.

内容的提问来源于stack exchange，提问作者Kamran Malik