G-Day all
Thanks for direction on this to Emmit, Jim, Ben, Stephan, and Dawn..
The correlation was the problem exactly. I guess I had the original table engrained
in my mind and couldn't see the forest for the trees.
What worked flawlessly is UPDATE lycopy03 SET totalamt = (t2.ctotals) + FROM lycopy03 t1, invsmrize t2 + WHERE t1.category = t2.category
Jim B. -- curious point you brought up. In converting the information from the view created with:
CREATE VIEW invsmrize (category, ctotals ) + AS SELECT category, SUM(cy_unit_cost * quantity) FROM tmdinv + GROUP BY category
The results in the ctotals column were really funky. One rows would look like 100045.54, another like 1999999.42334, another like 22.2344455533 and so on. Then when the update puts this info into the column defined as currency it all comes out to the penny.. We have to put a lot of trust in the behind the scenes conversions.. hmmmm...
Thanks all, problem solved Jim Limburg
Dawn Oakes wrote:
Jim, Try including the lycopy03 table with an identifier in your list of tables.
UPDATE lycopy03 SET totalamt = (T3.ctotals) FROM lycopy03 T1, tmdinv T2,
invsmrize T3 WHERE T1.category = T2.category and T2.catgegory =
T3.Category
Dawn
-----Original Message-----
From: Jim Limburg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 08, 2004 4:25 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Update -- NOT
Could someone fill me in on what I'm doing wrong here..
DROP VIEW invsmrize CREATE VIEW invsmrize (category, ctotals ) + AS SELECT category, SUM(cy_unit_cost * quantity) FROM tmdinv + GROUP BY category
UPDATE lycopy03 SET totalamt = (T2.ctotals) FROM tmdinv T1, invsmrize T2 + WHERE T1.category = T2.category
I am creating the view above to gather some totals, and then I just want to update a column in a table with the results. The lycopy03 table has just two columns Category (Text 15) and totalamt which is currency. The tmdinv table has several columns but the Category is Text 15, the cy_unit_cost is double and the quantity is real. In my view I get some ugly results, and I'm wondering if this is the culprit here.
Any help appreciated.
Jim Limburg
