how can convert string to numeric with Query/400

5217 views
Skip to first unread message

Ufuk Özdemir

unread,
Aug 20, 1999, 3:00:00 AM8/20/99
to

how can convert string to numeric with Query/400

i want to convert string value to numeric value is it possible with  query/400

like DIGITS operator or other is it possible

 

Kevin Buckley

unread,
Aug 20, 1999, 3:00:00 AM8/20/99
to
There is a way to do it and it is spelled out on IBMs support web page.
(http://as400service.rochester.ibm.com/)
It uses the time function and converts the alpha to microseconds and then
back to numeric. Here it is:

The TIMESTAMP function accepts a character representation of a valid time
stamp. Because part of the time stamp is a 6-digit microsecond, use the
character string to be converted for the microsecond.

The date and time used is constant and is not important for the conversion.
The format of the microsecond is mmmmmm:

All 6 digits are required. They must be numeric. If the field is less that 6
digits, it must be padded with zeros (0) to the left. For example, 0000xx,
for a 2-digit field. If the field is more than 6 digits, do multiple
conversions and use numeric operations to calculate the correct value. If
the field requires a decimal portion, do the conversion. Then use numeric
operations to convert to the correct decimal value.

Once the time stamp is created, the MICROSECOND function can be used to
retrieve the numeric representation of the character field.
For example:

CHARACTER is a 4-digit character field in the file. Define the following
result fields:

TIMESTAMP TIMESTAMP('1995-01-01-12.00.00.00'??CHARACTER)

NUMERIC MICROSECOND(TIMESTAMP)

Ufuk Özdemir <uf...@singer.com.tr> wrote in message
news:37bd...@news.marun.edu.tr...

tun...@gmail.com

unread,
Sep 25, 2012, 11:58:23 AM9/25/12
to
This is not an answer for the question, but I wanted to share my comment on this. How can IBM become a worldwide leader while not providing such a simple conversion function to its users? It is a piece of toy for even the most simple development tools since 20-30 years ago. Isn't it strange for you too? IBM never deserved to be a world leader with its software solutions. Very primitive tools. Excuse me IBM, I am earning my life thanks to my AS/400 skills but, you should make this job easier for your customers.

CRPence

unread,
Oct 11, 2012, 1:15:43 PM10/11/12
to
On 25 Sep 2012 08:58, tun...@gmail.com wrote:
>> On Friday, August 20, 1999 3:00:00 AM UTC-4, Kevin Buckley wrote:
>>> There is a way to do it
>>> <<SNIP daft but functional though limited conversion ref\xmp>>
>>>
>> Ufuk Özdemir<uf...@singer.com.tr> wrote in message
>>
>> How can convert string to numeric with Query/400?
>> I want to convert string value to numeric value is it possible
>> with query/400 like DIGITS operator or other is it possible
>
> This is not an answer for the question, but I wanted to share my
> comment on this. How can IBM become a worldwide leader while not
> providing such a simple conversion function to its users? It is a
> piece of toy for even the most simple development tools since 20-30
> years ago. Isn't it strange for you too? IBM never deserved to be a
> world leader with its software solutions. Very primitive tools.
> Excuse me IBM, I am earning my life thanks to my AS/400 skills but,
> you should make this job easier for your customers.

The NewsServer I use never had the original message nor the 20-Aug
reply, so I will reply to this message.

Not strange to me at all. The Query/400 was designed to replace the
Query/36; a carryover until various SQL tooling could replace the simple
report writer and mail\merge features provided by the Query/400. The QM
Query facility [STRQM CL command] is tooling for SQL [including query
reporting] that can replace most usages of the Query/400. Use of the
Query/400 tooling had long been deprecated; at least since the
availability of QMQRY for which RTVQMQRY and STRQMQRY CL commands [both
with ALWQRYDFN(*ONLY), and the ANZQRY CL command] were made available
over 20 years ago to assist a transition to locally stored [not
client-based; e.g. via ODBC] SQL queries. A *QMQRY object could be
created to perform the request the OP expressed as desired effect.

However note that an SQL VIEW has long been able to effect the
requested conversion [asked of in the original message], and the
Query/400 query definition has always been able reference [to query] an
SQL VIEW as a Query Input Database File (QRYFILE parameter). The
database file would be named either on the RUNQRY or on the "Specify
file selections" of the WRKQRY command [or its equivalent, QRYRUN or QRY
in the S/36EE]. Depending on the requirements, the expression to define
the VIEW column [or QM query report column] might be as simple as using
the DECIMAL cast function:
DECIMAL(string_fld, 10, 2)

--
Regards, Chuck

jeffbl...@googlemail.com

unread,
Feb 5, 2016, 9:06:25 AM2/5/16
to
Yeah IBM. You are rubbish. This guy knows better.
How can you not provide a way to convert character to numeric. Well apart from several programming languages, Command language, SQL (both interactive and batch) and query manager. That's probably only about twelve ways. Hmmm. Maybe not such a bright question


Tom Black

unread,
Oct 5, 2021, 1:40:27 PMOct 5
to
> That's a ton of work and shouldn't be necessary for quick development, miss you man.
> Tom Black
Reply all
Reply to author
Forward
0 new messages