Formatting Data

I have a table with two tables with colums that have a number value.
Example
Column One Column Two
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
4.0000000000000001E-2 4.0
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
I need to format the data so that the both tables and colums are the same.
Please help!!!!!!!!!!!!!!!!!!!1)What are the existing column data types?
2)If they are different , do an ALTER TABLE r=to make the same data type.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A8DAEF06-2F30-49D5-BBAA-87A37A0B5F52@.microsoft.com...
> I have a table with two tables with colums that have a number value.
> Example
>
> Column One Column Two
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> 4.0000000000000001E-2 4.0
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> I need to format the data so that the both tables and colums are the same.
> Please help!!!!!!!!!!!!!!!!!!!
>|||>> I need to format the data so that the both tables and colums are the same
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It looks like "number value" means a FLOAT and a
DECIMAL, so you are both vague and ambigous.
It also shows that the poster does not understand a tiered
artchitecture. Formattig is always done in the front end and never in
the database. Doesn't anyone learn basic programming concepts any
more?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141916365.071352.130990@.j33g2000cwa.googlegroups.com...
same. <<
> It looks like "number value" means a FLOAT and a
> DECIMAL, so you are both vague and ambigous.
CELKO, that statement is both repetative and redundant.
On a serious note, it is possible that the OP is trying to change the data
type to avoid problems with precision, although we can't be sure since they
are, in fact, both vague and ambiguous.|||Ok
I am totally new to SQL or any programming for that matter.
The Data Type is “Float” and Length is “8” this is the same for both
columns
My goal is to write a query that would give me the results where the values
are not equal
With the Data like this I am getting all rows when I run that query.
Thanks
Chris
"Jim Underwood" wrote:

> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1141916365.071352.130990@.j33g2000cwa.googlegroups.com...
> same. <<
> CELKO, that statement is both repetative and redundant.
> On a serious note, it is possible that the OP is trying to change the data
> type to avoid problems with precision, although we can't be sure since the
y
> are, in fact, both vague and ambiguous.
>
>|||That's a start. Now we know that both columns are floats and you are trying
to compare them, but the values stored are physically different by very
small (insignificant) amounts.
The question is, what difference is significant? Do you want these values
to be rounded to two decimal places then compared, or do you need precision
of 8 places?
What do the values actually represent? What are you doing with them, and
how are they being populated in the first place?
Properly understanding the problem and the desired results is crucial to
developing the right solution.
The following code will convert the values to decimal data type with 2
decimal values:
select cast(column1 as decimal(6,3)) as Column1, cast(column2 as
decimal(6,3)) as Column2 from SomeTable
where cast(column1 as decimal(6,3)) = cast(column2 as decimal(6,3))
When dealing with database questions, providing DDL and sample data is often
helpful as well.
Check out the following link for more details.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, I apologize if I insulted you. It was meant merely as a joke. As for
CELKO, don't worry about him, he beats up everyone who misunderstands SQL in
any way. Every time someone suggests that records and rows are the same
thing he drowns a kitten.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A154B16F-8E61-4714-85C0-7193EECE2FB8@.microsoft.com...
> Ok
> I am totally new to SQL or any programming for that matter.
>
> The Data Type is "Float" and Length is "8" this is the same for both
columns
> My goal is to write a query that would give me the results where the
values
> are not equal
> With the Data like this I am getting all rows when I run that query.
> Thanks
> Chris
>
> "Jim Underwood" wrote:
>
the
data
they|||This is how I wrote the query
select cast
([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
decimal(6,3))
and the result I am getting is as follows
Value P02_029_C0rterm-Rate
.036 3.580
.036 3.580
.040 4.000
.036 3.580
.036 3.580
.036 3.580
.040 4.000
.036 3.580
What I am doing wrong
Thanks
"Jim Underwood" wrote:

> That's a start. Now we know that both columns are floats and you are tryi
ng
> to compare them, but the values stored are physically different by very
> small (insignificant) amounts.
> The question is, what difference is significant? Do you want these values
> to be rounded to two decimal places then compared, or do you need precisio
n
> of 8 places?
> What do the values actually represent? What are you doing with them, and
> how are they being populated in the first place?
> Properly understanding the problem and the desired results is crucial to
> developing the right solution.
> The following code will convert the values to decimal data type with 2
> decimal values:
> select cast(column1 as decimal(6,3)) as Column1, cast(column2 as
> decimal(6,3)) as Column2 from SomeTable
> where cast(column1 as decimal(6,3)) = cast(column2 as decimal(6,3))
> When dealing with database questions, providing DDL and sample data is oft
en
> helpful as well.
> Check out the following link for more details.
> http://www.aspfaq.com/etiquette.asp?id=5006
> Also, I apologize if I insulted you. It was meant merely as a joke. As f
or
> CELKO, don't worry about him, he beats up everyone who misunderstands SQL
in
> any way. Every time someone suggests that records and rows are the same
> thing he drowns a kitten.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A154B16F-8E61-4714-85C0-7193EECE2FB8@.microsoft.com...
> columns
> values
> the
> data
> they
>
>|||Actually, I should have noticed this sooner...
If you look more closely at your original data...
Column One Column Two
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
4.0000000000000001E-2 4.0
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
Note the "E-2" at the end of the first column. This is scientific notation
indicating that the decimal point should be moved two places to the left.
The numbers in teh first row above are in fact 0.0358 and 3.580, so they
will not be made equal through rounding. in these examples, column2 =
Column1 * 100.
What is this data, where does it come from, and why are you comparing the
columns?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:18C6145C-5D4C-4CBD-8739-3D4C77CDF214@.microsoft.com...
> This is how I wrote the query
> select cast
> ([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
> decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
> where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
> decimal(6,3))
>
> and the result I am getting is as follows
>
> Value P02_029_C0rterm-Rate
> .036 3.580
> .036 3.580
> .040 4.000
> .036 3.580
> .036 3.580
> .036 3.580
> .040 4.000
> .036 3.580
> What I am doing wrong
> Thanks
> "Jim Underwood" wrote:
>
trying
values
precision
and
often
for
SQL in
are
the
since|||>> I am totally new to SQL or any programming for that matter. <<
Okay, I will go easier on you, but SQL is a reallllly bad first
programming language. It is a declarative language that is used only
for data base access. Powerful but strange to someone with only High
School algebra and business procedures for his model of algorithms.
You can do a simple "WHERE col1 <> col2" and SQL will follow the IEEE
Floating rules. The next question is, since this is your first
programming language, do you know how floating point numbers work?
You will need to research this.|||Jim,
I am not sure how I should write the query to do what you are saying.
We have to ERP system and I need to make sure the tax rate in each system
are the same. These rate change each month. Can you correst the following
query to get the result I need.
select cast
([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
decimal(6,3))
Thanks
You are the best
Chris
"Jim Underwood" wrote:

> Actually, I should have noticed this sooner...
> If you look more closely at your original data...
> Column One Column Two
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> 4.0000000000000001E-2 4.0
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> Note the "E-2" at the end of the first column. This is scientific notatio
n
> indicating that the decimal point should be moved two places to the left.
> The numbers in teh first row above are in fact 0.0358 and 3.580, so they
> will not be made equal through rounding. in these examples, column2 =
> Column1 * 100.
> What is this data, where does it come from, and why are you comparing the
> columns?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:18C6145C-5D4C-4CBD-8739-3D4C77CDF214@.microsoft.com...
> trying
> values
> precision
> and
> often
> for
> SQL in
> are
> the
> since
>
>