On 05-Dec-2013 06:19 -0800, Hoteltravelfundotcom wrote:
I am trying to use in this field, ittrnd, it is yyyymmdd.
I need to total according to year/month
So I want to substring but this is not allowing me, any idea why?

Given ITTRND is defined in DDS as a numeric type with eight digits [e.g. 08P00] to represent digits in the date format YYYYMMDD, the attempt is apparently to define a Result Field in Query/400 that gives the value YYYYMM from that field. Instead of SUBSTR scalar which in Query/400 requires a /character/ as the first argument, use arithmetic; i.e. no reason to cast to character.

Field Name Expression Column Heading Len Dec
YYMM substr(ittrnd,1,6) yyyymm 6 00
<ed: above fails with QRY2248 "SUBSTR value is not allowed."

The above attempt is prevented because, unlike the DB2 for i SQL, Query/400 does not support *implicit casting* between numeric and character data types. All casting, what little is supported, must be done explicitly.

Field Text Len Dec

Field Name Expression Column Heading Len Dec
YYMM ittrnd/100 yyyymm 6 00

The above result field definition will divide by one hundred to make the DD portion of the numeric-date value become the value YYYYMM.DD which is implicitly cast to Zoned Decimal because of the Len specification. The Len=6 specification will allow for the remaining six significant digits YYYYMM, and the Dec=0 will cause the loss of the insignificant digits; i.e. drop the .DD as the tenths and hundredths.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.