On Mar 26, 6:41 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> On Mar 26, 5:53 pm, s..._at_uaex.edu wrote:
>
>
>
>
>
> > I have a table, there are more columns than this.
> > I used this to keep it simple.
>
> > ColA ColB Quantity
> > 30 24 2
> > 30 0 1
>
> > I want to do
>
> > UPDATE myTable SET Quantity= (SUM(Decode(ColA,NULL,0,0,0,1)
> > +Decode(ColB,NULL,0,0,0,1) )
>
> > I get an error message of :
> > group function is not allowed here
>
> > How can I count each Column that has a POSITIVE number and put the sum
> > in QUANTITY?
>
> > TIA
> > Steve
>
> Does this query have a group by clause?
>
> Are you trying to just count the positive values on each row into the
> quantity column of the same row or sum them for the entire table?
>
> I think posting the full where clause would be helpful to
> understanding your problem.
>
> The sign function returns -1 for less than zero, 0 for 0, and 1 for >
> 0 and in the past was often used in a decode to allow identifying
> relative value of the variables to each other. Howerver the CASE
> statement generally makes this unnecessary.
>
> select case when col1 <= 0 then 0 else 1 end ...
>
> See SQL manual.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
Mark, thanks for the suggestion:
Here's what works for me. I'm trying to update one row.
UPDATE myTable SET Quantity= (Decode(ColA,NULL,0,0,0,1)
+Decode(ColB,NULL,0,0,0,1) )
Received on Tue Mar 27 2007 - 07:12:40 CDT
