Table does not have a column named when read_csv result is aliased

[ashokgudur]

The following query throws error: Table "sdrnew" does not have a column named "HASHKEY" LINE 3: WHERE sdrnew.HASHKEY != 'aaaaa123'

SELECT 'aaaaa123' HASHKEY, *
FROM read_csv('sdrs1.txt', null_padding=true,ignore_errors=true, sep='|') sdrnew
WHERE sdrnew.HASHKEY != 'aaaaa123'

But the the same query without alias name is removed from the where clause works fine.

SELECT 'aaaaa123' HASHKEY, *
FROM read_csv('sdrs1.txt', null_padding=true,ignore_errors=true, sep='|') sdrnew
WHERE HASHKEY != 'aaaaa123'

I need to use this in importing csv file and check duplicates like this.

INSERT INTO sdrmain
SELECT hash(MSISDN) HASHKEY,*
FROM read_csv('c:\sdrdata\sdrs1.txt', null_padding=true,ignore_errors=true, sep='|') sdrnew
WHERE NOT EXISTS (SELECT HASHKEY FROM sdrmain WHERE sdrmain.HASHKEY = sdrnew.HASHKEY)

Can anybody help me?

[Tishj]

The problem is that that is not a table alias name, it's an alias for the expression inside the projection list, it does not relate to the table

Your problem is somewhat similar to #11962

[ashokgudur]

OK. I used it as subquery with an alias and referenced the alias.
It worked fine.
Thanks Tishj