Re: re{ SQL Query

(From 100034.534@CompuServe.COM walt@mathcs.emory.edu (Mark Denham?) writes:) >> I have come across what seems like a simple problem but I can't >> think of an easy solution to it. >> >> I have a table with the following columns: >> FIELD1 >> FIELD2 >> FIELD3 >> >> and an Ace report select of: >> SELECT UNIQUE FIELD1, FIELD2 from table;>> >> Before I execute this report I would like to know how many rows >> will be selected but it seems impossible to use count as I >> cannot specify two fields on the UNIQUE. By the way I am using >> ESQL/C. > >Here is one approach you could try. It involves setting up a view that >performs the basic SELECT that you require: > > CREATE VIEW viewname AS > SELECT UNIQUE FIELD1, FIELD2, FIELD3 FROM tablename> >Once you have done this you can do things like: > > SELECT count(*) FROM viewname> > SELECT count(*) FROM viewname WHERE ..... Almost an excellent solution! :) It is simple and inventive but, unfortunately, not very efficient. Page 2-45 in my (now antiquated) 4.00 ISQL Reference Manual warns that selects on views may need to create a temporary table. This is the case in this situation. Furthermore, for some reason, that I have yet to understand, it appears that the view execution is less efficient than creating a temporary table manually and then selecting the count. Although not quite as simple, a short ESQL/C program to do what is required is still probably the best bet. Just my view (pun intented ;->). ############## __ ######################################################## ########### / / ############ Ron Whiteleather # ######### / / ######### Disk Memory Division - Media Mfg. # ####### / /___ ______ ####### # ###### / __ // __ / ###### email: ronw@hpdmmef.boi.hp.com # ###### / / / // /_/ / ###### US Mail: 11411 Chinden Blvd., MS 265 # ####### /_/ /_// ____/ ####### Boise, Idaho 83707 # ######## / / ######### Telnet: 1-323-3476 # ######### / / ########### FAX: (208) 323-3319 # ############ /_/ ##########################################################