SQL server Computed column giving in-correct result-CodePudding

Following SQL Statement returns NULL in MP_SALESID. However if I replace where KRFPRODUCTIONORDERGROUPiD = 'T1.KRFPRODUCTIONORDERGROUPID' with an actual value for T1.KRFPRODUCTIONORDERGROUPID then I am getting a value in MP_SalesId. What am I missing here? I have been fighting this for last 12 hours :(

   SELECT T1.KRFPRODUCTIONORDERGROUPID AS KRFPRODUCTIONORDERGROUPID, 
   (CAST ((select top(1) salesId from salesLine 
   join INVENTTRANSORIGIN as SalesLineInventTransOrigin on 
   SalesLineInventTransOrigin.INVENTTRANSID = salesLine.INVENTTRANSID 
   join InventTrans as ProdTableInventTrans on 
   ProdTableInventTrans.MARKINGREFINVENTTRANSORIGIN = 
   SalesLineInventTransOrigin.RECID 
   join INVENTTRANSORIGIN as ProdTableInventTransOrigin on 
   ProdTableInventTransOrigin.recid = 
   ProdTableInventTrans.INVENTTRANSORIGIN 
   join ProdTable on ProdTableInventTransOrigin.INVENTTRANSID = 
   PRODTABLE.INVENTTRANSID 
   where KRFPRODUCTIONORDERGROUPiD = 'T1.KRFPRODUCTIONORDERGROUPID') 
   AS NVARCHAR(20))) AS MP_SALESID
   FROM PRODTABLE T1

CodePudding user response：

Don't use single quote(') around T1.KRFPRODUCTIONORDERGROUPID

SELECT T1.KRFPRODUCTIONORDERGROUPID AS KRFPRODUCTIONORDERGROUPID, 
   (CAST ((select top(1) salesId from salesLine 
   join INVENTTRANSORIGIN as SalesLineInventTransOrigin on 
   SalesLineInventTransOrigin.INVENTTRANSID = salesLine.INVENTTRANSID 
   join InventTrans as ProdTableInventTrans on 
   ProdTableInventTrans.MARKINGREFINVENTTRANSORIGIN = 
   SalesLineInventTransOrigin.RECID 
   join INVENTTRANSORIGIN as ProdTableInventTransOrigin on 
   ProdTableInventTransOrigin.recid = 
   ProdTableInventTrans.INVENTTRANSORIGIN 
   join ProdTable on ProdTableInventTransOrigin.INVENTTRANSID = 
   PRODTABLE.INVENTTRANSID 
   where KRFPRODUCTIONORDERGROUPiD = T1.KRFPRODUCTIONORDERGROUPID) 
   AS NVARCHAR(20))) AS MP_SALESID
   FROM PRODTABLE T1