I tried writing this code

update PRTempSeed
set FTaxable = (GrossWage) - (FEXemp * FPerEX),
STaxable = (GrossWage - (SEXemp * SPerEX) - SBaseEX,
CTaxable = (GrossWage) - (SEXemp * CPerEX) - CBaseEX,
SSTaxable =
case
when ((GrossWage + WYTD) <= 113500) then (GrossWage)
when ((WYTD) > 113500) then 0
else
(GrossWage + WYTD) - 113500
end

and I get this error

ElevateDB Error #700 An error was found in the statement at line 12 and
column 1 (Missing ))

What's wrong with this code

Ben

Hi Ben

This line:-

STaxable = (GrossWage - (SEXemp * SPerEX) - SBaseEX,

.... has two opening parentheses but only one closing parenthesis.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Thanks.


"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:D45BA365-B2D7-4F66-A30A-D4480DB4FA19@news.elevatesoft.com...
> Hi Ben
>
> This line:-
>
> STaxable = (GrossWage - (SEXemp * SPerEX) - SBaseEX,
>
> ... has two opening parentheses but only one closing parenthesis.
>
> Cheers
>
> Jeff
> --
> Jeff Cook
> Aspect Systems Ltd
> www.aspect.co.nz
>

How do I write this statement

update PRTempSeed
SET GrossWage =
case
When (< 0) then 0.00
else GrossWage
end

error= ElevateDB Error #700 An error was found in the statement at line 4
and column 8 (Expected expression but instead found <)


SET GrossWage =
(IF < 0)(GrossWage THEN 0.00 ELSE GrossWage)

error =  ElevateDB Error #700 An error was found in the statement at line 3
and column 5 (Expected ( but instead found <)

Ben

"Ben" <arrow1432@verizon.net> wrote in message
news:EAA4E387-D2A1-4D37-AE3D-B96267236BD0@news.elevatesoft.com...
> Thanks.
>
>
> "Jeff Cook" <jeffc@aspect.co.nz> wrote in message
> news:D45BA365-B2D7-4F66-A30A-D4480DB4FA19@news.elevatesoft.com...
>> Hi Ben
>>
>> This line:-
>>
>> STaxable = (GrossWage - (SEXemp * SPerEX) - SBaseEX,
>>
>> ... has two opening parentheses but only one closing parenthesis.
>>
>> Cheers
>>
>> Jeff
>> --
>> Jeff Cook
>> Aspect Systems Ltd
>> www.aspect.co.nz
>>
>
>

Ben

>How do I write this statement
>
>update PRTempSeed
>SET GrossWage case
> When (< 0) then 0.00
>else GrossWage
>end

SQL case statements don't work like the Delphi ones. You need to fully specify the condition so

update PRTempSeed
SET GrossWage case
When (GrossWage < 0) then 0.00
else GrossWage
end

but you could still have problems if GrossWage is null

UPDATE PRTempSeed
SET GrossWage = IF(GrossWage IS NOT NULL,IF(GrossWage < 0, 0.0, GrossWage),0))

totally untested though

Roy Lambert [Team Elevate]

Thanks for your reply.  Ity was very helpful.
I just did some more testing and this statement would be more proper

SET GrossWage =
case
When (GrossWage < 0) then 0.00
when (GrossWage Is Null) then 0.00
else GrossWage
end


BTW if I was going to convert a null to a 0 I also can use

set GrossWage = coalesce(GrossWaage,0)

Thanks again

ben

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:8D162BA0-BAED-4A0F-ABD9-B2E9AB096033@news.elevatesoft.com...
> Ben
>
>>How do I write this statement
>>
>>update PRTempSeed
>>SET GrossWage case
>> When (< 0) then 0.00
>>else GrossWage
>>end
>
> SQL case statements don't work like the Delphi ones. You need to fully
> specify the condition so
>
> update PRTempSeed
> SET GrossWage case
> When (GrossWage < 0) then 0.00
> else GrossWage
> end
>
> but you could still have problems if GrossWage is null
>
> UPDATE PRTempSeed
> SET GrossWage = IF(GrossWage IS NOT NULL,IF(GrossWage < 0, 0.0,
> GrossWage),0))
>
> totally untested though
>
> Roy Lambert [Team Elevate]