On 28-Feb-2014 06:25 -0800, Hoteltravelfundotcom wrote:
I am getting an error when I try to make substring. The date we have
is 8 pos. yyyymmdd
I need to sort according to month. I get this error "SUBSTR value is
not allowed." This is because it is numeric and it's not allowed to
substr on numeric?
Field Expression Column Heading Len Dec
YYYYMM SUBSTR(IDDOCD, 1, 6) 06 00
Field Text Len Dec
IDORDT ORDER TYPE 3
IDDOCD DOCUMENT DATE 8 0
The error is QRY2248; implying the SUBSTR scalar requires as its
first argument, a character data type.
As others have noted but not explicitly stated\clarified, the
Query/400 feature does not have support for implicit cast between
numeric and character as does the DB2 for i SQL. But that is moot in
the given scenario for a variety of reasons:
• Implicit cast to character for the given numeric would yield
undesirable results for year values of before year 1000; likely not an
issue, but is a reason the DIGITS scalar is recommended.
• The described scenario per Expression shown, appears to want to
use year+month, presumably to define what to be used for collation; i.e.
not just month, as described in words.
• Unless there is a character collation that modifies the precedence
of the code points x'F0'-x'F9', there is little reason to order on
anything other than the original column, if the format is conducive;
YYYYMMDD is conducive for ordering without any modification.
• The next error will be QRY1327 because the Query/400 also does not
enable casting from character to either a different length character nor
to a numeric, per having specified a precision and scale; i.e. the
(06,00) len\dec specifications will have to be removed. However,
specifying the len+dec for a numeric Expression, will cause a cast of
the numeric result to Zoned Decimal.