>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