place decimal for a char datatype

15 Jul 2005 9:51 PM

microsoft.public.dotnet.languages.vb

Hi All,

I am importing data from a mainframe output to SQl Server 2000
database.

I have some cost fields but I have made the field's datatupe in char,
so that I will not have any problem with populating the SQL Server
database (in the mainframe, it is signed numeric datatype).

here is table and sample data:

CREATE TABLE [dbo].[table1] (
[Claim_Number] [char] (9) NOT NULL ,
[Cost] [char] (10) NOT NULL

) ON [PRIMARY]
GO

Insert table1
select 'D2681445', '+000001225'
union all
select 'D2681446', '+000021708'
union all
select 'D2681447', '+000020570'

these costs are actually: 12.25, 217.08, 205.70

I would appreciate any suggestions with regards to this (after
populating either updating field's values as 12.25 or removing leading
zeroes and so on).

But I want to do query in such a way so that the results will be 12.25,
217.08, 205.70

Thanks a million in advance.

Best regards,

mamun

15 Jul 2005 10:19 PM

--CELKO--

Apparently these are fixed length columns and always positive

CAST (( SUBSTRING (foobar_cost, 2, 8) + '.' + SUBSTRING
(foobar_cost, 9,10) )
AS DECIMAL (10,2))

16 Jul 2005 4:34 AM

Louis Davidson

I think that should be spelled fubar_cost :)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1121465964.359218.61910@g49g2000cwa.googlegroups.com...
> Apparently these are fixed length columns and always positive
>
> CAST (( SUBSTRING (foobar_cost, 2, 8) + '.' + SUBSTRING
> (foobar_cost, 9,10) )
> AS DECIMAL (10,2))
>

15 Jul 2005 10:21 PM

Louis Davidson

So you would rather have problems every time you use the data? That seems
very odd to me. Cleaning up data and putting it into correct columns is
what ETL is all about.

The alterertative is to do your transformation every time you use the data:

select cast (substring(cost, 1,8) + '.' + substring(cost, 9,2) as numeric
(10,2))
from table1

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP

Show quote

"microsoft.public.dotnet.languages.vb" <mamun***@hotmail.com> wrote in
message news:1121464260.100799.136820@g43g2000cwa.googlegroups.com...
> Hi All,
>
> I am importing data from a mainframe output to SQl Server 2000
> database.
>
>
> I have some cost fields but I have made the field's datatupe in char,
> so that I will not have any problem with populating the SQL Server
> database (in the mainframe, it is signed numeric datatype).
>
>
> here is table and sample data:
>
>
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) NOT NULL ,
> [Cost] [char] (10) NOT NULL
>
> ) ON [PRIMARY]
> GO
>
>
> Insert table1
> select 'D2681445', '+000001225'
> union all
> select 'D2681446', '+000021708'
> union all
> select 'D2681447', '+000020570'
>
>
> these costs are actually: 12.25, 217.08, 205.70
>
>
> I would appreciate any suggestions with regards to this (after
> populating either updating field's values as 12.25 or removing leading
> zeroes and so on).
>
> But I want to do query in such a way so that the results will be 12.25,
> 217.08, 205.70
>
> Thanks a million in advance.
>
> Best regards,
>
> mamun
>