• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1596
  • Last Modified:

dataype problem

hi
i am moving data from AS400 db2 os/400 to db2 on windows

3708069.    0.0000         (source data)

i am creating tables in db2 windows and given datatype decimal for 3708069 and datatype decimal(5,9) for 0.000  it dont like it so i put bigint

?? when i put bigint its giving folllowing result only a number no . no .0000
wht datatype should i define i tried integer it dont like it decimal it dont like it ,it liked only bigint but result is messed plz advice

3708069    0 (target data)
0
bobby2929
Asked:
bobby2929
  • 4
  • 3
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:


decimal or real should work just fine.


Kent
0
 
bobby2929Author Commented:
its giving buffer overflow error
0
 
bobby2929Author Commented:
thts wht i am doing and thts the error
drop table STG_ORDERSHP1 ;

create table STG_ORDERSHP1 (                        
      COMPANY      varchar(      3      ),
      DIVISION      varchar(      3      ),
      ORDER_NUMBER      real,
      BACK_ORDER_LEVEL      real,
      WEIGHT_OF_ALLOC      decimal(      11,10)
);

insert into db2admin.STG_ORDERSHP1
(
SELECT
RRR_ORSHP.SHCMPX,
RRR_ORSHP.SHDIVX,
RRR_ORSHP.SHORDN,
RRR_ORSHP.SHBOLV,
RRR_ORSHP.HPRDWG
from rrr_orshp)
fetch first 10 rows only


drop table STG_ORDERSHP1 ;

create table STG_ORDERSHP1 (                        
      COMPANY      varchar(      3      ),
      DIVISION      varchar(      3      ),
      ORDER_NUMBER      real,
      BACK_ORDER_LEVEL      real,
      WEIGHT_OF_ALLOC      decimal(      11,10)
);

insert into db2admin.STG_ORDERSHP1
(
SELECT
RRR_ORSHP.SHCMPX,
RRR_ORSHP.SHDIVX,
RRR_ORSHP.SHORDN,
RRR_ORSHP.SHBOLV,
RRR_ORSHP.HPRDWG
from rrr_orshp)
fetch first 10 rows only


create table STG_ORDERSHP1 ( COMPANY      varchar(      3      ), DIVISION      varchar(      3      ), ORDER_NUMBER      real, BACK_ORDER_LEVEL      real, WEIGHT_OF_ALLOC      decimal(      11,10) )
DB20000I  The SQL command completed successfully.

insert into db2admin.STG_ORDERSHP1 ( SELECT RRR_ORSHP.SHCMPX, RRR_ORSHP.SHDIVX, RRR_ORSHP.SHORDN, RRR_ORSHP.SHBOLV, RRR_ORSHP.HPRDWG from rrr_orshp) fetch first 10 rows only
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0413N  Overflow occurred during numeric data type conversion.  
SQLSTATE=22003

SQL0413N  Overflow occurred during numeric data type conversion.

Explanation:

During processing of the SQL statement, an overflow condition
arose when converting from one numeric type to another.  Numeric
conversion is performed according to the standard rules of SQL.  

 Federated system users: numeric conversion can occur at the
federated server, at data sources, or both.  

 The statement cannot be processed.  No data was retrieved,
updated, or deleted.  

User Response:

Examine the syntax of the SQL statement to determine the cause of
the error.  If the problem is data-dependent, it may be necessary
to examine the data processed at the time of the error.  

 Federated system users: if the reason is unknown, isolate the
problem to the data source failing the request (see the problem
determination guide for procedures to follow to identify the
failing data source) and examine the data range restrictions for
that data source.  

 sqlcode :  -413

 sqlstate :  22003
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
bobby2929Author Commented:
sorry my source is
SHCMPX  SHDIVX  SHORDN     SHBOLV  HPRDWG         SHTXFA

W                R       3708069.    99.         0.000          0.0000

W                R       4309956.    98.         0.000          0.0000
0
 
ghp7000Commented:
i think it depends  lol on what the source data means
for example, does 3708069.  mean 3708069. 000?
does 99. mean 99.000?
if so, it is decimal (9,3) should work.
Make sure that your defintions for your other columns match the highest values for your source table, example
is the highest division number 999?
is the highest company number 999?
if the division and company values are of type number, then you have to cast these values first in order to insert them into the table.
0
 
bobby2929Author Commented:
hi ghp
whts wrong in this script i am trying everything with no sucess
plz advice

drop table STG_ORDERSHP1 ;

create table STG_ORDERSHP1 (                    
     COMPANY     varchar(     3     ),
     DIVISION     varchar(     3     ),
     ORDER_NUMBER     decimal(9,3),
     BACK_ORDER_LEVEL     decimal(9,3),
     WEIGHT_OF_ALLOC     decimal(9,3)
);

insert into STG_ORDERSHP1 (COMPANY  ,DIVISION  ,ORDER_NUMBER ,
    BACK_ORDER_LEVEL ,
     weiGHT_OF_ALLOC) values ('W', ' R ','3708069.', '99.','0.000');



SQL0408N  A value is not compatible with the data type of its
assignment target.  Target name is "ORDER_NUMBER                
                                          ".
0
 
ghp7000Commented:
sorry, didnt see properly your values
value 3708069. must be defined as decimal(10,3) and cannot be quoted, it is a number, not a string
so try this:
drop table STG_ORDERSHP1 ;
create table STG_ORDERSHP1
 (                    
     COMPANY                 varchar(3),
     DIVISION                  varchar(3),
     ORDER_NUMBER        decimal(10,3),
     BACK_ORDER_LEVEL  decimal(9,3),
     WEIGHT_OF_ALLOC   decimal(9,3)
);

insert into STG_ORDERSHP1 (COMPANY  ,DIVISION  ,ORDER_NUMBER , BACK_ORDER_LEVEL ,WEiGHT_OF_ALLOC) values                                ('W', ' R ', '3708069., 99.,  0.000);

note that when finished, the value 3708069. will be represented in the database as 3708069.000, if this is what you want, great. If you want 3708069. to be represented as a number, then change your table defintion to integer for this column and the value will be represented as 3708069 in the database (without the period)
0
 
ghp7000Commented:
sorry, should be
insert into STG_ORDERSHP1 (COMPANY  ,DIVISION  ,ORDER_NUMBER , BACK_ORDER_LEVEL ,WEiGHT_OF_ALLOC) values                                ('W', ' R ', 3708069., 99.,  0.000);
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now