I don't understand how this CAST query works

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

General SQL Server Forums

New to SQL Server Programming

I don't understand how this CAST query works

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-01-26 : 09:43:41

Hi there.

I have this query that is supposed to show a message when it finds something with a measurement of zero.

The query works but I don't understand WHY it works, especially to do with where CAST has been applied to the code to convert it to VARCHAR.

Why is it that it gives me a error message when I remove the CAST, the brackets surrounding the convert and the VARCHAR as part of the code?

The error message is: "Cannot convert is zero please set this to the to a numeric(30,6)

xxxxxxxxxxxxxxxxxxxxxxxxxx
I have three columns:

Site Code (Type: CHAR)
Asset Number (DECIMAL)
Message Text
xxxxxxxxxxxxxxxxxxxxxxxxxx

[b]The Query:[b/]

SELECT
feature.site_code,
feature.plot_number,
('The measurement ' + measurement_type.measurement_name + ' for Feature with Site Code ' + feature.site_code + ' and Plot Number ' + CAST(feature.plot_number as VARCHAR) + ' is zero please set this to the correct value') as message_text
FROM
feature,
feat_measurement,
measurement_type
WHERE
feature.site_code = feat_measurement.site_code AND
feature.plot_number = feat_measurement.plot_number AND
feat_measurement.measurement_code = measurement_type.measurement_code AND
feat_measurement.feature_quantity = 0

---------------------------------------------

I know its related to the data type Decimal for Plot number but I can't quite grasp the full picture of the error and how it links to the data types.

If anyone can help I'd really really appreciate it.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-26 : 09:48:58

Cast converts your number into a char to it can be included in a string...

And that's not an error message, it's the output of the query

And the reason is

feat_measurement.feature_quantity = 0

Which is funny because has nothing to do with

feature.plot_number

EDIT: Do you know who wrote this?

Plus it would be better to write it in ANSI SQL Joins...CAST happens to be ANSI...usually we use CONVERT..mostly cause of Dates

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-01-26 : 09:58:12

Hi Brett thanks for the reply.

Yep you are right, on the surface the measurement quantity has nothing to do with plot_number, its three waya part of a relationship between feature table and feat_measurement and measurement_code table where the zero is just part of the qualification.

Does this mean I can use CAST to convert a date data type into a string too? For example in my error message I want it to show the date when that item was created(?)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-26 : 10:01:05

"Does this mean I can use CAST to convert a date data type into a string too? For example in my error message I want it to show the date when that item was created(?)"

yes

Maverick_
Posting Yak Master

107 Posts

Posted - 2010-01-26 : 10:05:28

Thank you!

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:24:04

I think that CONVERT() is more normally used to convert a DateTime datatype to a String (than CAST) because CONVERT() can take an extra parameter to indicate what formatting style you want (the SQL Documentation BooksOnline has the details)

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-26 : 10:37:32

quote:
Originally posted by Kristen

I think that CONVERT() is more normally used to convert a DateTime datatype to a String (than CAST) because CONVERT() can take an extra parameter to indicate what formatting style you want (the SQL Documentation BooksOnline has the details)

BRILLIANT!!!!!

quote:
Originally posted by X002548
CAST happens to be ANSI...usually we use CONVERT..mostly cause of Dates

Brett

8-)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-26 : 10:38:01

TEST

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources