Path: utzoo!mnetor!uunet!lll-winken!lll-lcc!ames!hao!gatech!mcnc!decvax!mandrill!hal!ncoast!allbery
From: allbery@ncoast.UUCP (Brandon Allbery)
Newsgroups: comp.databases
Subject: Re: Informix 4GL Question?
Message-ID: <7444@ncoast.UUCP>
Date: 28 Feb 88 19:02:21 GMT
References: <714@uel.uel.co.uk> <2314@geac.UUCP>
Reply-To: allbery@ncoast.UUCP (Brandon Allbery)
Followup-To: comp.databases
Organization: Cleveland Public Access UN*X, Cleveland, Oh
Lines: 69

As quoted from <2314@geac.UUCP> by daveb@geac.UUCP (David Collier-Brown):
+---------------
| In article <714@uel.uel.co.uk> andrew@uel.uel.co.uk (Andrew Josey) writes:
| >let counter = 0
| >select count (*) into counter from table
| >  where table.column = key
| >if counter > 0
| >...
| >As some of my database tables are large, I would like to find
| >an alternative technique that terminates after finding the first
| >match instead of searching the whole table.
| 
|    This is interesting, in that it is a perfectly plausible request
| that does not **seem** to be well-defined in the relational
| algebra/calculus (and perhaps even less well-defined in the *#%!@?&&
| sublanguages we get with them).
+---------------

Dunno about relational calculus, but I would say:

SELECT COUNT(*) INTO :counter FROM DUAL
	WHERE EXISTS (SELECT 'x' FROM table WHERE table.column = :key);

This could be done from Informix-4GL if you create the DUAL table (a table
with one column containing one record, for the express purpose of performing
SQL operations that do not requite tables to execute but DO require a table
to satisfy SQL syntax).

create table dual (
	dummy char(1)
);
insert into dual values ("X");

The EXISTS operator doesn't have to search beyond the first match, and the
DUAL table should be fairly fast in use.  To make it faster, run the SQL
statement as:

	whenever error continue
	select "x" from dual where exists ...
	if status != 0 then ...

Now the SELECT succeeds and returns one record or fails and returns nothing.

That is actually how it would be done from Oracle, which is where I got
this trick from.

	EXEC SQL WHENEVER NOT FOUND GO TO notfound;
	EXEC SQL SELECT "x" FROM DUAL WHERE EXISTS (SELECT ...);
	/* records exist */
	
notfound:
	/* records don't exist */

For Accell types:  SET/SELECT does this automatically, see the manual.

	set $dummy to select #field from table where #field = $key;

Since the SET command can only handle one value if there is no EXECUTING
phrase, the first record found is returned and the SELECT statement stops
executing.  To see if it found anything, check the value of $dummy or the
value of status$().  (Set $dummy to UNDEFINED first if you check the value;
it'll be either UNDEFINED or the same as $key afterward.)  Of course, for the
case above the fastest way is to define an explicit relationship, after which
the delete operation will fail if there are any related records....

I admit that it's not the cleanest in the case of standard SQL, but it can
be done with some small measure of optimization.
-- 
	      Brandon S. Allbery, moderator of comp.sources.misc
       {well!hoptoad,uunet!hnsurg3,cbosgd,sun!mandrill}!ncoast!allbery