SQL Server field type decimal (18,6) before the decimal point is a few? Remember the database SP bug handling once

SQL Server field type decimal (18,6) before the decimal point is a few?

Admittedly, this is a very low-level problem ....

Why do you ask such a low-level question?

Because of this problem, an SP failed to perform a data import .... That bothered me for hours ....

The thing is this ...

Company headquarters on a set of Oracle ERP, our system some data to be taken from the inside, such as supplier,product.

Oracle exports the data files, and we brush with SSIS to the appropriate database locally (database fields are consistent with Oracle).

For us, the data source has changed, and we need to transform the Oracle data into the data we can use first.

Because Oracle on-line comparison "Fast", in order to make the existing system can basically run, colleagues write an SP conversion into the database of our system.

This SP is very coarse-written.

A colleague simply makes Oracle's data available as a field in the system after it is processed.

The SQL statement is this:

SELECT[Dt_uamt],[Dt_damt],[Dt_amt] intoTemp_dt_podt from (   NULL  asDt_uamt,Convert(varchar,Convert(decimal( -,6),IsNull(Usd_amount,'0')*  -)) asDt_damt,Convert(varchar,Convert(decimal( -,6),IsNull(Line_amount,'0')*  -)) asDt_amt fromOracleDB.[dbo]. Oracletb
)

Of course, there are more than three fields, and a table has nearly 100 fields, and this is just an example of the problem.

Colleagues here use the SELECT INTO statement.

The SQL SELECT into statement can be used to create a backup copy of the table.

This can be achieved quickly without a single table to build and ensure that the data can be fully exported.

But this is going to be a problem.

1 , first select into needs to be in the table does not exist, so every time you need to drop temp_dt_podt.  2. Because Oracle's field type is different from ours, it causes the type of field created to be different from the type of our original database.   3. When using null as into a field, the corresponding field type in the into table is int

The third article above will cause some before SP will error ....

You might say you don't need null as Dt_uamt with ' as Dt_uamt. Of course it's possible.

I also said that this SP is for the previous system can run up to write, some problems exist is normal .....

Then solve these problems and give it to me .... (I'm so bitter, I cry in the toilet ....) ）

After all, the SELECT INTO statement can be used to create a backup copy of the table, we brush the data or insert into the good.

Insert into is required for the table. A table of nearly 100 fields ... Altogether 10 several tables ..... It's impossible to build manually.

That's what I was thinking.

The table before into in SP is only the wrong field data type and the name is correct. I just need to change the field type of the original table to the right.

Well, suddenly feel a lot less work, haha.

I use the database task---Execute script to guide the structure of the table of the original SP into.

Then the table structure used in the original system is directed out.

Compare two files change the corresponding field type.

and record the Modified field, write out the corresponding SQL statement to modify.

I built a test database in the test database.

Build the SP into table through the export file.

Let's execute the SQL statement I tidied up.

Haha, is my job done?

Obviously not .... When I modify the original SP to change the select INTO to insert and execute .....

Unexpectedly error ....

An error occurred while varchar was converting to numeric. ”

What's the situation?

I changed a lot of decimal ... I'm going to try it one at a ....

I really tried it one after another. The result is that dt_damt this field is wrong.

Is it a data problem? Is there data in the data that is not numeric?

Select from   OracleDB. [dbo]. Oracletb  whereisnumeric(dt_damt)!=1

Results returned as empty ....

I'll go, it's all numbers.

What's the situation?

Is it longer than the length?

I don't think so. The Dt_damt field in the table that I built is the decimal (18,6) type.

And the SP written by the colleague also converted into decimal (18,6)

Convert (varchar,convert(decimal(6),isnull(usd_amount,'  0'* Dt_damt

(Amount ... It seems to have multiplied by 100. Didn't notice at that time)

I looked at the original SP into table Dt_damt type decimal (18,2)

I can 18,2 my decimal (18,6).

But it does not affect it, are all types of decimal (18,*), before the decimal point should be 18 bits.

I really thought that before.

So I was executing a SQL statement.

Select Convert (decimal(2),isnull(line_amount,'0' )  as Dt_damt  from OracleDB. [dbo]

No error ....

Select Convert (decimal(6),isnull(line_amount,'0'as  Dt_damt from OracleDB. [dbo]

It's an error.

An error occurred while varchar was converting to numeric. ”

So I decisive Baidu a Decimal

Introduction to Decimal data Types

Decimal is a data type for databases such as SQL Server, MySQL, and not part of a floating-point number, and can be defined with integer parts and bits of a decimal part. Using exact decimal types not only ensures more accurate data calculations, but also saves storage space, such as percentages using decimal (4,2To The storage data range is:-Ten^ -~Ten^ --1The fixed precision and number of decimal digits. A decimal type of data takes up 2~17 bytes. decimal[(p[, S])]p (number of significant digits) the maximum number of decimal digits that can be stored, and the left and right sides of the decimal points are included. The number of significant digits must be1To the maximum significant number of digits -Between the values. The preset number of significant digits is -. S (decimal places) The maximum number of decimal digits that can be stored to the right of the point. The number of decimal digits must be from0To the value of P. You can specify a number of decimal digits only if you specify a significant number of digits. The preset number of decimal digits is0therefore0<= s <=p. The maximum size of the storage will vary with the number of significant digits. Decimal (p,s) indicates that the number has n digits, where the integer p-S bit, fractional s bit. Cases:decimal(Ten,6), there are 10 digits in the numeric value, where integers are 4 bits and decimals account for 6 bits.

Decimal (10,6), a total of 10 digits in the numeric value, where the integer is 4 bits and the decimal number is 6 bits.

IsNumeric Introduction

Grammar IsNumeric (expression) parameter  expression to evaluate. return type   int Comment When input expression is counted as a valid integer, floating-point number,moneydecimalisnumeric 1 0 1 Ensure that expression can be converted to one of the above numeric types.

So record it so that you can make the same mistake later.

SQL Server field type decimal (18,6) before the decimal point is a few? Remember the database SP bug handling