Re: bad, bad field misuse. need to turn into to SQL.

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

How's this?:

select cast(substr((IXXXX * 10000),1,5) as decimal(5,0)) *.001 as width,
cast(substr((IXXXX * 10000),6,6) as decimal(6,0)) * .001 as length
from ...



Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144


On 8 January 2015 at 09:51, Gqcy <gmufasa01@xxxxxxxxx> wrote:

> I need to get length and width out of our item master via SQL..
> database field defined: Packed 11,4 (IXXXX)
>
>
> old, <charles barkley>TERRIBLE</cb> desire usage of field:
>  width:  positions 1 -5, 3 decimal positions
>  length: positions 6-11, 3 decimal positions
>
> good ole RPGII code to do this:
>
> C  IXXXX  MULT  10000      IXXXX0  11 0
> C         MOVEL IXXXX0     IWIDE    5 3
> C         MOVE  IXXXX0     ILENG    6 3
>
>
> or, we also did...
> D            ds
> D IXXX4                 11  4
> D  Width                 5  3 overlay(IXXX4:1)
> D  length                6  3 overlay(IXXX4:6)
>
>
> C        IXXX4 = IXXXX;
>
>
> when I try the following:
>
> select substr((IXXXX * 10000),1,5) as width,  substr((IXXXX * 10000),6,6)
> as length, IPROD
> from ......
>
> I get my length and width as whole numbers, but when I try to push the
> math any further (by multiplying by .001), I get zeros in my fields...
>
>
> I know my penitence will be great for this...
>
>
> --
> 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.