oracle sql about table.substr

Question

something wrong with my sql

environment：oracle 12C

--table.title is available, and sql below is works

select substr(title,0,length(title)-8)  from table

--but this sql didn't work how to fix it?

SELECT tableA.something
FROM tableA 
LEFT JOIN table
on tableA.name = table.substr(0,LENGTH(title)-8) 

--output

"table"."SUBSTR":invalid identifier

thx.

Answer 1

Adjust your syntax to:

SELECT tableA.something
FROM tableA 
LEFT JOIN table
on tableA.name = substr(table.title, LENGTH(table.title)-8)

table.title is an argument to the LENGTH() function. Also needs to be argument to SUBSTR().