WHERE EXISTS

Hi Bob. Duh!! That would be better. I also noticed that there is an ALTSEQ keyword that I can use in the file that somehow ignores the case. Are you familiar with this? I was thinking that perhaps he could just create a logical by the fields he's trying to compare then just to a SETLL to see if the rcord exists. Not sure if this would work or if it'd be faster or slower than a where exists.

Pretty sure SQL ignores ALTSEQ. ALTSEQ applies to the keys of a PF or LF, not do the data itself. TRNTBL might come into play if you name an LF in a SQL statement, but that would be dynamic, so no faster than SQL UPPER().

I think Bob has it with the WHERE EXISTS. Unless you need the COUNT for some other reason, EXISTS will stop as soon as the count reaches 1 and return TRUE.

Here's a question, though: Do you have an index or indexes on nciadr by any or all of the fields in your WHERE tests? That could also speed things up greatly. Without supporting indexes, you may be doing table scans.

Hi Dale. No, I don't believe there's an index by the where clause fields. That would definitely help. I'm not sure how the WHERE EXIST would look being as I'm only dealing with one file. Can you give me an example? Thanks! BTW, I wasn't thinking of using the ALTSEQ LF with an SQL statement, I was thinking about using a SETLL instead.

ALTSEQ means (typically) that the key fields are converted to uppercase, but only in the key/index, not in the data portion of the file. 

Right. I dont' think ALTSEQ would help.

SQL example something like:

exec sql SELECT CASE
                  WHEN EXISTS (SELECT * FROM nciadr

                                 WHERE admrrn=:store
                                   AND upper(ademl1)=upper(:email)
                                   AND upper(adstfn)=upper(:fname)
                                   AND upper(adstln)=upper(:lname)
                              ) THEN '1'
                  ELSE               '0'
                END INTO :MyInd
           FROM SYSIBM.SYSDUMMY1;

SELECT needs a from clause, and the dummy table always provides exactly one row. The dummy table is a real table, and it has a single column, but you never need to refer to that column. Basically, this table lets you calculate any arbitrary SQL expression. Couldn't find a concrete definition of it, but it's all over the SQL reference in the examples of the built-in functions. Other SQLs have a similar table; I think in Oracle it's called DUAL.

If you put the SELECT in interactive SQL, and remove the INTO :MyInd, and supply a value where :store is, you would get a result table with one row, and one column, with either a '1' or a '0', which is the value that comes out of the CASE expression.