SQLTeam.com | Weblogs | Forums

Set a Variable to the result of a select statement

I want to select the most recent record by date but i want the cost from that record and not the date field. I saw that i can use 'max' to get the most recent record and that part works but like i said i dont want the date, i want the cost. So, i thought i could set the resulting date equal to a variable then use that variable in my next select statement. I getting the message 'Incorrect syntax near 'set''. There might be an easier way to get to what i need but i dont know it. Here is a portion of the query:

declare @POD DATETIME

select distinct
ITEMMAST.ITEM as Item,
ITEMMAST.DESCRIPTION as Item_Description,
ITEMMAST.DESCRIPTION as Short_Description,

   CASE 
      WHEN TRACKING_FL_04 <> ' ' THEN ALT_UOM_04
      WHEN TRACKING_FL_03 <> ' ' THEN ALT_UOM_03
      WHEN TRACKING_FL_02 <> ' ' THEN ALT_UOM_02 
      ELSE ALT_UOM_01
   END as Issue_UOM,
   
   set @POD = (select max(POLINE.EARLY_DL_DATE)
               from ls_apps.POLINE 
               where POLINE.ITEM = ITEMMAST.ITEM),
       
   (select POLINE.ENT_UNIT_CST as Cost    
       from ls_apps.POLINE 
       where POLINE.ITEM = ITEMMAST.ITEM and POLINE.EARLY_DL_DATE = @POD),

I resolved this myself by using the following code:

(select top 1 POLINE.ENT_UNIT_CST
from ls_apps.POLINE
where POLINE.ITEM = ITEMMAST.ITEM and POLINE.EARLY_DL_DATE =
(select max(POLINE.EARLY_DL_DATE)
from ls_apps.POLINE
where POLINE.ITEM = ITEMMAST.ITEM)) as Cost,

It did give me an issue where it was giving me an error because it was returning more than 1 value (I am assuming because we have multiple locations and each location could have a record with the same date) so i added the 'Top 1' to the beginning of the sequence to get the first instance.