I am doing:
WITH PREFIX AS
(SELECT DISTINCT ITNBR,SUBSTR(STID,1,1) FROM ITMRVA)
SELECT ITNBR,COUNT(*) FROM PREFIX ORDER BY ITNBR
... and it is giving me the error:
Token was not valid. Valid tokens: ( + * - ? : DAY RRN CASE CAST CHAR
DATE DAYS HASH HOUR LEFT TIME.
The cursor is placed between DISTINCT and ITNBR...
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
"Courage is the strength or choice to begin a change. Determination is the
persistence to continue in that change."
Anonymous
Jeff Young <cooljeff913@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/21/2009 04:29 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc
Subject
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
