On 08-Jan-2015 08:51 -0600, Gqcy wrote:
I need to get length and width out of our item master via SQL..
database field defined: Packed 11,4 (IXXXX)
CREATE TABLE ...
( ...
, IXXXX DECIMAL(11, 4) /* 9999999.9999 */
)
old, <charles barkley>TERRIBLE</cb> desire usage of field:
width: positions 1-05, 3 decimal positions
length: positions 6-11, 3 decimal positions
Width declared as 05S03 [NUMERIC(5, 3)] and will be described with
the letter 'w' representing the numeric digits: WW.www
Length declared as 06S03 [NUMERIC(6, 3)] and will be described with
the letter 'l' representing the numeric digits: LLL.lll
scale line: ....+....1. /* positions 1 thru 11 */
as stored:: WWwwwLLLlll /* values: WW.www and LLL.lll */
Note: the storage does not include decimal separator
good ole RPGII code to do this:
C IXXXX MULT 10000 IXXXX0 11 0
The effect is that the 11P00 variable named IXXXX0=WWwwwLLLlll
C MOVEL IXXXX0 IWIDE 5 3
The effect is that the 05P03 variable named IWIDE=WW.www
C MOVE IXXXX0 ILENG 6 3
The effect is that the 06P03 variable named ILENG=LLL.lll
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;
The effect is that the 05S03 variable named Width=WW.www and the
06S03 variable named Length=LLL.lll
Thus with both the RPG and RPGIV, the separate data items stored
combined in the one field IXXXX is properly split into the two separate
variables within the program.
when I try the following:
select substr((IXXXX * 10000), 1, 5) as width
, substr((IXXXX * 10000), 6 ,6) as length
, IPROD
from ......
For best\consistent results, the expression (IXXXX*10000) should be
cast into the desired numeric type before using the SUBSTR scalar; the
implicit casting of that expression is to the numeric type DECIMAL(16,4)
and per implicit casting to character [effectively an implied CHAR()
casting scalar] of that result is a *left-justified* result. That means
if the value of the /width/ portion of IXXXX value is less than 10 or
the /length/ portion of the IXXXX value is less than 100, the SUBSTR()
will produce *incorrect* results.
That casting [noted above] is per the literal\constant value 10000
being typed as INT, but per multiplication with Decimal typed column, a
temporary copy of the INT is made into a P(length('10000'),0), thus we
have ["P(p,s)" implies Packed decimal of precision=p and scale=s]:
P(11,4)*P(5,0)
That means p=11, s=4, p'=5, s'=0 for the rule that the "precision of
the result of multiplication is min(mp,p+p') and the scale is
min(ms,s+s')" where mp is maximum precision and ms is maximum scale
[which are likely default of 31 per the Decimal Result Options
(DECRESULT) specification]. Thus the typing of the final result is
P(11+5,4) or P(16,4).
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzintdecoper.htm>
_Integer and decimal operands_
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzdecarithmetic.htm>
_Decimal arithmetic in SQL_
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...
Skip the arithmetic altogether, and obtain the required data from the
character representation of the number.
For the column IXXXX defined as DECIMAL(11, 4), the effect of the
expression DIGITS(IXXXX)='WWwwwLLLlll'; the same of DECIMAL(11,X) and
NUMERIC(11,X) as the DIGITS casting scalar returns just the digits
without any decimal separator or sign.
Thus the following obtains the whole\integer values as a string:
select substr(DIGITS(IXXXX), 1, 5) as width
, substr(DIGITS(IXXXX), 6 ,6) as length
...
To obtain the decimal values as a string with precision:
select insert(left (DIGITS(IXXXX), 5), 3, 0, '.') as width
, insert(right(DIGITS(IXXXX), 6), 4, 0, '.') as length
...
To obtain the decimal values as numeric with a precision, then one of
the following [where the first example establishes precision implicitly
according to the rules for the multiplication, but best to explicitly
specify what is desired]:
select dec(substr(DIGITS(IXXXX), 1, 5)) * .001 as width
, dec(substr(DIGITS(IXXXX), 6 ,6)) * .001 as length
/* note: the DEC casting scalar defaults to P(5, 0) */
...
select dec(insert(left (DIGITS(IXXXX), 5), 3, 0, '.'), 5, 3) as width
, dec(insert(right(DIGITS(IXXXX), 6), 4, 0, '.'), 6, 3) as length
...
midrange.com
