SQL statements needs change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

saad_tariq - 08 Jul 2008 21:14 GMT

Hi,

I am trying to use these two decode statements with SUM function, can someone
format it properly for me so that it does not give me a syntax error keeping
in view that the database version I am using is 10... Thanks...

Here is the sql line...

Sum(Decode(con.loaded_emp,"L" And boe.open=1,1,0)) AS BO

-- Sum(Decode(con.loaded_emp In ("E",Null) And boe.open=1,1,0)) AS BOP …
these two statements give me a syntax error.

Any help in this regard will be much appreciated ...Thanks very much

Reply to this Message

Serge Rielau - 09 Jul 2008 01:11 GMT

> Hi,
>
[quoted text clipped - 10 lines]
>
> Any help in this regard will be much appreciated ...Thanks very much

What you are trying to do is beyond DECODE. It's I'm afraid deep in
CASE-expression territory.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

bozon - 11 Jul 2008 14:28 GMT

> Hi,
>
[quoted text clipped - 13 lines]
> --
> Message posted via DBMonster.comhttp://www.dbmonster.com/Uwe/Forums.aspx/informix/200807/1

Yes, use the

case statement

sum(
case
when ( con.loaded_emp = "L" and boe.open = 1 ) then 1
else 0
end
) as BO

sum(
case
when ( (con.loaded_emp is null or con.loaded_emp = "E") and boe.open =
1) then 1
else 0
end
) as BOP

Reply to this Message