2

This question already has an answer here:

I want to use "MaxID" as part of my join condition, but it keeps throwing an error:

ERROR: syntax error at or near "["
LINE 29: AND t1.["MaxID"]=t2.id

This is my query:

with MaxIDs as
(
 select max(id) as "MaxID"
 ,elID
 from valsum
 group by elID
)
select 
t1.elID
,es.eNum
,es.des
,es.d1
,case 
  when es.st = 1 then 'Pink' 
  when es.st = 2 then 'Red' 
  when es.st = 3 then 'Blue' 
  ELSE es.st::text 
END AS st
,t1.price
from MaxIDs t1
INNER JOIN etime es 
ON t1.elID=es.elID
join valsum t2 
ON t1.elID=t2.elID 
AND t1.MaxID=t2.id   
WHERE CAST(es.d1 As Date) BETWEEN '01/01/2016' AND '12/31/2016'
AND es.st IN (3) 
ORDER BY CAST(es.d1 As Date) DESC

I also tried to use the alias w/o the brackets, but the same error was thrown. How can I join on this field?

With no brackets and no quotes this is the error:

AND t1.MaxID=t2.id   

ERROR: column t1.maxid does not exist
LINE 29: AND t1.MaxID=t2.id

marked as duplicate by Paul White Oct 27 '17 at 5:47

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

Use t1."MaxID" literally the same as in with or drop quotes in with.

Not the answer you're looking for? Browse other questions tagged or ask your own question.