Re: Help with SQL statement -- RPG400-L

Hi,

IMHO CTEs are at least more readeable.

When concatenating several fields no indexes can be used, because a "new"field is created and indexes only can be used for existing fields.But using GROUP BY may cause ALL rows being. Using DISTINCT may be thebetter solution, because it can work as SETGT/READ in RPG. And if there isan index that consists only of the selected fields an IOA (index only access= the fastest way to access data, because all necessary information is inthe key and no additional access to the rest of the row is necessary) can beperformed.


WITH grouped AS (SELECT Distinct <fields>
                FROM   <file>
                JOIN   <other file> on <join criteria>)

SELECT COUNT(*)
FROM   grouped

Event though at least the new SQL Query Engine (SQE) should be smart enoughtto realize that in this example not all rows have to be read.


Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (LesBrown)

"If you think education is expensive, try ignorance." (Derek Bok)

----- Original Message -----From: <AGlauser@xxxxxxxxxxxx>

To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Tuesday, December 05, 2006 21:57
Subject: Re: Help with SQL statement

Just for the sake of interest, I think that this would have solved Jay's
problem as well:

WITH grouped AS (SELECT <fields>
                FROM   <file>
                JOIN   <other file> on <join criteria>
                GROUP BY <fields>)

SELECT COUNT(*)
FROM   grouped


First of all, does that make sense (I didn't test it).  Secondly, are CTEs
more efficient that the combination of CONCAT and DIGITS?



#####################################################################################
Attention:

The above message and/or attachment(s) is private and confidential and isintendedonly for the people for which it is addressed. If you are not named in theaddressfields, ignore the contents and delete all the material. Thank you. Have anice day.


For more information on email virus scanning, security and content
management, please contact administrator@xxxxxxxxxxxx
#####################################################################################
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.