>I'm trying to add a status column to a select statement based on the contents of a different file. I'm trying to pull the customer number from the customer file and the value of 1 or 3 as nstatus. This status value needs to be 1 if the customer is found in the following (select custno from arship where dContract_Exp is null) and 3 if it is not.
>
>
>
>select custno, iif(custno in ( select custno from arship where dContract_Exp is null ), 1, 3) as nstatus from dbo.customer
>
>
>
>TIA
>Jeff
Jeff,
Try
select C.CustNo,
case when exists(select 1 from ArShip A where A.dContract_Exp is null and A.CustNo = C.CustNo)
then 1
else 3
end as nStatus from dbo.customer C
If it's not broken, fix it until it is.
My Blog