I have a query, where i want to use the column connector from GRACREQPROV_GRP table into another query of table GRFNMWRT_GRP for defining some case statements.

so basically i want the logic as IF connector has ‘ASP’ or ‘P’ then take rank=1
if connector starts with GRP then take rank 2,
but when i am trying to use connector column, it is giving error as column not found.

select distinct 
gruser.USERID as userid,
  gpv.CONNECTOR AS Server,
req.REQNO AS Ticket_no,
grfinst.CREATED_BY Ticket_created_by,
  zgrac.ZEMPNAME AS Ticket_created_by_NAME,
  grfn.CHANGED_BY Ticket_assigned_to,
  zgrac.ZEMPNAME AS Ticket_assigned_to_by_NAME,
  grfinst.INSTANCE_STATUS,
  grfinst.APPROVAL_STATUS,
  rnk

from 

`GRAC_GRP` req

LEFT JOIN 
(select distinct MANDT,REQ_ID,CONNECTOR from`GRACREQPROV_GRP`
 ) gpv
ON
  req.MANDT=gpv.MANDT
  AND req.REQ_ID=gpv.REQ_ID

left join 
(select distinct MANDT,EXTERNAL_KEY,FINISHED_AT,CREATED_BY,INSTANCE_ID,PROCESS_ID, INSTANCE_STATUS, APPROVAL_STATUS,EXTERNAL_KEY_DIS from `GRFNMWRT_GRP`
 
) grfinst 

on req.MANDT=grfinst.MANDT
AND req.REQNO=grfinst.EXTERNAL_KEY_DIS

LEFT JOIN 

(select distinct MANDT,ZEMPID,ZEMPNAME FROM `ZGRAC_EMP_GRP`) zgrac

on grfinst.MANDT=zgrac.MANDT
AND grfinst.CREATED_BY=zgrac.ZEMPID

LEFT JOIN 

(select distinct CHANGED_BY,INSTANCE_ID,LINEITEM_STATUS,TIMESTAMP_LONG,CONNECTOR
ROW_NUMBER() OVER(PARTITION BY INSTANCE_ID, TIMESTAMP_LONG ASC) as rnk

 from `GRFNMWRT_GRP`  ) grfn

ON grfinst.INSTANCE_ID= grfn.INSTANCE_ID
AND grfn.CHANGED_BY= zgrac.ZEMPID

WHERE  
REQ.REQTYPE IN (1,2,3,4,5,6,124,125)
AND INSTANCE_STATUS IN("RUNNING","FINISHED")
AND LINEITEM_STATUS IN ("APPROVED", "REJECTED", "ONHOLD")