Re: SQL to only check for existence:

[vhamberg@xxxxxxxxxxx]

Are you sure there's no where clause? It's in the subquery (where exists...), I 
think.

The point is that "where exists" does not do a full table scan to return a 
result, therefore it is very fast.

I'm not sure whether the select into is faster - probably no noticeable 
difference.

To explain further, the "select 1 from..." is working against a single-row 
table, hence it returns a single value and can be assigned to the host variable 
(I think that makes sense). It'd probably fail if the "select 1 from..." 
returned multiple rows.

The idea is to test for records in a file, so that file name goes into the 
innermost select.

HTH
Vern

-------------- Original message -------------- 
From: "rick baird" <rick.baird@xxxxxxxxx> 

> Vernon, 
> 
> Your result would have been instant because you didn't specify a where 
> clause. 
> 
> do you think set :$itemfound (select 1... 
> will be faster than 
> 
> select '1' into :$itemfound..... 
> 
> ? 
> 
> thanks, 
> 
> Rick 
> 
> On 4/18/06, vhamberg@xxxxxxxxxxx wrote: 
> > Try pasting the following into an SQLRPGLE source and substitute your 
> > library 
> and file for qiws/qcustcdt. I ran it over a file with c.350,000 records, it 
> was 
> instant. 
> > 
> > di s 1p 0 
> > c/exec sql 
> > c+ set :i = (select 1 from sysibm/sysdummy1 
> > c+ where exists(select * from qiws/qcustcdt)) 
> > c/end-exec 
> > c eval *inlr = *on 
> > 
> > -------------- Original message -------------- 
> > From: "rick baird" 
> > 
> > > hey all, 
> > > 
> > > Is there a way to run an SQL statement that doesn't (necessarily) 
> > > return anything, but only checks for the existence of one or more 
> > > records based on a where statement - similar to a SETLL and %found? 
> > > 
> > > I've got a rather complicated SQL statement using: 
> > > LIKE '___abc___' or it could be: 
> > > LIKE ______xyz' 
> > > 
> > > - over a very large file, and I'm having performance issues. 
> > > 
> > > I don't want to do a count(), because I don't care how many, only that 
> > > at least one exists. with count, it would have to read through the 
> > > entire file to determine it. 
> > > 
> > > I also tried just doing a select - optimize for 1 row and a single 
> > > fetch, but that seemed to take forever too. It appears as if it is 
> > > searching the entire file, instead of stopping at the first one. 
> > > 
> > > help! 
> > > 
> > > Rick 
> > > 
> > > -- 
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> > > list 
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> > > To subscribe, unsubscribe, or change list options, 
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> > > Before posting, please take a moment to review the archives 
> > > at http://archive.midrange.com/midrange-l. 
> > > 
> > -- 
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> > To subscribe, unsubscribe, or change list options, 
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> > Before posting, please take a moment to review the archives 
> > at http://archive.midrange.com/midrange-l. 
> > 
> > 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>