Hi,
yet an other solution:
Select Itnbr, count(Distinct substr(stid, 1, 1))
From IDFile
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: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jeff Young
Gesendet: Wednesday, 21. January 2009 22:28
An: Midrange Systems Technical Discussion
Betreff: Re: SQL: How to get number of records (based on a prefix)
Peter,
Try this:
With Prefix as (select distinct itnbr,substr(stid,1,1) from IDFILE) select
itnbr,count(*) from prefix order by itnbr
Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions
V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical
Solutions Implementer V5R3
________________________________
From: "Peter_Vidal@xxxxxxxx" <Peter_Vidal@xxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Wednesday, January 21, 2009 4:20:44 PM
Subject: SQL: How to get number of records (based on a prefix)
Hi list:
I want to use SQL on this one. I have a file with item numbers and site
ids.
ITNBR = 15 characters
STID = 3 characters
The prefix of the STID defines the physical location of the site and
positions 2 and 3 may bring more data about sub-groups of that physical
location. I really do not care about the last 2 positions, only on the
prefix of STID.
The data on the file looks like this:
Item Site
Number ID
NHZF500-051 A1P
NHZF500-051 E1P
NHZF500-051 J1D
NHZF500-051 J1E
NHZF500-051 J1P
After the execution of the SQL, I want to have the following results:
NHZF500-051 3
I need to have only one instance of the item number and also I need to
have the number of times the prefix changed for that item (in this case
3).
Thanks in advance!
PETER VIDAL
MAPICS IT TECHNICAL SUPPORT TEAM / SR SYSTEM ANALYST
10540 Ridge Rd., Suite 203, New Port Richey, FL 34654-5111
Tel:727-849-9999, x2414 Fax:727-815-3120
WWW.PALL.COM
"Moral excellence comes about as a result of habit. We become just by
doing just acts, temperate by doing temperate acts, brave by doing brave
acts."
Aristotle
----------------------------------------------------------------------------
----
Attention:
This communication may contain information that is confidential,
privileged and/or exempt from disclosure under applicable law.
If you are not the intended recipient, please notify the sender
immediately and delete the original, all attachments, and all
copies of this communication.
----------------------------------------------------------------------------
----
midrange.com
