Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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 November 28th, 2005, 06:39 AM
Authorized User
 
Join Date: Sep 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored procedure decimal precision

i have very simple SP:
Code:
CREATE PROC [dbo].[mySP]  
AS SELECT  ( DecimalValue1 * DecimalValue1 ) AS [COLUMN 1],  
( DecimalValue1 + DecimalValue1 ) AS [COLUMN 2] FROM mytab;
1)all DecimalValues are decimal(18,2) in DB table and i want to have a result with that precision
2)i want to "use" results from previous colums in next columns.
how to do it ?
3)how to reorder columns in dataset (by c# code)?

greeting from Poland
thx in A.
Filip


 
Old November 28th, 2005, 02:21 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

CREATE PROC [dbo].[mySP]
AS SELECT Convert(decimal(18,2),( DecimalValue1 * DecimalValue1 )) AS [COLUMN 1],
Convert(decimal(18,2), ( DecimalValue1 + DecimalValue1 )) AS [COLUMN 2] FROM mytab;


 
Old November 28th, 2005, 02:28 PM
Authorized User
 
Join Date: Sep 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by jbenson001
 CREATE PROC [dbo].[mySP]
AS SELECT Convert(decimal(18,2),( DecimalValue1 * DecimalValue1 )) AS [COLUMN 1],
Convert(decimal(18,2), ( DecimalValue1 + DecimalValue1 )) AS [COLUMN 2] FROM mytab;


convert so easy...and i did not know it:(
thanks

 
Old November 28th, 2005, 02:34 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Glad to help :)

Jim

 
Old November 28th, 2005, 03:24 PM
Authorized User
 
Join Date: Sep 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by jbenson001
 Glad to help :)

Jim

if u knew answer for 2) question...:)

 
Old November 28th, 2005, 03:59 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Can you give an example of what you are trying to do?

 
Old November 28th, 2005, 04:14 PM
Authorized User
 
Join Date: Sep 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by jbenson001
 Can you give an example of what you are trying to do?

i want to have column1 results in column2...
Example (doesn't work of course:):
CREATE PROC [dbo].[mySP]
AS SELECT ( DecimalValue1 * DecimalValue1 ) AS [COLUMN 1],
( DecimalValue1 + DecimalValue1*COLUMN1) AS [COLUMN 2] FROM mytab;

Column 2 use column1 results.


 
Old November 28th, 2005, 04:27 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

You can do excactly what you have written except that you cannot use column1, you need to use the actual column name.

 
Old November 28th, 2005, 04:43 PM
Authorized User
 
Join Date: Sep 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by jbenson001
 You can do excactly what you have written except that you cannot use column1, you need to use the actual column name.

so i should rewrite the same instructions...
i thought that it could be slower solution.


 
Old November 28th, 2005, 04:49 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Either use:
DecimalValue1 + DecimalValue1*(DecimalVal1*DecimalVal2) as col2

OR
Use a temp table
SELECT ( DecimalValue1 * DecimalValue1 ) AS [COLUMN 1], DecimalValue1, DecimalValue1
into #temp

select [column 1], DecimalValue1 + DecimalValue1 * [column 1] AS [column 2]
From #Temp





Similar Threads
Thread Thread Starter Forum Replies Last Post
decimal data type all 0's after decimal gobotsoup SQL Server ASP 1 February 28th, 2007 02:32 PM
Stored Procedure Help BukovanJ SQL Language 2 October 10th, 2006 08:02 AM
How to control the decimal point precision johnsonlim026 ASP.NET 1.0 and 1.1 Basics 0 August 7th, 2005 11:58 PM
stored procedure allang MySQL 3 January 26th, 2005 12:48 PM
Length vs precision in decimal datatype rvw SQL Server 2000 2 January 5th, 2004 12:36 PM





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