Re: SQL digits throws SQLSTATE 22018 -- RPG400-L

Michael,

Haven't checked the thread throughly but, if you are at 7.1 you could use
the %SCANRPL BIF. If not, you could do something like this:

EXEC SQL Set :field1 = %Replace(:field1, '-', '');
EXEC SQL Set :field1 = %Replace(:field1, '/', '');

etc.,

At the end, you could do either cast FIELD1 as numeric or %DEC it (N=
%Dec(field1, 12, 0) ; )

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--

On Wed, Feb 16, 2011 at 6:37 PM, Koester, Michael <mkoester@xxxxxxxxxxxxx>wrote:

Steve,
I may end up going with something like that, Steve. Part of the
challenge is that the separators may include ( ), /. -, blanks, and who
knows what else. I was intrigued by the [perceived by me] power if the
DIGITS function to strip out the non-numeral stuff. It seems I had
expectations from seeing another use of it that lead me to believe it
could do more magic. My bad?

I may end up doing loop/check/replace through each of the fields with
RPG BIFs. I was just hoping for a short-cut.

Thanks for your efforts (in your second post as well).

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Needles,Stephen J
Sent: Wednesday, February 16, 2011 5:44 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL digits trows SQLSTATE 22018

Also

This will better deal with the empty field2 value. In this example,
field1 = 123-456-7890 and field2 = blanks.

select
field1,
case when field1 <> ' ' then
trim(substring(field1,1,locate('-',field1,1)-1) ||
substring(field1,5,locate('-',field1,4)-1) ||
substring(field1,9,4)) else '0' end,
field2,
case when field2 <> ' ' then
trim(substring(field2,1,locate('-',field2,1)-1) ||
substring(field2,5,locate('-',field2,4)-1) ||
substring(field2,9,4)) else '0' end

From testfile

This will return:
FIELD1 CASE expression FIELD2
CASE expression
123-456-7890 1234567890 0

The evaluation of the CASE function causes the default of '0' when
field2 is empty ("empty" means blank, not null).

Steve

-----Original Message-----
From: Needles,Stephen J
Sent: Wednesday, February 16, 2011 4:33 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: SQL digits trows SQLSTATE 22018

Michael,

I don't think that DIGITS is your answer. It assumes you will be
beginning with a numeric to start with. You didn't include your input
data, but let's assume the phone number character string is represented
as: 123-456-7890.

This is not a number, so DIGITS will fail. According to the manual: "The
DIGITS function returns a character-string representation of the
absolute value of a number."

It seems you wish to strip the '-' character. If this is true, one way
is to use the SUBSTR and LOCATE functions.

select field1, trim(substring(field1,1,locate('-',field1,1)-1) ||
substring(field1,5,locate('-',field1,4)-1) ||
substring(field1,9,4))
from testfile

I ran this against a table containing '123-456-7890' in the field1
column.

It returns:
FIELD1 TRIM function
123-456-7890 1234567890

Is this what you are after? There are probably other solutions that
will flood this thread by tomorrow. :-)

steve

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Wednesday, February 16, 2011 3:29 PM
To: RPG400-L@xxxxxxxxxxxx
Subject: SQL digits trows SQLSTATE 22018

I have an embedded SQL statement which I'm trying to use to parse phone
numbers from a few 13-character fields. What I have isn't working if
any one of the fields has no digits at all in it and throws SQLSTATE
'22081'. I guess I'm not too proficient yet with the DIGITS function,
so maybe someone can set me on the right track?

A simplified version would be as follows:

Exec sql
select substr(digits(field1),23,10),
substr(digits(field2),23,10)
into :target1,
:target2
From fileA
Where keyfield1 = :TheDesiredRecord;

The database fields (field1 and field2) are defined 13 A; the receivers
(target1 and target2) are both 10 A.
If all goes well, and field1 and/or field2 contain 7 or 10 digits, with
or without dashes, slashes, or other creative commentary, my receivers
would get the numerals only, or blanks if no digits exist in the
associated database field.

Of course, sqlstate 22018 keeps that from happening. Suggestions
welcome.

Thanks.
Michael Koester

--
This is the RPG programming on the IBM i / System i (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 communication, including attachments, is confidential, may be
subject to legal privileges, and is intended for the sole use of the
addressee. Any use, duplication, disclosure or dissemination of this
communication, other than by the addressee, is prohibited. If you have
received this communication in error, please notify the sender
immediately and delete or destroy this communication and all copies.

--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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.