SQL Statement Timed Out

[tman24m]

I'm trying to execute an SQL statement against the 400 using ASP. There are over 5 million records in the file.

SELECT HTFCMP, HTFPSE, HTFMFM, HTFMFY, HTFMYR FROM P12569.HBTHT6 WHERE P12569.HBTHT6.HTDTDT BETWEEN 20020909 AND 20020913 AND P12569.HBTHT6.HTFWKC = '6LB'

Executes with no problem and within a second.

If I add a GROUP BY clause to the statement, the AS400 seems to ignore the access path and look throught every single record causing a timeout.

SELECT HTFCMP, HTFPSE, HTFMFM, HTFMFY, HTFMYR FROM P12569.HBTHT6 WHERE P12569.HBTHT6.HTDTDT BETWEEN 20020909 AND 20020913 AND P12569.HBTHT6.HTFWKC = '6LB' GROUP BY HTFCMP, HTFPSE, HTFMFM, HTFMFY, HTFMYR

IBM documentation is not much help. Anyone know anything about this?

Thanks
T Wayman

 

[MarcLodge]

Hi tman24m,
I think you are using the GROUP BY statement incorrectly. This function is usually used when counting the number of occurences within a set of data, or similar. For instance, if you had a table of products made up of PRODNO, DATECHNGD, PRICE and you wished to find how many times the price of each product had changed you would issue the SQL:
SELECT COUNT(*), PRODNO
FROM PRODTBL
GROUP BY PRODNO

I'm going to guess that you are probably trying to get rid of duplicates, in which case you probably want DISTINCT and to omit the GROUP BY statement.

Hope this helps, if not get back to me

Marc

 

[tman24m]

Thanks for the reply,

I should have mentioned that I had also tryed doing a SELECT DISTINCT with the same results. Our AS400 programmer , who knows very little about SQL, thinks that when using a GROUP BY or SELECT DISTINT, the SQL statement is ignoring the access path on the 400. I know very little about the 400 and am therefore at a loss for words.

I am having other problems as well. IBM has SQL documentation which supports a lot of the language I am used to using, however, when I try to execute a lot of the SQL statements, KEY WORDS are not recognized. Is it possible that there is an upgrade we need to get SQL working correctly on the 400?

Thanks again
T Wayman