Using cast in case statement
I'm trying to use a case statement and I keep getting an error "800 Corresponding types must be compatible in CASE expression". The columns I have are: column1, integer column2, integer column3, decimal(12,2) column4, integer What I need to do is this.... case when column1 > 0 then sum(column2*column3) when column4 > 0 then sum(column4*column3) else "0.00" end I've also tried to do: sum(case when column1 > 0 then column2*column3 when column4 > 0 then column4*column3 else "0.00" end) I'm guessing I need to somehow cast the column1,2,4 to decimal(12,2) to make this work. I can't seem to figure out the cast syntax. Any help is greatly appreciated. Thanks, Jared
jared.hanks@gmail.com said: > I'm trying to use a case statement and I keep getting an error "800 > Corresponding types must be compatible in CASE expression". > > The columns I have are: > > column1, integer > column2, integer > column3, decimal(12,2) > column4, integer > > What I need to do is this.... > > case > when column1 > 0 > then sum(column2*column3) > when column4 > 0 > then sum(column4*column3) > else "0.00" Isn't the line above the problem? That looks like a character string to me. > end > > I've also tried to do: > > sum(case > when column1 > 0 > then column2*column3 > when column4 > 0 > then column4*column3 > else "0.00" > end) > > I'm guessing I need to somehow cast the column1,2,4 to decimal(12,2) > to make this work. I can't seem to figure out the cast syntax. > > Any help is greatly appreciated. Platform and version are always nice to know. -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Sorry - I'm lost with this example... So far I know: CASE is used to make different decissions based on the evaluation of a SINGLE variable/expression. What you do is make different decissions based on DIFFERENT variables/ expression - So, should you not use some kind of IF clauses ? CASE <main expression> WHEN <boolean expression addressing case main expression> <statements> (optional EXIT CASE) ... ... OTHERWISE ..<statements> END CASE Regards Hubert PS: I'm sure someone will correct me :-) On 16 Mrz., 07:56, jared.ha...@gmail.com wrote: > I'm trying to use a case statement and I keep getting an error "800 > Corresponding types must be compatible in CASE expression". > > The columns I have are: > > column1, integer > column2, integer > column3, decimal(12,2) > column4, integer > > What I need to do is this.... > > case > when column1 > 0 > then sum(column2*column3) > when column4 > 0 > then sum(column4*column3) > else "0.00" > end > > I've also tried to do: > > sum(case > when column1 > 0 > then column2*column3 > when column4 > 0 > then column4*column3 > else "0.00" > end) > > I'm guessing I need to somehow cast the column1,2,4 to decimal(12,2) > to make this work. I can't seem to figure out the cast syntax. > > Any help is greatly appreciated. > > Thanks, > Jared
h.hoelzl@querix.com said: > Sorry - I'm lost with this example... I assume it's SQL rather than 4GL. > So far I know: > > CASE is used to make different decissions based on the evaluation of a > SINGLE variable/expression. > > What you do is make different decissions based on DIFFERENT variables/ > expression - So, should you not use some kind of IF clauses ? > > > CASE <main expression> > WHEN <boolean expression addressing case main expression> > <statements> > (optional EXIT CASE) > ... > ... > OTHERWISE > ..<statements> > > END CASE > > Regards > Hubert > PS: I'm sure someone will correct me :-) > > On 16 Mrz., 07:56, jared.ha...@gmail.com wrote: >> I'm trying to use a case statement and I keep getting an error "800 >> Corresponding types must be compatible in CASE expression". >> >> The columns I have are: >> >> column1, integer >> column2, integer >> column3, decimal(12,2) >> column4, integer >> >> What I need to do is this.... >> >> case >> when column1 > 0 >> then sum(column2*column3) >> when column4 > 0 >> then sum(column4*column3) >> else "0.00" >> end >> >> I've also tried to do: >> >> sum(case >> when column1 > 0 >> then column2*column3 >> when column4 > 0 >> then column4*column3 >> else "0.00" >> end) >> >> I'm guessing I need to somehow cast the column1,2,4 to decimal(12,2) >> to make this work. I can't seem to figure out the cast syntax. >> >> Any help is greatly appreciated. >> >> Thanks, >> Jared > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.