>>Hello,
>>
>>here's the situation:
>>
>>I do have a select statement:
>>
>>SELECT t.*, (t.number*t.price) * iif(t.transs = ""CS"" OR t.transs = ""DV"" OR t.transs = ""L "" OR t.transs = ""R "" OR t.transs = ""RC"" OR t.transs = ""RD"" OR t.transs = ""V "" OR t.transs = ""ST"" OR t.transs = ""OP"" , 1, -1) as bruto from trans t
>>
>>It gives me the following result for bruto:
>>
>>912414,12124
>>12421
>>111, 12425235
>>etc.
>>
>>Cause these are all amounts I would like to have just 2 places behind the comma. I tried
>>
>>SELECT t.*, (t.number*t.price) * iif(t.transs = ""CS"" OR t.transs = ""DV"" OR t.transs = ""L "" OR t.transs = ""R "" OR t.transs = ""RC"" OR t.transs = ""RD"" OR t.transs = ""V "" OR t.transs = ""ST"" OR t.transs = ""OP"" , 1, -1)+00000000,00 as bruto from trans t
>>
>>and that gives me all 0
>>
>>I changed the comma into a dot and yet it gives me all the numbers behind the comma.
>>
>>Any other suggestions ?
>>
>
>Aside from shooting the person who hardcoded this list of values, skinning them, strapping them nekkid out on an anthill on a blistering hot, sticky day and forcing them to have really whiny users add, change or subtly alter an average of 2.347 codes per week, no two versions with quite the same list, and each user wanting you to add 'their' special code just for their run?
>
>Wrap the sick second expression in ROUND( blah blah woof woof, 2).
>
>Hint - if nothing else, at least get rid of the freaking ORs and crap and use INLIST(), making it semi-readable and not a clear sign of impending brain death, just waiting to fall off the nearest cliff. Better would be to use a nice IN clause and make the thing readable and maintainable. But it's your code, not mine.
>
>And these are the suggestions when I'm being charitable. I'd be hard pressed to make it perform worse or make it less maintainable if I tried hard. Seriously. Doesn't
>
>

IIF(INLIST(t.transs,'"CS"','"DV"','"L "', '"R "','"RC"', '"RD"','"V "','"ST"','"OP"'),1,-1)

>
>at least look semi-sane and extensible? (INLIST() can only have up to 24 values, it is not optimizable, but it's infinitely better than the original)

Or:

IIF(alltrim(t.transs) $ "~CS~DV~L~R~RC~RD~V~ST~OP~", 1, -1)
this one is not limited to 24 values.

(You may use IIF("~" + alltrim(t.transs) + "~" $... so it does not mix up R and RC and RD codes). You would not need alltrim() if all codes could be 2 char in length (along with the code field).


Nick