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

On 08-Jan-2015 09:04 -0600, Gary Thompson wrote:

On Thursday, January 08, 2015 7:51 AM Gqcy wrote:

I need to get length and width out of our item master via SQL..
database field defined: Packed 11,4 (IXXXX)

<<SNIP>> usage of field:
width: positions 1-05, 3 decimal positions
length: positions 6-11, 3 decimal positions
<<SNIP>>

Maybe use something like:

select cast ( dec13 * .001 AS DECIMAL(13,3) )
from library1/file1

where dec13 is a 13,0 Decimal number

the SQL cast is used to avoid truncation of field or column dec13

in STRSQL that select statement returns:
....+....1....+...
CAST function
1,100.001

To obtain the DEC(5,3) and DEC(6,3) from the noted positions within the DEC(11,4) data [per the OP], using a variation of the above alluded arithmetic, there is the following:

select
IXXXX /* the original DEC(11,4) values: WWwwwLL.Llll */
, cast ( IXXXX * .00001 AS DECIMAL(5 ,3) ) as width
/* the above expression extracts values: WW.www */
, cast ( 10 * (
IXXXX - ( 100000 *
cast ( IXXXX * .00001 AS DECIMAL(5 ,3) )
) ) AS DECIMAL(6, 3) ) as length
/* the above expression extracts values: LLL.lll */
/* by subtracting WWwww00 from WWwwwLL.Llll to get */
/* LL.Llll then multiply by ten to get the LLL.lll */
from ...

The complexity of the arithmetic for the LENGTH is why I would use the character casting to extract that value. I would use the character casting for both merely for consistency, regardless the above arithmetic expression for WIDTH is actually simpler. FWiW I would also use the DECIMAL() casting scalar rather than CAST, but only because IMO the expression DEC(...,P,S) is easier to read than the expression CAST(...AS...DECIMAL(P,S))