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),