news@jpusa1.UUCP (usenet) (08/15/87)
>From: barker@ems.MN.ORG (Bob W. Barker) >Subject: SQL help > >This SQL doesn't quite work: > >update cusstat >set CS_balance = > select CS_balance + A_dollars from cusstat, adjust > where CS_custname = A_custname and A_posted = 'N'/ > >-Bob Barker > > I've found working with SQL in both popular micro database management systems an adventure in semantics. First let me give you an example of what we have had to do when updating one table from another in a similar situation. ( NOTE: This is Informix syntax. ) update cusstat set balance = balance + (select A_dollars from adjust where cusstat.name = adjust.name) where cusstat.name in (select name from adjust where A_posted = "N"); The "set" you wish to work on must be qualified by the outer filter (where cusstat name in) before you begin the update. As one other gentleman pointed out - there can be no duplicate names in the adjust table, as the subquery will return them all, which of course will not be accepted by the set statement.(Errors!) Secondly - This is a prime example of the drawbacks of working with SQL to do (basically) procedural data manipulation. Has anybody else experienced a non-procedural crunch like this? What method have you chosen to take? I've suspected Query data-sub-languages are for just that - queries. The DML extensions to SQL are forced to work with domains qualified by filters rather than individual records, which can be cumbersome on a small machine with a large database. Any one have similar questions or answers? I am very new to working with databases and then mostly with micros, so I'm intrigued by simple discussions.... -Rick Mills Rick Mills JPUSA {gargoyle,ihnp4}!jpusa1!rick (312) 561-2450
hughes@tsunami.Berkeley.EDU (Eric Hughes) (03/17/90)
In article <9349@stiatl.UUCP> rk@stiatl.UUCP (Rajeev Kumar) writes: >I have a table with two fields: SuperGrp and SubGrp, with the >characteristics that a SubGrp can be a SuperGrp for other SubGrps. In other words, a directed graph. >"Retrieve all the SubGrps of @US". >By "all SubGrps" I mean if @US has a SubGrp which >also happens to be a SuperGrp, then retrieve the >SubGrps of that SubGrp and so on. This query is a form of a "transitive closure." In graph language, it means that you take a node, and come up with the set of all nodes which can be reached from the original. This can't be done in SQL. Date has a paper on this in his book _Selected Writings_. BTW, he thinks it is a deficiency. Eric Hughes hughes@ocf.berkeley.edu
davidm@uunet.UU.NET (David S. Masterson) (03/19/90)
In article <HUGHES.90Mar16200143@tsunami.Berkeley.EDU> hughes@tsunami.Berkeley.EDU (Eric Hughes) writes: In article <9349@stiatl.UUCP> rk@stiatl.UUCP (Rajeev Kumar) writes: >"Retrieve all the SubGrps of @US". >By "all SubGrps" I mean if @US has a SubGrp which >also happens to be a SuperGrp, then retrieve the >SubGrps of that SubGrp and so on. This query is a form of a "transitive closure." In graph language, it means that you take a node, and come up with the set of all nodes which can be reached from the original. This can't be done in SQL. Date has a paper on this in his book _Selected Writings_. BTW, he thinks it is a deficiency. This seems to be a fairly common problem in many different areas. One common area where this problem shows up is in the manufacturing of anything of composed of subparts which in turn are composed of other subparts. It is often important to be able to follow such a subpart graph through an unknown number of levels in order to determine a relationship (as in "What end-products are dependent on part X?" or "What parts go into the construction of product Y?"). Since its been brought up, I'd like to ask, given the deficiency in SQL, how are database vendors attempting to answer these problems? -- =================================================================== David Masterson Consilium, Inc. uunet!cimshop!davidm Mt. View, CA 94043 =================================================================== "If someone thinks they know what I said, then I didn't say it!"
rob@cs.fau.edu (Robert Rittenhouse) (01/31/91)
Is the following legal Ansi SQL: Given: Table Orders with attributes Productnumber, Price Table Inventory with attributes Productnumber, Cost the following query: SELECT SUM(Price), SUM(Cost) FROM Orders, Inventory WHERE Orders.Productnumber = Inventory.Productnumber The Microrim people claim it's not (which is their excuse for it returning incorrect answers in Rbase). I've looked in several texts and found neither anything forbidding it nor any examples! Rob R.