Oracle左连接中使用SUBSTR函数触发ORA-00904错误的技术求助

Hey there, let's break down why you're hitting this error and get it fixed quickly.

What's causing the issue? #

The ORA-00904 error here stems from a simple syntax mistake: you’re treating Oracle’s built-in SUBSTR function as if it’s a column belonging to table B.

• In your SELECT clause, B.SUBSTR(TRAN_REF,11,10) is wrong because SUBSTR isn’t a column in Table2—it’s an Oracle system function. You can’t prefix it with a table alias like B..
• The same mistake happens in your JOIN condition: B.SUBSTR(SRC_TRAN_REF,11,10) incorrectly tries to call SUBSTR as a table-specific object, which it’s not.

The Solution #

You need to adjust the syntax to pass the table B columns as arguments to the SUBSTR function, instead of trying to call the function as if it’s part of the table. Here’s how to fix both parts:

1. In the SELECT list: Replace B.SUBSTR(TRAN_REF,11,10) with SUBSTR(B.TRAN_REF,11,10)
2. In the JOIN condition: Replace B.SUBSTR(SRC_TRAN_REF,11,10) with SUBSTR(B.SRC_TRAN_REF,11,10)

Corrected Full SQL Query #

SELECT 
    A.column1,
    SUBSTR(B.TRAN_REF, 11, 10),
    A.column2,
    B.column1,
    B.column2 
FROM Table1 A 
LEFT JOIN Table2 B 
    ON A.column1 = SUBSTR(B.SRC_TRAN_REF, 11, 10);

Quick double-check: Make sure TRAN_REF and SRC_TRAN_REF are valid columns in Table2. If either of those doesn’t exist, you’ll hit the same ORA-00904 error—but this time for the column name instead of the function.

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