Troubleshooting
Problem
This document uses the timestamp field to convert a character field to numeric with IBM Query for i (WRKQRY, Query/400).
Resolving The Problem
Note: Any additional support beyond this document might require a consulting agreement.
Question: Can IBM Query for I (WRKQRY) convert a character field to numeric?
Answer: Generally speaking, no. IBM Query for I (WRKQRY) does not provide a function that automatically performs this conversion. However, you can use the following method.
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 six digits of the microsecond are required. They must be numeric. If the field is less than 6 digits, the microsecond must be padded with zeros (0) to the left. For example, 0000xx would be correct 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.
After the time stamp is created, the MICROSECOND function can be used to retrieve the numeric representation of the character field.
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.
Question: Can IBM Query for I (WRKQRY) convert a character field to numeric?
Answer: Generally speaking, no. IBM Query for I (WRKQRY) does not provide a function that automatically performs this conversion. However, you can use the following method.
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 six digits of the microsecond are required. They must be numeric. If the field is less than 6 digits, the microsecond must be padded with zeros (0) to the left. For example, 0000xx would be correct 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.
After the time stamp is created, the MICROSECOND function can be used to retrieve the numeric representation of the character field.
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.
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SSB2F6","label":"Query for i"},"ARM Category":[{"code":"a8m0z0000000C4BAAU","label":"IBM i"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Historical Number
4892030
Was this topic helpful?
Document Information
Modified date:
26 November 2024
UID
nas8N1013639
Manage My Notification Subscriptions