SQL "Exists" Question

Saml

unread,

Mar 18, 2004, 9:36:33 PM3/18/04

to

In SQL, if I just want to see if a record exists but don't need to read it
into a buffer, I can do that in Informix with:

if exists(select 'x' from employee_master where empid = 99999)

In RPG, I can use setll and not read the record.

Is it possible to achieve the same thing in RPG SQL?

Garry

unread,

Mar 19, 2004, 10:34:16 AM3/19/04

to

"Saml" <no...@nonesuch.com> wrote in message news:<z1t6c.276$oF....@eagle.america.net>...

You could do:

select count('x') from employee_master where empid = 99999

which would give you '0' if no such record existed.

HTH

Garry

Dieter Bender

unread,

Mar 19, 2004, 11:15:19 AM3/19/04

to

select empid from employee_master where empid = 999999 will give you an
sqlstate / sqlcode whe the record doesn't exist

Dieter

Jonathan Ball

unread,

Mar 19, 2004, 1:07:02 PM3/19/04

to

That will still read the record. I verified that it does.

I'm not sure why the OP doesn't want to read the record.

Saml

unread,

Mar 19, 2004, 8:00:01 PM3/19/04

to

Why don't we want to read the record? Efficiency.

We're looking for a generic solution that is efficient. There may not
always a unique key, so potentially more than one record could be accessed.
If you actually read data the result set could contain more than one row and
you must allow for this when you check SQLCOD or SQLSTT. Checking is not a
problem, but you really don't know what effort SQL will make, including
perhaps building a temporary result set, before it returns the first record.
The count(*) solution causes all the records that satisify the "where"
clause to be processed. (Not necessarily read--the optimizer may decide
that just reading the logical would do, but you should need to process all
records.)

The suggested solution that I've seen somewhere goes something like this:
select 1 from a_single_record_file
where exists (select 1 from myfile where a_where_clause)

Thhe theory is that SQLstops searching as soon as it finds any row that
satisfies the exists predicate, since "exists" is supposed to return true or
false. However, I still wonder about the efficiency of the second select
when more than one record satisifies a_where_clause and I'm wondering if
I've got it wrong.

Sam

"Jonathan Ball" <jon...@whitehouse.not> wrote in message
news:aFG6c.29092$%06....@newsread2.news.pas.earthlink.net...

> Garry wrote:
> > "Saml" <no...@nonesuch.com> wrote in message
news:<z1t6c.276$oF....@eagle.america.net>...
> >
> >>In SQL, if I just want to see if a record exists but don't need to read
it
> >>into a buffer, I can do that in Informix with:
> >>
> >> if exists(select 'x' from employee_master where empid = 99999)
> >>
> >>In RPG, I can use setll and not read the record.
> >>
> >>Is it possible to achieve the same thing in RPG SQL?

Elvis

unread,

Mar 22, 2004, 11:58:54 AM3/22/04

to

Since you are using embedded SQL you can use a single-fetch method
(check SQL Reference book for single-fetch).
Actually, I think I've seen some post where all they did was
interrogate the result of the prepare statement (didn't go to open
cursor even), but can't remember what exactly they were checking (file
exists vs. record exists).

I've tried playing with interactive sql (STRSQL) and this might work
(well, V5R2 at least as that's what I was playing with).

Select 1 from myFile
WHERE l_partkey = 191545
Fetch First row only
for read only
optimize for 1 row

For embedded sql, "FETCH FIRST FROM C1 FOR :x ROWS″ should work.

Elvis

"Saml" <no...@nonesuch.com> wrote in message news:<3JM6c.361$oF....@eagle.america.net>...

Saml

unread,

Mar 22, 2004, 8:04:40 PM3/22/04

to

I still think that there the possibility that SQL will generate the entire
result set, even if it only returns the first row.

Sam

"Elvis" <budi...@hotmail.com> wrote in message
news:e148ad68.04032...@posting.google.com...

Elvis

unread,

Mar 23, 2004, 3:27:21 PM3/23/04

to

You're probably right about the result set.
Why not use what you know works then (SETLL)?
Just write an RPG program or service program and create a UDF (User
Defined Function) that calls your UDF and returns TRUE or FALSE.

Elvis

"Saml" <no...@nonesuch.com> wrote in message news:<r3M7c.580$oF.1...@eagle.america.net>...

Dieter Bender

unread,

Mar 24, 2004, 2:56:29 AM3/24/04

to

Saml,

an open Cursor or a result set is just an open data path, opening a cursor
doesn't read anything, only fetch reads.

Dieter

Saml

unread,

Mar 24, 2004, 7:32:25 PM3/24/04

to

Consider what happens when you have a "Group By" clause. Before you get any
data back there may have been considerable IO. Remember we were looking for
a generic solution and that the sample in the original post was, perhaps,
simplisticly misleading.

Sam

"Dieter Bender" <dieter...@t-online.de> wrote in message
news:dir7j1-...@modula.bender-dv.de...

Dieter Bender

unread,

Mar 25, 2004, 4:46:45 AM3/25/04

to

Saml,

it happens what every time happens, when you open a cursor, the DBMS
makes the decision how to get the data, but it doesn't read any records at
this time.

Dieter Bender