ORA-935缺失表达式错误排查：SQL语句疑难求助

Hey there, sorry you're stuck grinding on this ORA-00935 error—those can be frustrating when syntax looks correct at a quick glance. Let's start with the most likely culprit in your code, then cover other debugging angles.

The Primary Issue: Incorrect CASE Expression Syntax #

Your SQL uses END CASE to close the CASE blocks in your SELECT lists, but Oracle SQL only uses END for CASE expressions (the kind you use to return a value in a SELECT). END CASE is reserved for PL/SQL's procedural CASE statements (used for flow control, not value returns). This misalignment is almost certainly triggering the "missing expression" error.

Fix the CASE Blocks #

Change every instance of END CASE to just END:
Before:

CASE SUBSTR(CUSTOMERKEY,1,1) WHEN ''P'' THEN ''I'' WHEN ''O'' THEN ''B'' END CASE

After:

CASE SUBSTR(CUSTOMERKEY,1,1) WHEN ''P'' THEN ''I'' WHEN ''O'' THEN ''B'' END

Additional Debugging Checks #

Even after fixing the CASE syntax, here are a few other things to verify to avoid similar issues:

• Validate UNION Column Consistency: Double-check that both SELECT statements in the UNION return exactly the same number of columns, with matching data types. Mismatches here can throw unexpected syntax or type errors.
• Inspect Dynamic Variable Concatenation: Your query uses dynamic string拼接 (like ' || in_HHNbr || '). Ensure variables like in_HHNbr, in_NewUpd, etc., aren't empty or containing characters that break the SQL syntax (e.g., unescaped quotes). For better safety and performance, consider using bind variables instead of direct string concatenation (this also prevents SQL injection risks).
• Test in Chunks: Try running each SELECT statement separately first (without the INSERT and UNION) to isolate which part is causing the error. If one SELECT runs fine but the other doesn't, you'll know exactly where to focus.

Corrected Full SQL #

Here's the revised query with the CASE syntax fixed:

INSERT INTO RPT_HOUSEHLDBATCH (
    CUSTOMERKEY,
    HOUSEHOLDNBR,
    CUSTOMERTYPE,
    LASTNAME,
    FIRSTNAME,
    ADDRNBR,
    AddressLine1,
    AddressLine2,
    AddressLine3,
    CITYNAME,
    STATECD,
    ZIPCD,
    SCORE,
    DATECREATED,
    RUNDATE,
    TYPECD,
    PREVIOUSHHLDNBR
) 
SELECT 
    CustomerKey,
    ' || in_HHNbr || ',
    CASE SUBSTR(CUSTOMERKEY,1,1) WHEN ''P'' THEN ''I'' WHEN ''O'' THEN ''B'' END,
    a.LastName,
    a.FirstName,
    AddrNbr,
    AddressLine1,
    AddressLine2,
    AddressLine3,
    Cityname,
    StateCd,
    ZipCd,
    2,
    b.AddDate,
    SYSDATE,
    ''' || in_NewUpd || ''',
    HouseHoldNbr 
FROM rpt_HouseHldBatchwrk a 
JOIN PERS b ON SUBSTR(a.CUSTOMERKEY,2) = b.PersNbr 
WHERE CUSTOMERKEY = ''P' || in_PersNbr || ''' 
UNION 
SELECT 
    CustomerKey,
    ' || in_HHNbr || ',
    CASE SUBSTR(CUSTOMERKEY,1,1) WHEN ''P'' THEN ''I'' WHEN ''O'' THEN ''B'' END,
    a.LastName,
    a.FirstName,
    AddrNbr,
    AddressLine1,
    AddressLine2,
    AddressLine3,
    Cityname,
    StateCd,
    ZipCd,
    2,
    b.AddDate,
    SYSDATE,
    ''' || in_NewUpd || ''',
    HouseHoldNbr 
FROM rpt_HouseHldBatchwrk a 
JOIN ORG b ON SUBSTR(a.CUSTOMERKEY,2) = b.OrgNbr 
WHERE CUSTOMERKEY = ''O' || in_OrgNbr || '''

Give that a try—this should resolve the ORA-00935 error. If you're still hitting issues, testing the individual SELECT statements will help narrow things down further.

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