On 17 Sep 2013 08:47, Jeff Young wrote:
I have an SQL VIEW where I am selecting only part of a character
field using the SUBSTR function in a CTE, then using the derived
field in a Group By for the final select.
Part of the code is:
SubStr( COALESCE(A.UDFBVALUE,' '), 1, 10) as SCHEDB
My problem is that when I look at the file definition, the field
SCHEDB is defined as :
Data Field Buffer
Field Type Length Length
SCHEDB CHAR 40 42
What do I need to do to get this field defined as CHAR 10?
With the described effect, there would appear to be a defect with the
typing of that column. The effect of CHAR shown as the resultant data
type, seems to imply that the UDFBVALUE is either VARCHAR or CHAR; i.e.
the column SCHEDB should not indicate "Variable length field".
According to the SUBSTR scalar function documentation, the effect of
that expression should be a fixed-length vs a varying length character
"Data Type of the Result for SUBSTR" is "CHAR, if: length is
explicitly specified by an integer constant that is greater than zero."
While the type can be explicitly cast using CHAR casting function or
the CAST function, the literal value integer ten as the length-argument
specification of the SUBSTR scalar function, the type should implicitly
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
This mailing list archive is Copyright 1997-2013 by MIDRANGE dot 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 here. If you have questions about this, please contact