Re: Set field length in SQL View

Thanks to all.
I had originally tried the CHAR(Substr(coalesce....) option, but for some
reason it did not take.
I had the DDL for the view in a source file, and made (I thought) the
change there and used RUNSQLSTM to process it.


After seeing your replies, I checked the DDL again and for some reason, the
change I made to add the CHAR before the coalesce was missing!
I changed it again, and now it is working.

Thanks again.


On Tue, Sep 17, 2013 at 11:54 AM, TheBorg <sjl_abc@xxxxxxxxxxx> wrote:

COALESCE will pick the size of the larger field as the filed size. CAST
forces it to be CHAR(10):

CAST( SubStr( COALESCE(A.UDFBVALUE,' '), 1, 10) as CHAR(10)) as SCHEDB,



"TheBorg" wrote in message
news:mailman.8240.1379433148.9013.midrange-l@xxxxxxxxxxxx...

CAST the resulting value as CHAR(10)
-sjl


"Jeff Young" wrote in message
news:mailman.8239.1379432863.9013.midrange-l@xxxxxxxxxxxx...

All,
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) 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?

Thanks,

--
Jeff Young
Sr. Programmer Analyst

--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.