Erro: could not devise a query plan for the given query

user_5292347 0
Viewed 4165

Whats wrong with this query?I'm trying to run on redshift but i'm getting the erro could not devise a query plan for the given query .

The 2 tables have filenames and I wish to discover which files they share and which they dont(I need to know which one has the file in this case). In case they share, the filename would appear on both columns, in case they dont share, the filename would appear only on the column of the table that has it while the other column would be null/empty. There are rows with the same filename so thats why I used distinct. I used substring to ignore the extension.

select distinct a.filename as arquivo_a , r.filename as arquivo_r 
from table1 a
FULL JOIN table2 r ON  SUBSTRING(a.filename,1,LEN(a.filename) - 4) = SUBSTRING(r.filename,1,LEN(r.filename) - 4)
amazon-redshift sql
1 Answers

You got this error because you can't use formula on the left join field, so you should have used at least something like:

select *
from table1 a
FULL JOIN table2 r 
   ON a.filename_without_ext = SUBSTRING(r.filename,1,LEN(r.filename) - 4)

and it would have worked.

The fact that the filename was not unique is not relevant

user_78674200
Related