Convert Varchar to number

Using SQL 2005. I have a field with dollar amounts but field type is VarChar. Need to convert to number to sum. What's the best way to do this or how can I sum a field type Varchar that has dollar amounts. Thank you, Davidfunction = cast
datatype = money
as in


declare @.c1 varchar(10), @.m1 money
select @.c1 = '123.45'
select @.m1 = cast(@.c1 as money)
select @.m1|||Thank you very much. Works great. David

Convert text to float in SQL Statement

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.|||Originally posted by Crespo-n00b
Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.

I've tried it and it still gives me a wrong answer.
I should have 45299 + 7832.5 = 53131.5, but I kept getting
13310.

Any more ideas?|||A Miracle ??

use pubs

create table answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

insert into answerint select '45299','7832.5'

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint

drop table answerint

Works for me though !!!|||Crespo and a mriacle in the same post...holy cow!

Where have you been hiding out?

And Yes float is quirky...

But I would add

USE Northwind

CREATE TABLE answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

INSERT INTO answerint
SELECT '45299','7832.5' UNION ALL
SELECT 'BRETT', 'KAISER'

SELECT SUM(
(Cast(Qty1 as float))
+ (Cast(Qty2 as float))
) as intAnswer FROM answerint
WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

DROP TABLE answerint|||Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.|||Originally posted by Crespo-n00b
Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.

Thanks for the replies.
I've tried it all out and they work for single select but not when a
"SUM" is used.

But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

Thanks

Convert SQL query to SP

I have the following sql query

select A,B,sum(C),D

from Y inner join X on..inner join Z on...

where A like ..., B between ...

group by A,B,D

as a result of this select I get list of A,B,sum(C),D values

Ho do I create a SP that updates some table W with list of selected A,B,sum(C),D values ?

Should I use cursor for this or not?

Thank you!!!!!

Enclose your update statement with Create Proc...

No need to have cursor to update your values (use Update...From syntax)

here the sample,

Code Snippet

Create proc YourSpname

as

Update sometable

set

sometablecol = sumofc

From

sometable main

Join

(

select A,B,D,sum(c) as sumofc

from Y inner join X on..inner join Z on...

where A like ..., B between ...

group by A,B,D

) as data

On data.a = main.a and data.b=main.b and data.d=main.d

Convert Query access cross tab to Query SQL Server 2005

Please help me

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

thank you

If you use SQL Server 2005,

Code Snippet

Declare @.PreparedStatement as varchar(1000);

Declare @.Query as varchar(1000);

Select @.PreparedStatement = ',[?]', @.Query ='';

Select @.Query = @.Query + Replace(@.PreparedStatement,'?',Year) From oilpalm Group By Year;

Select @.Query = Substring(@.Query,2,Len(@.Query))

Exec ('Select * from oilpalm PIVOT( SUM(Block) for Year In (' + @.Query + ')) as Pvt')

|||i'm sorry, but i sill don't understand with than query

Convert Oracle Trigger to MS SQL Server 2000

I am new to MS SQL and trying to get the syntax for the following trigger
correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
null ProjectSpec.ProjectValueFlag and update this sum to
ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
FOR INSERT, UPDATE, DELETE
AS
UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
(SELECT SUM(ProjectSpec.ProjectSpecValue)
FROM ProjectSpec
WHERE ProjectValueFlag IS NOT NULL
AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
AND ProjectSpec.ProjectID = :NEW.ProjectID)
WHERE ProjectInfo.ProjectID = :NEW.ProjectID--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use the inserted dataset. See the BOL article "Using the inserted and
deleted Tables" for more info.
Note: Most relational DB designers frown on putting calculated values
in tables, 'cuz it can cost so/too much to maintain (data layer). The
calculated value is, usually, only of interest when a query is run
(display layer). There are exceptions - accounting dbs: "closed"
months/years - it would be faster to read the monthly total from a pre
computed column than to read & calculate the column's data. Storage of
calculated values depends on your db usage (and willingness to violate
Normalization).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQje7cIechKqOuFEgEQLJXgCfUlbviSdhvBND
2nMVBVCyxOiq+gAAoLzn
IpSs8fhllDl6qE91JzvF8DD3
=bizF
--END PGP SIGNATURE--
Jason wrote:
> I am new to MS SQL and trying to get the syntax for the following trigger
> correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
> null ProjectSpec.ProjectValueFlag and update this sum to
> ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
> CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
> FOR INSERT, UPDATE, DELETE
> AS
> UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
> (SELECT SUM(ProjectSpec.ProjectSpecValue)
> FROM ProjectSpec
> WHERE ProjectValueFlag IS NOT NULL
> AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
> AND ProjectSpec.ProjectID = :NEW.ProjectID)
> WHERE ProjectInfo.ProjectID = :NEW.ProjectID|||Like the MGFoster said, I would frown upon such things, but you could
probably do something like:
CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
FOR INSERT, UPDATE, DELETE
AS
UPDATE ProjectInfo
SET ProjectInfo.SpecValueTotal =
(SELECT SUM(ProjectSpec.ProjectSpecValue)
FROM ProjectSpec
WHERE ProjectValueFlag IS NOT NULL
AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
--I don't think you need join to the new value here
-- AND ProjectSpec.ProjectID = :NEW.ProjectID
)
WHERE ProjectInfo.ProjectID in (select inserted.projectId
from inserted
union all
select
deleted.projectId
from deleted)
This will recalculate the sum for any rows that have been either deleted,
updated, or inserted, since the key will show up in the inserted table for
insert and update, and in deleted for update or delete. You want to include
all rows, not just new ones, so I commented out the one row.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:534AA117-3A7C-49C9-9F7D-1FAACD4C2A9C@.microsoft.com...
>I am new to MS SQL and trying to get the syntax for the following trigger
> correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
> null ProjectSpec.ProjectValueFlag and update this sum to
> ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
> CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
> FOR INSERT, UPDATE, DELETE
> AS
> UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
> (SELECT SUM(ProjectSpec.ProjectSpecValue)
> FROM ProjectSpec
> WHERE ProjectValueFlag IS NOT NULL
> AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
> AND ProjectSpec.ProjectID = :NEW.ProjectID)
> WHERE ProjectInfo.ProjectID = :NEW.ProjectID

Convert NULL value to INTEGER

hi all,
i have a problem with converting NULL value INTEGER.
i need to convert NULL value to 0 (zero)
this is my sample query :
SELECT SUM( qty ) AS qty FROM products WHERE id = 12121
i've tried these to
SELECT CONVERT( INT, SUM( qty ) ) AS qty FROM products WHERE id = 12121
SELECT CAST( SUM( qty ) AS INTEGER ) AS qty FROM products WHERE id = 12121
the problem came up when querying unexisting data ( 12121 is not
exists ).
can you help me.
thx,
aCeSELECT COALESCE(SUM( qty ),0) AS qty FROM products WHERE id = 12121
SELECT ISNULL(SUM( qty ),0) AS qty FROM products WHERE id = 12121
COALESCE is preferred, as it is standard SQL. It is also more
flexible than ISNULL as it can except more than two parameters; it
returns the first non-NULL parameter.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Oct 2007 11:12:26 -0700, aCe <acerahmat@.gmail.com> wrote:
>hi all,
>i have a problem with converting NULL value INTEGER.
>i need to convert NULL value to 0 (zero)
>this is my sample query :
>SELECT SUM( qty ) AS qty FROM products WHERE id = 12121
>i've tried these to
>SELECT CONVERT( INT, SUM( qty ) ) AS qty FROM products WHERE id =>12121
>SELECT CAST( SUM( qty ) AS INTEGER ) AS qty FROM products WHERE id =>12121
>the problem came up when querying unexisting data ( 12121 is not
>exists ).
>can you help me.
>thx,
>aCe

convert datatype from db2 to sql

i am running a package which extracts result of count(x), count(y),sum(z) from db2(as400) servers

i want to store the result of all the three in a table to a column of datatype varchar(25).

can someone help me with the convert/cast function in db2 which i can include in the query.

something like : select cast(count(x) as varchar(25)) from lib.file

No, but we can help you use SSIS to convert them...

Have you searched IBM's site for a SQL reference document containing commands?|||

yaa Phil,even i thought it is better to use a data converter transformation.

the problem iam running into is iam trying to store int and decimal datatypes into the same output column.

which is a varchar.

the convertor works fine for int datatype but is giving error for decimal.

"datatype of output column 'colname' doesnot mach with the datatype 'system.decimal' of the source column 'columnname' "

"component DataReaderSource failed validation and returned validation status VS_NEEDSNEWMETADATA"

what i understand is it not problem with the conver transformation its problem with the data source reader.

let me be more clear i have 6 querys running agains AS400 in a loop 1,2,4,5 querys give output in int and querys 3,6 give output numeric.

when i put each of this querys(1,2,4,5) in datasource reader the output datasource reader pics DT_I4 as the result datatype and for querys 3,6 it pics DT_NUMERIC

i think i am left with option to change in the query itself

help needed.... thanks in advance