0

I'm writing a fairly simple nested select query to change the value in one of my columns but for some reason I keep getting an error related to the resolution of the columns: "Column 'test1' cannot be resolved"

As per the findings this question: Amazon Athena - Column cannot be resolved on basic SQL WHERE query I've tried to delete the column and re-add it with no luck.

Additionally, when I execute the below it works. 

select
test1, test2, test3
from datanewdb;

Here is the code that isn't working:

select 
test1, test2, test3
from (
  select
  'FILLER',
  test2,
  test3
  from datanewdb)
datanewdb 
where test1 = '123';

The actual value of those columns on the table:

  • test1: CODE 250
  • test2: 12.50
  • test3: negative

I would expect my code to return:

  • test1: FILLER
  • test2: 12.50
  • test3: negative
Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
midiman
  • 109
  • 1
  • 3
  • 17

1 Answers1

4

With a sub-query in SQL only the columns defined in the SELECT clause are exposed outside of the sub-query. If you want to be able to reference the column associated with the new literal value 'FILLER', you need to name it:

select test1, test2, test3
from (
    select
        'FILLER' AS test1,
        test2,
        test3
    from datanewdb
) datanewdb 
where test1 = '123';
Dain Sundstrom
  • 2,699
  • 15
  • 14
  • Wow.. I was looking at the code for quite a bit of time, I can't believe I missed that. Thank you! I'm facing another issue now but it's unrelated to this question. – midiman May 16 '19 at 20:21