Re: Using cast in case statement
ok, I've figured it out. i have: column1 (type integer) column2 (type integer) column3 (type decimal(12,2)) column3 is what was causing the problem, so i made column1 and 2 the same type as column3 by doing... sum(case when cast(column1 as decimal(12,2)) > '0' then column1*column3 when cast(column2 as decimal(12,2)) > '0' then -1*column2*column3 else cast("0.00" as decimal(12,2)) end) I have a table with customer orders. column1 is qty sold, while column2 is qty returned. column3 is price. since column1 and column 2 are never negative I was having a problem getting the correct amount sold/returned. thanks for your help. On Mar 16, 12:30 am, h.hoe...@querix.com wrote: > 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
jared.hanks@gmail.com wrote: > ok, I've figured it out. i have: The Clown is right (unfortunately he often is ;-), your real problems is the "0.00" in the else clause and the '0' in the WHEN clauses. They're not numbers but strings. This should work also and without all of the casts: sum(case when column1 > 0 then column1*column3 when column2 > 0 then -1*column2*column3 else 0.00 end) Now all of the results returned by the CASE...END are DECIMAL by default. RTFineM (Guide to SQL Reference): integer * decimal == decimal and the default type of literal numerics is also decimal. Art S. Kagel > column1 (type integer) > column2 (type integer) > column3 (type decimal(12,2)) > > column3 is what was causing the problem, so i made column1 and 2 the > same type as column3 by doing... > > sum(case > when cast(column1 as decimal(12,2)) > '0' > then column1*column3 > when cast(column2 as decimal(12,2)) > '0' > then -1*column2*column3 > else cast("0.00" as decimal(12,2)) > end) > > I have a table with customer orders. column1 is qty sold, while > column2 is qty returned. column3 is price. since column1 and column > 2 are never negative I was having a problem getting the correct amount > sold/returned. > > thanks for your help. > > On Mar 16, 12:30 am, h.hoe...@querix.com wrote: >> 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 > >
I removed the quotes and it worked great without the cast. I knew I was just doing something dumb. Thanks for the help. On Mar 16, 10:27 am, fred <f...@bloomberg.com> wrote: > jared.ha...@gmail.com wrote: > > ok, I've figured it out. i have: > > The Clown is right (unfortunately he often is ;-), your real problems is > the "0.00" in the else clause and the '0' in the WHEN clauses. They're > not numbers but strings. This should work also and without all of the > casts: > > sum(case > when column1 > 0 > then column1*column3 > when column2 > 0 > then -1*column2*column3 > else 0.00 > end) > > Now all of the results returned by the CASE...END are DECIMAL by > default. RTFineM (Guide to SQL Reference): integer * decimal == decimal > and the default type of literal numerics is also decimal. > > Art S. Kagel > > > column1 (type integer) > > column2 (type integer) > > column3 (type decimal(12,2)) > > > column3 is what was causing the problem, so i made column1 and 2 the > > same type as column3 by doing... > > > sum(case > > when cast(column1 as decimal(12,2)) > '0' > > then column1*column3 > > when cast(column2 as decimal(12,2)) > '0' > > then -1*column2*column3 > > else cast("0.00" as decimal(12,2)) > > end) > > > I have a table with customer orders. column1 is qty sold, while > > column2 is qty returned. column3 is price. since column1 and column > > 2 are never negative I was having a problem getting the correct amount > > sold/returned. > > > thanks for your help. > > > On Mar 16, 12:30 am, h.hoe...@querix.com wrote: > >> 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
fred said: > The Clown is right (unfortunately he rarely is ;-) Fixed that for you. ;o) -- 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.