Hi Alex,

Let me say outright that the CASE function in T-SQL is much better then IIF() in VFP. You missed that it has another form

SELECT CASE 
WHEN code = 0 THEN 'Something'
WHEN code = 1 AND code2 = "A" THEN 'Something Else'
...
ELSE 'N/A'
END

Can you explain how SUM() fails? Error? Something else?

>I have the following query (somewhat simplified for this example) that works fine in VFP8.
>The important point is the use of IIF() to add or subtract transaction count and amount depending on certain codes.
>

>Select MerchantId,
>  sum(Iif(Right(Trn_code,1) = "0", 1, -1)) As Trans, ;
>  sum(Iif(Right(Trn_code,1) = "0", amount, -amount)) As Purchases ;
>from History ;
>where filedate Between dDate1 And dDate2 ;
>  and (Trn_code = "P0" or Trn_code = "P1") ;
>  country_cd = lcCtry_Cd ;
>group By MerchantId ;
>into Cursor Temp1
>

>Codes used are many but we are interested in only P0 and P1. There is a lookup table that tells us if a code is a "+" or a "-" for a proper SUM(). The data cannot be changed. The amounts are always positive in the underlying data. The query has to decide if it is plus or minus for the sum.
>
>Challenge:
>Due to the amount of data (6 million+ records and growing daily), the table is close to 2GB in size. I now have to migrate this table to SQL Server 2000 which does not have an IIF().
>
>Reading on T-SQL I see a bad/incomplete implementation of ICase() (IMHO) as follows:
>

>SELECT CASE code
>WHEN 0 THEN 'Something'
>WHEN 1 THEN 'Another'
>WHEN 2 THEN 'Whatever'
>ELSE 'N/A'
>END
>

>But I cannot get it to work for my case.
>
>If I start to build it in parts,
>

>Select MerchantId,
>case Trn_code
>when 'P0' then 1
>else -1
>end as Trans
>from History
>where filedate='04/01/2004'
>

>this works. But when I start adding SUM() and then also the part on Sum(Amount) it fails.
>
>I though of trying to do away with IIF() altogether and try a join to the lookup table to see if it each record should be added or subtracted but cannot get it to work.
>
>Maybe creating a T-SQL IIF() equivalent function?
>
>Any ideas or suggestions welcome.
>
>TIA