Re: SQL performance question 2

David

I use SQL all the time, and I agree completely that it is NOT the tool for what your people are doing, especially the SETLL test for a record's existence - that is so well done in RPG, and it is at least clumsy in SQL.

It hurts nothing to have an F-spec for a file AND to use that file in a SELECT - FETCH loop - nice thing is, you already have all the field names, then.

I recommend putting *HIVAL into wKeyfield2, then use READPE with the same 2 key fields - the READP might not work, because you can't guarantee here that a record exists for wKeyfield1 - in fact, this can work as both your existence test and finding the max as you want. I think!

David FOXWELL wrote:

My second question :

I'm rather worried when I see this :


EXEC SQL

Select MAX (keyfield2) Into : wkeyfield2

>From Myfile

Where keyfield1 = : wKeyfield1;



To replace this :

SETGT (wKeyfield1 :wKeyfield2 ) MyFile

READP MyFile

To get the highest value of wKeyField2 for wKeyField1.

I would have thought that, on a large file, RPG would excel here.

I'd like your opinion.