Re: SQL in 4gl

On Thu, 02 Apr 1998 20:09:21 -0600, hashish1@mailcity.com wrote: >I have two sql statements embedded in the 4gl program, 1(sql1) for fetcing >the data and passing four at a time to the user and 2(sql2) for counting the >whole num of rec. In sql1 im joining 4 tables and selecting 5 common felds so >the record that i obtain appears redundant but if u select all feilds its >actually not, so i use 'select unique' to cut off the rows that appears to be >redundant. The problem comes when i use sql2(select count(*)) statement which >actually counts the 'appears to be redundant records' altogether so i get a >different number. I tried using 'count(distinct [field]) ' but no success, >since i have a composite of fields to be unique. Please help.... > >thank you > >-----== Posted via Deja News, The Leader in Internet Discussion ==----- >http://www.dejanews.com/ Now offering spam-free web-based newsreading I assume you do something like: select unique field1, field2 from sometable and select count(*) from sometable which doesn't give you the right results if field1 and field2 aren't the primary key of sometable or at least unique within the rows you select. (The fact that multiple tables are involved in your case is immaterial.) There is no simple solution to this in Informix SQL. It's a serious design flaw in SQL. The original designers of the language unfortunately didn't tink to hard about orthogonality (among many other things). In SQL 3 and possibly even at the higher level of SQL 92 you could have done something like this: select count(*) from select unique field1, field2 from sometable where the from clause essentially contained your first select statement to create a virtual table to count from. This isn't too bad, but unfortunately Informix haven't gotten arround to implement this yet. In the meantime you have at least these options: 1. Abandon the separate count altogether. In most cases we have found that such counts are done for not very important reasons and in a live database will give the wrong answer anyway (someone changed something between the count and fetch of data). 2. Create a view from your first select statement. Do a count on the view. 3. Create a cursor from your first select statement. Loop through this cursor (foreach or while/fetch) and count in code the number of rows fetched. An alternative here may be to fetch and use the actual data and count in the same loop. This of course depends on when and for what you need the count. 4. Use your first select statement to select into a temporary table. Count from this table. This solution can also be used to later fetch from the same temporary table. This also avoids problems with other people updating the database between a count and your fetch so the number of rows fetched will allways be equal the number counted. There are probably other solutions as well. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: Primary with ODBC info: http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html