Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 12th, 2007, 11:15 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default Data conversion

Hi,

I have numeric values stored in Char(12) fields in TABLE1 that need to be Inserted/Converted into TABLE2 in numeric (decimal) fields.

I've written the following code:

Code:
INSERT INTO dbo.TABLE2
(
ITEM_QUANTITY,
ITEM_VALUE,
)
SELECT 
CAST(dbo.TABLE1.SOS_QUANTITY_12 AS DECIMAL),
CAST(dbo.TABLE1.SOS_VALUE_12 AS DECIMAL)
FROM 
dbo.TABLE1 
WHERE 
CAST(dbo.TABLE1.SOS_QUANTITY_12 AS DECIMAL) > 0.0000 AND 
CAST(dbo.TABLE1.SOS_VALUE_12 AS DECIMAL) > 0.00 
GROUP BY
dbo.TABLE1.SOS_QUANTITY_12,
dbo.TABLE1.SOS_VALUE_12
[u]TABLE1 fields:</u>
SOS_QUANTITY_12
is a CHAR field
Length 12
with example values such as (12.0204 and 10.23)

SOS_VALUE_12
is a CHAR field
Length 12
with example values such as (12.02 and 10.2)

PROBLEMS

1. is that values which are between 0 and 1 (i.e. 0.3 in TABLE1) are not Inserted into TABLE2.
2. is that the values like '2.3' in TABLE1 are being stored as whole numbers i.e. 2 TABLE2.

[u]TABLE2 fields:</u>
QUANTITY
is stored as
DECIMAL
LENGTH 9
PRECISION 18
SCALE 4

VALUE
is stored as
DECIMAL
LENGTH 9
PRECISION 18
SCALE 2

Any ideas please?

Thanks in advance.


Neal

A Northern Soul
__________________
Neal

A Northern Soul
 
Old May 14th, 2007, 12:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You are missing the parameters for DECIMAL() function there. It should be something like DECIMAL(10,2) or so. Moreover I don't see the need of CASE statements there. Why is that you used CASE statements there?

cheers.

_________________________
- Vijay G
Strive for Perfection
 
Old May 14th, 2007, 06:39 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default

Hi Vijay,

Thanks for the advice on the DECIMAL function... I'm obviously very sleepy!!

I'm using the CAST function not CASE, though.

Cheers,

Neal

A Northern Soul
 
Old May 14th, 2007, 07:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

That was my bad. sorry about that. May be I overlooked. Hope that worked.

_________________________
- Vijay G
Strive for Perfection
 
Old May 30th, 2007, 11:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Moderators,

Please deactivate this person's ID (choooper), who seems to be spilling spams all over this place.

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Conversion issue Uppa SQL Server 2000 0 February 12th, 2005 05:01 PM
Help with data type conversion please androoo Pro VB 6 2 December 24th, 2004 05:45 PM
Help with data type conversion please androoo Pro VB.NET 2002/2003 0 November 29th, 2004 09:06 AM
data type conversion Adam H-W SQL Server 2000 2 March 2nd, 2004 08:38 AM
Data Type Conversion owain SQL Language 5 October 31st, 2003 12:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.