RE: SQL to indicate existance of at least one line

Thanks, Vern,
I don't know how to do that. But I think I understand : isn't it the fact that my LF contains OMIT on a 3rd field, and this 3rd field isn't excluded in my SQL statement?

I just created the index advised by VE. Copied the existing DDS of the LF and took off the OMIT. Now, (according to VE ) the statement uses the index. I note also that I can't delete the LF again as it's locked by QZDASOINIT. VE? The lock only went when I closed down all my SQL script windows.

What I don't get now, is how is the statement so fast when it DOESN'T use the index? I have about 1000000 records but the result shows almost immediately.

David FOXWELL
Service Informatique
Tél : 03 90 23 91 63
david.foxwell@xxxxxxxxx

P*Pensez à l'environnement avant d'imprimer ce message

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg
Envoyé : lundi 19 janvier 2009 14:28
À : Midrange Systems Technical Discussion
Objet : Re: SQL to indicate existance of at least one line

David

I think you want the one that performs best - can you list the things that Visual Explain shows? There might be a way to see it in text form, as I recall.

Both should be able to use an index, if it exists.

By the way, you could also use

select distinct '1' from mytable where field1 = 'A' and field2='000002';

I don't know if that would require a table scan = VE would say what happened.

But please give us the details from VE - we can't speak to this without more information - look for a way t

David FOXWELL wrote:

Hi,

I wanted to initialize a boolean to indicate the presence of at least one line in my table.
I compared these 2 statements in Visual Explain, as I was unsure which to use in my RPG program.

select '1' from sysibm.sysdummy1
where exists (select * from mytable where field1 = 'A' and
field2='000002')

select '1' from mytable where field1 = 'A' and field2='000002' fetch
first row only;

The second statement gives a much simpler graphic, but I suspect that all the records that satisfy the test are copied into a temporary table. I don't understand the image produced for the first example.

Also, in my example, as I have an LF with a key field1, field2 and with OMIT on another field. I'm getting the message from VE that indexes exist but can't be used. The statement is however, very rapidly executed. Could this be that VE isn't working properly?

I prefer the second statement purely for the reason that I don't like having to make references to sysdummy1. Although, obviously, that's not a very good reason!