Using the CONVERT function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using the CONVERT function

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2004-12-03 : 16:40:35

Hey all, I am getting this error:

quote:
Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'HWFR.dbo.Material', column 'AvgCost'. Use the CONVERT function to run this query.

And my calculation are in my SP looks like this:

CASE WHEN tbl_material_used.mproduct = 1 THEN SUM(tbl_material_used.mprice)
	WHEN tbl_material_used.mproduct = 4 THEN SUM(tbl_material_used.mqty * Material.avgcost)
End as Price1

Well, the mqty is a nvarchar and the avgcost is a smallmoney data type. It is saying to use the CONVERT funtion... is this used in the select statement area? Can I do what I am trying to do with those different data types? Thanks...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 16:42:57

THEN SUM(CONVERT(int, tbl_material_used.mqty) * Material.avgcost)

What data type do you want to convert it to? I used int in the above.

Tara

hueby
Posting Yak Master

127 Posts

Posted - 2004-12-03 : 16:47:20

Ok.. I get it. Well.. i need to convert it to whatever will work with the money data type in the 'avgcost'. The 'mqty' is not always a whole number so I can't use integers correct(?). Is this where a float would come in or decimal?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 16:48:30

Yes. I would use decimal before using float.

Tara

hueby
Posting Yak Master

127 Posts

Posted - 2004-12-03 : 16:51:29

Got it. Thank you Tara.

Also.. I was wondering can I do something like:

CASE WHEN table = 1 or 2 or 3 THEN same function

or must i stay with

CASE WHEN table = 1 Then ..
2 then..
3 then..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 16:53:46

CASE WHEN ColumnName IN (1,2,3) THEN...

Tara

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-03 : 18:16:33

CASE WHEN ColumnName BETWEEN 1 AND 3 THEN

The BETWEEN predicate is usually a little faster than IN.
The IN predicate is usually faster than the OR operator.

FWIW

HTH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 18:19:43

Where did you read that IN is faster than OR? IN and OR are equal as SQL Server modifies the IN to OR at execution time.

Tara

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-05 : 08:08:53

Can't give you chapter and verse on where I found this tidbit of trivia. While the IN predicate is, in effect, a series of OR operations, the net effect is that the IN is faster. Run some teswts and you'll see.

HTH

=========================================
Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)
(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

Kristen
Test

22859 Posts

Posted - 2004-12-05 : 10:31:26

"Run some teswts and you'll see"

You gonna post them for Tara to try then?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources