Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
Good morning. A co-worker of mine has a program with the following SQL statement in it. This program has been running for 2 days now and I'm wondering if it's because of this code. What he's doing is converting customer information from one system to another and the names, addresses, etc. can be any combination of upper and lower case, so he's trying to identify if the customer has already been written by converting everything to uppercase. I just wonder if, when processing against a large file, 800K records, if there is a simplier way of determining if a record exists. Here's his statement:
exec sql select count(*) into :count from nciadr where
admrrn=:store and upper(ademl1)=upper(:email) and
upper(adstfn)=upper(:fname) and
upper(adstln)=upper(:lname);
Does converting everything to uppercase before getting a count run slow? Any suggestions would be greatly appreciated.
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.