AW: SQL and host indicator

Hi,

Count(Distinct *) is not allowed.

But Count(*) and Count(Column) may return different results.
With Count(Column) rows with NULL values in the appropriate column are not
counted.
With Count(*) all rows are counted, even if all columns in a row are NULL.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Michael_Schutte@xxxxxxxxxxxx
Gesendet: Monday, July 21, 2008 20:23
An: RPG programming on the AS400 / iSeries
Betreff: RE: SQL and host indicator



But COUNT(DISTINCT ColumnName) could yield a different result than
COUNT(DISTINCT *) if the latter is even syntax correct.


Michael Schutte
Admin Professional
Bob Evans Bob-B-Q: You haven't had barbeque until you've had Bob Evans
Bob-B-Q. Try our six Bob-B-Q dishes, starting at $5.99. For more
information, visit www.BobEvans.com.


rpg400-l-bounces@xxxxxxxxxxxx wrote on 07/21/2008 02:00:22 PM:

Birgitta,

I don't believe this is the case anymore.

From the "High Performance Data Access Methods" presentation Q&A by

Dan Cruikshank, IBM and Elvis
Budimlic, CTI.

"Dan - No, the count(*) returns a derived column representing the
total number of rows in the result set based on row selection (ie
WHERE or ON clause settings). If no filtering is provided then the
count can be obtained from the table object itself in a single IO.

Elvis - COUNT(*) and COUNT(specificColumn) are functionally the same,
so there should be no performance difference, nor have we ever
observed a contrary case. In fact, SQE has a fast path for
COUNT usage with
no selection criteria (i.e. no WHERE clause). If you do have
selection criteria, it is far more
important to ensure you have proper indexes to help query optimizer
satisfy the selection criteria.

Dan - When and where to create an EVI depends mainly on your
application requirements. In my presentation I created an EVI to
obtain a fast

count."

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307
wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of BirgittaHauser
Sent: Monday, July 21, 2008 1:33 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: AW: SQL and host indicator

Hi,

If not necessary, I'd not use a count(*), because all rows must be

read.

If I only want to check if a row exists, I'll use the following
SELECT
statement:

Clear wPrvFL;
Exec SQL Select '1' into :wPrvFl
From ....
Where ...
Fetch First Row Only;

--> If a row is found wPrvFl is set to *On, otherwise it will not be
changed.
--> The executions stops after the first row is found.
--> An index only access might be performed.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

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

(Les

Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not
training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

Im
Auftrag von David FOXWELL
Gesendet: Monday, July 21, 2008 17:56
An: RPG programming on the AS400 / iSeries
Betreff: SQL and host indicator


Hi all,

I just compiled this. I just wanted to ask if this a perfectly
normal

way

to
set an indicator with SQL.

Thanks



D wPrvFl S N

EXEC SQL

SELECT

CASE WHEN COUNT(*) > 0 THEN '1' ELSE '0' END INTO : wPrvFl

FROM etc
--
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.


--
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.

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are
not a named addressee you are hereby notified that you are not
authorized to read, print, retain, copy or disseminate this
communication without the consent of the sender and that doing so is
prohibited and may be unlawful. Please reply to the message
immediately by informing the sender that the message was misdirected.
After replying, please delete and otherwise erase it and any
attachments from your computer system. Your assistance in correcting
this error is appreciated.
--
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.