Re: Substr question in Query 400

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.