re{ SQL Query 
Author Message
 re{ SQL Query


Quote:
p) 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.

I have since lost the full blow note that you put on the net so the reply below
is based on the information above. I am assuming that you KNOW the field names
and the name of the table that you wish to get the row count from. 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 .....

If this is irrelevant for what you are trying to do then ignore it.

The pleasure is all mine.

Mark Denham
Shadowfax Systems Ltd
London



Fri, 14 Oct 1994 23:13:11 GMT
 re{ SQL Query


Quote:
>>   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.      #
#######     / /___ ______ #######                                         #

######    / / / // /_/ /   ######  US Mail: 11411 Chinden Blvd., MS 265   #
#######  /_/ /_// ____/   #######           Boise, Idaho   83707          #
########       / /      #########  Telnet:  1-323-3476                    #
#########     / /     ###########  FAX:     (208) 323-3319                #
############ /_/ ##########################################################



Mon, 17 Oct 1994 00:07:51 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Getting SQL queries to the Emulator SQL Query

2. UK query - Delphi3 - NT4-Memory leak on SQL Query to SQL/server 6.5

3. Subject: SQL Query - MAX, Grouping and Sub query

4. SQL Query to query a view with a changing name

5. Access Query to SQL Query

6. Converting MS Access Query to MS SQL Query

7. Query fails on VB6 but runs on VB5 and SQL Query Tool

8. Query works in SQL Query Analyzer but doesn't work in DTS

9. Difference between dynamic sql query and a normal query

10. SQL Query equivalent for MS Access Query

11. Running SQL Query outside Query Analyzer

12. Is there a wizzard that can convert my acces queries to sql queries [Imanewbie]


 
Powered by phpBB® Forum Software