You can use CASE in SQL as you use IIF in foxpro
SELECT CLIENTFIRST, CLIENTLAST,
CASE WHEN STAFF_KEY=12 THEN 1
ELSE 0
END AS MYCLI FROM CLIENTS
>In SQL passthrough queries, is there a way other than a brute force double query joined with UNION ALL to return the results of a logical exression as a column? This syntax is not accepted by MS SQL Server:
>
>SELECT CLIENTFIRST, CLIENTLAST, STAFF_KEY=12 AS MYCLI FROM CLIENTS
>
>the trick of
>
>SELECT CLIENTFIRST, CLIENTLAST, 1 AS MYCLI FROM CLIENTS WHERE STAFF_KEY=12
>UNION ALL
>SELECT CLIENTFIRST, CLIENTLAST, O AS MYCLI FROM CLIENTS WHERE STAFF_KEY<>12
>
>gets very unwieldy when you want multiple logical columns.
--sb--