Re: Simple use of substr problem

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
<<SNIP>>
ITTRND TRANSACTION DATE 8 0

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.