Re: Set field length in SQL View

[Jeff Young <jyoung0950@xxxxxxxxx>]

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.