CODEWITHSUNDEEP
ibm-midrange
As4rikru
As4rikru

Reputation: 43

AS400 Define Query Results Convert a character field in to numeric and calculate

AS400 Define Query Results, Convert a character field in to numeric and calculate

In AS400 wrkqry and where i define query results i want to do following.

Character field from a PF with value 0,25 and convert it to a numeric field and add 1,0 so i get the results 1,25 in the numeric result field.

I cant change the physical file to make that field numeric.

Upvotes: -1

Views: 2313

Answers (2)

CoppoG
CoppoG

Reputation: 1

Ref. IBM doc

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

TIMESTAMP   TIMESTAMP('2003-01-01-00.00.00.00'||CHAR4)

NUMERIC     MICROSECOND(TIMESTAMP)

Example 2: CHAR6 is a 6-digit character field in the file. Define the following result fields:

TIMESTAMP   TIMESTAMP('2003-01-01-00.00.00.' || CHAR6)

TIMESTAMP2  timestamp(timestamp)

NUMERIC     microsecond(timestamp2)

The newly created NUMERIC field will be a BINARY data type.

Upvotes: 0

Charles
Charles

Reputation: 23783

I've never tried it, but I've heard you can convert char to numeric via the microsecond portion of a time stamp; then pull the microseconds back out as numeric.

Better solution would be to build a SQL View or DDS LF that converts the field to numeric; then use that in your Query/400 query.

Best solution, get rid of Query/400. Use an SQL based query tool. IBM Db2 Web Query for i is the modern replacement for Query/400.

But even 20 years ago, IBM offered the SQL based Query Manager (STRQM) tool that offered a prompted mode similar to Query/400.

edit
found an IBM doc that has the Query/400 conversion hack.

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

TIMESTAMP   TIMESTAMP('2003-01-01-00.00.00.00'||CHAR4)

NUMERIC     MICROSECOND(TIMESTAMP)

Upvotes: 1

Related Questions