Why do I get an "invalid identifier" error when I use LEFT()? [duplicate]

Question

From a 6-character string in TERM_CODE_KEY I need to produce a calculated result as column academic_yr:

-- TERM_CODE_KEY is of type VARCHAR2(6) and returns values of the form YYYYMM
SELECT TERM_CODE_KEY,
CASE SUBSTR(TERM_CODE_KEY,5)                -- Get MM part
    WHEN '01' THEN LEFT(TERM_CODE_KEY,4)    -- start of a calculation on the YYYY part
    WHEN '05' THEN LEFT(TERM_CODE_KEY,4)    -- start of a calculation on the YYYY part
    ELSE LEFT(TERM_CODE_KEY,4)              -- start of a calculation on the YYYY part
END AS academic_yr
FROM DWH.SR_COURSES_FAC
WHERE (TERM_CODE_KEY BETWEEN '202001' AND '202409')
ORDER BY TERM_CODE_KEY, academic_yr ASC

Oracle returns an error on the ELSE clause:

SQL Error [904] [42000]: ORA-00904: "LEFT": invalid identifier

Why is LEFT() identified as an identifier? If I change the ELSE clause to return a string instead of a calculation the same error moves up to the last WHEN clause. It should be possible to execute operations in the THEN and ELSE clauses. What am I doing wrong?

Answer 1

It's syntactically correct, but LEFT() doesn't exist in Oracle. Instead, use SUBSTR()

SELECT TERM_CODE_KEY,
       CASE SUBSTR(TERM_CODE_KEY, 5, 2)
           WHEN '01' THEN SUBSTR(TERM_CODE_KEY, 1, 4)
           WHEN '05' THEN SUBSTR(TERM_CODE_KEY, 1, 4)
           ELSE SUBSTR(TERM_CODE_KEY, 1, 4)
       END AS academic_yr
FROM DWH.SR_COURSES_FAC
WHERE TERM_CODE_KEY BETWEEN '202001' AND '202409'
ORDER BY TERM_CODE_KEY, academic_yr ASC;

LEFT() is T-SQL based; a SQL Server function