>Can you nest a CASE statement within another CASE statement when passing a SPT query to SQL Server 7? I need to determine whether one field is an "A" or "B", then based on that, calculate an amount based on the value in another field. I am trying to do this in one query with something like:
>
>CASE wkdsr.pay_type
>WHEN 'FED' THEN
> CASE disaster.override1
> WHEN 0 THEN wkapp.amt_paid*(disaster.fed_pct/100)
> ELSE wkapp.amt_paid*(disaster.override1/100)END AS amt_paid
>ELSE
> CASE disaster.override2
> WHEN 0 THEN wkapp.amt_paid*(disaster.st_pct/100)
> ELSE wkapp.amt_paid*(disaster.override2/100) END AS amt_paid
>
>Anyone know something that will work??
>
>Thanks,
>Doug

Is the A or B value an external value or is it part of the query in the form of another field.

In the first case... you want to execute a different select based on some value... build the select statement into a string variable:

lcSelect = 'SELECT '
do case
case Blah = Blat
lcSelect = lcSelect + 'cField1/cField2 as Sprat '
case Blah = Blot
lcSelect = lcSelect + '(cField1/cField2)*.5 as Sprat '
endcase
lcSelect = lcSelect + 'FROM MyTable WHERE '
do case
* build the where

***************
In the second case, not you can not put CASE statements into the SQL, but you can use the IIF...

select cType,IIF(cType = 'A',nAmount,nDetail) as Amount FROM ...

Of course, you can nest the IIF's too... sometimes though, you are better off doing two selects or using xBase commands to get what you want.

BOb