Strange problem after use coalesce()

[db2group88]

hi, we are using db2 v8.2 EE on windows, with jcc driver. try to create
a join view, after that i check the syscat.columns table, for those
decimal field use COALESCE method, all the length become 11 instead of
the actual length of the field inside table, can anyone explain this to
me ? thank you

create view ML101PD.ARJMBAL1 (ARNM05, ARNO01, ARNM01, ARNO07, ARNO08,
ARNO09, ARFL17, ARFL03, ARMO12, ARDY12, ARCC12, ARYR12, ARNO15,
systimestamp, loginname, id ) as select ML101PD.ARPMCUS.ARNM05,
ML101PD.ARPMCUS.ARNO01, ML101PD.ARPMCUS.ARNM01, ML101PD.ARPMCUS.ARNO07,
ML101PD.ARPMCUS.ARNO08, ML101PD.ARPMCUS.ARNO09,
COALESCE(ML101PD.ARPMBAL.ARFL17, ' '), COALESCE(ML101PD.ARPMBAL.ARFL03,
' '), COALESCE(ML101PD.ARPMBAL.ARMO12, 0),
COALESCE(ML101PD.ARPMBAL.ARDY12, 0), COALESCE(ML101PD.ARPMBAL.ARCC12,
0), COALESCE(ML101PD.ARPMBAL.ARYR12, 0),
COALESCE(ML101PD.ARPMBAL.ARNO15, 0), ML101PD.ARPMBAL.systimestamp,
ML101PD.ARPMBAL.loginname, COALESCE(ML101PD.ARPMCUS.id,
ML101PD.ARPMBAL.id ) from ML101PD.ARPMCUS left join ML101PD.ARPMBAL on
ML101PD.ARPMCUS.ARNO01 = ML101PD.ARPMBAL.ARNO01 where
((ML101PD.ARPMBAL.ARFL03='Y')) and ((ML101PD.ARPMBAL.ARFL17<>'C'))

[jefftyzzer]

Looks like this is the default behavior. From page 115 of volume 1 of
the SQL Reference:

Operand 1: DECIMAL(w,x)
Operand 2: DECIMAL(y,z)
Result: DECIMAL(p,s) where p = max(x,z)+max(w-x,y-z)1s = max(x,z)

Perhaps an RTRIM is in order.

--Jeff

db2group88 wrote:

hi, we are using db2 v8.2 EE on windows, with jcc driver. try to create
a join view, after that i check the syscat.columns table, for those
decimal field use COALESCE method, all the length become 11 instead of
the actual length of the field inside table, can anyone explain this to
me ? thank you

create view ML101PD.ARJMBAL1 (ARNM05, ARNO01, ARNM01, ARNO07, ARNO08,
ARNO09, ARFL17, ARFL03, ARMO12, ARDY12, ARCC12, ARYR12, ARNO15,
systimestamp, loginname, id ) as select ML101PD.ARPMCUS.ARNM05,
ML101PD.ARPMCUS.ARNO01, ML101PD.ARPMCUS.ARNM01, ML101PD.ARPMCUS.ARNO07,
ML101PD.ARPMCUS.ARNO08, ML101PD.ARPMCUS.ARNO09,
COALESCE(ML101PD.ARPMBAL.ARFL17, ' '), COALESCE(ML101PD.ARPMBAL.ARFL03,
' '), COALESCE(ML101PD.ARPMBAL.ARMO12, 0),
COALESCE(ML101PD.ARPMBAL.ARDY12, 0), COALESCE(ML101PD.ARPMBAL.ARCC12,
0), COALESCE(ML101PD.ARPMBAL.ARYR12, 0),
COALESCE(ML101PD.ARPMBAL.ARNO15, 0), ML101PD.ARPMBAL.systimestamp,
ML101PD.ARPMBAL.loginname, COALESCE(ML101PD.ARPMCUS.id,
ML101PD.ARPMBAL.id ) from ML101PD.ARPMCUS left join ML101PD.ARPMBAL on
ML101PD.ARPMCUS.ARNO01 = ML101PD.ARPMBAL.ARNO01 where
((ML101PD.ARPMBAL.ARFL03='Y')) and ((ML101PD.ARPMBAL.ARFL17<>'C'))

[db2group88]

so you do rtrim(COALESCE(ML101PD.ARPMBAL.ARMO12, 0))?

[db2group88]

rtrim() is for string, how it works for decmial field? thanks

[Serge Rielau]

db2group88 wrote:

so you do rtrim(COALESCE(ML101PD.ARPMBAL.ARMO12, 0))?

CAST the 0 to the type of ML101PD.ARPMBAL.ARMO12. That should do it.
COALESCE(ML101PD.ARPMBAL.ARMO12, CAST(0 AS <..>))

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[db2group88]

i tried, and the length become from 11 to 5, but the actual field
armo12 in table arpmbal is 2.

Serge Rielau wrote:

db2group88 wrote:

so you do rtrim(COALESCE(ML101PD.ARPMBAL.ARMO12, 0))?

CAST the 0 to the type of ML101PD.ARPMBAL.ARMO12. That should do it.
COALESCE(ML101PD.ARPMBAL.ARMO12, CAST(0 AS <..>))

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Serge Rielau]

db2group88 wrote:

i tried, and the length become from 11 to 5, but the actual field
armo12 in table arpmbal is 2.

Can you clarify what you mean by length?
DECIMAL has scale and precision.
Which output are you looking at?
Can you post a complete example illustrating the problem? Including
CREATE TABLE and whatever you use to show the (bad) result.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[db2group88]

length is the column name for syscat.columns, our application need to
get the info to initialize the field properly,

here is the row i get from this table for the field ARMO12 with
tabname='ARPMBAL'

ML101PD ARPMBAL ARMO12 64 SYSIBM DECIMAL 2 0 Y 0 0 3 0 -1

2 is the length of the field.

the way i create the view is create view ML101PD.ARJMBAL1 (ARNM05,
ARNO01, ARNM01, ARNO07, ARNO08, ARNO09, ARFL17, ARFL03, ARMO12, ARDY12,
ARCC12, ARYR12, ARNO15, systimestamp, loginname, id ) as select
ML101PD.ARPMCUS.ARNM05, ML101PD.ARPMCUS.ARNO01, ML101PD.ARPMCUS.ARNM01,
ML101PD.ARPMCUS.ARNO07, ML101PD.ARPMCUS.ARNO08, ML101PD.ARPMCUS.ARNO09,
COALESCE(ML101PD.ARPMBAL.ARFL17, ' '), COALESCE(ML101PD.ARPMBAL.ARFL03,
' '), COALESCE(ML101PD.ARPMBAL.ARMO12, 0),
COALESCE(ML101PD.ARPMBAL.ARDY12, 0), COALESCE(ML101PD.ARPMBAL.ARCC12,
0), COALESCE(ML101PD.ARPMBAL.ARYR12, 0),
COALESCE(ML101PD.ARPMBAL.ARNO15, 0), ML101PD.ARPMBAL.systimestamp,
ML101PD.ARPMBAL.loginname, COALESCE(ML101PD.ARPMCUS.id,
ML101PD.ARPMBAL.id ) from ML101PD.ARPMCUS left join ML101PD.ARPMBAL on
ML101PD.ARPMCUS.ARNO01 = ML101PD.ARPMBAL.ARNO01 where
((ML101PD.ARPMBAL.ARFL03='Y')) and ((ML101PD.ARPMBAL.ARFL17<>'C'))

after i create the view, do the query on the syscat.columns with
tabname='ARJMBAL1'

ML101PD ARJMBAL1 ARMO12 8 SYSIBM DECIMAL 11 0 Y 0

you can see the field length is 11 instead of 2.

[jefftyzzer]

You'd cast your argument to RTRIM as a string, e.g.,
RTRIM(CHAR(your_value_expression)).

db2group88 wrote:

rtrim() is for string, how it works for decmial field? thanks

[Tonkuma]

here is the row i get from this table for the field ARMO12 with

tabname='ARPMBAL'

ML101PD ARPMBAL ARMO12 64 SYSIBM DECIMAL 2 0 Y 0 0 3 0 -1

2 is the length of the field.

the way i create the view is create view ML101PD.ARJMBAL1 (ARNM05,
....., ARMO12, ....) as select
ML101PD.ARPMCUS.ARNM05, .....
, COALESCE(ML101PD.ARPMBAL.ARMO12, 0),
....) from ML101PD.ARPMCUS left join ML101PD.ARPMBAL on
ML101PD.ARPMCUS.ARNO01 = ML101PD.ARPMBAL.ARNO01 where
((ML101PD.ARPMBAL.ARFL03='Y')) and ((ML101PD.ARPMBAL.ARFL17<>'C'))

after i create the view, do the query on the syscat.columns with
tabname='ARJMBAL1'

ML101PD ARJMBAL1 ARMO12 8 SYSIBM DECIMAL 11 0 Y 0

you can see the field length is 11 instead of 2.

My guess is as followings.
In the view, column ARMO12 is defined as
COALESCE(ML101PD.ARPMBAL.ARMO12, 0).
Data types of arguments are DEC(2,0) ("ARMO12") and INTEGER ("0").
Result of data type of COALESCE will be promoted highest precedence.
DECIMAL is precedent to INTEGER. So, INTEGER will promoted DEC(11,0).
Consequently, COALESCE(DEC(2,0), INTEGER) =COALESCE(DEC(2,0),
DEC(11,0)) =Result data type is DEC(11,0)

Please refer P94 Table 7. Data Type Precedence Table, P113-5 Rules for
result of data type and Function COALESCE in SQL Reference Vol1.

[Tonkuma]

Easiest way to keep precision may be.....
COALESCE(ML101PD.ARPMBAL.ARMO12, 0.)
[Add dot(".") to 0]