Problems with Data Type Conversion - CAST vs STR 3

[Windy77]

Sorry it’s a long explanation but I’ve tried to cover everything I’ve tried so far.

I’m trying to do a comparison between values in two fields (they’re supposed to match up), but one field is defined float(8), and the other varchar(50).

Using the code here (I’ve stripped out all unnecessary fields etc.), I get the error listed below.


SELECT lcd.ClaimNo, tf.WorkSourceRef
FROM lawCallDataRaw lcd
INNER JOIN tFile tf ON

lcd.ClaimNo = tf.WorkSourceRef

-- Where lcd.ClaimNo is the float(8) and tf.WorkSourceRef is the varchar(50)


Server: Msg 8114, Level 16, State 5, Line n
Error converting data type varchar to float.


So I tried to convert the float field to be varchar using CAST


SELECT lcd.ClaimNo, tf.WorkSourceRef
FROM lawCallDataRaw lcd
INNER JOIN tFile tf ON

CAST(lcd.ClaimNo AS VARCHAR(50)) = tf.WorkSourceRef


This works except the ClaimNo displays as "483120.0", so need to try & drop the " .0 ".


Reading the SQL Help I decided to try STR instead of CAST.


SELECT lcd.ClaimNo, tf.WorkSourceRef
FROM lawCallDataRaw lcd
INNER JOIN tFile tf ON

STR(lcd.ClaimNo, 50, 0) = tf.WorkSourceRef


This doesn't fail the syntax check, but doesn't extract any data. I did a separate SQL with only the lawCallDataRaw file to check the STR results and found that it extracted a field that is fixed 50 characters wide with 44 spaces before the 6 actual characters.


I also read in the SQL Help that it’s advisable to …

“Note To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.”


So, I’ve tried a variety of code looking something like this …

SELECT lcd.ClaimNo, tf.WorkSourceRef
FROM lawCallDataRaw lcd
INNER JOIN tFile tf ON

CAST((STR(lcd.ClaimNo, 50, 0)) AS VARCHAR(50)) = tf.WorkSourceRef

(this produced the same fixed 50 result as the STR on it’s own).


Variations on the attempts below produce Syntax Errors, so I have no idea what’s wrong …

CAST AS (STR(lcd.ClaimNo, 50, 0)) = tf.WorkSourceRef

CAST(lcd.ClaimNo AS VARCHAR(50)) AS (STR(lcd.ClaimNo, 50, 0)) = tf.WorkSourceRef



… there are no examples & I can’t get it to work … and since I’m pretty much a novice at SQL, then maybe it’s not the best way to do this after all.

Help would be very much appreciated.

 

[tlbroadbent]

The STR function right justifies the converted value. Try this.

SELECT lcd.ClaimNo, tf.WorkSourceRef
FROM lawCallDataRaw lcd
INNER JOIN tFile tf
ON ltrim(str(lcd.ClaimNo,50,0)) = rtrim(tf.WorkSourceRef)
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Windy77]

Thanks for your prompt response, I tried your suggestion and the result was exactly the same as the earlier solution (using CAST) in that the ClaimNo displays as "483120.0".

I could display / print the tf.WorkSourceRef instead of the ClaimNo, but if whatever is causing the extra " .0 " is doing something strange to the value (rounding ?) then I may end up missing some matches ? Or am I dreaming up problems here ?

(fyi - I'm in the United Kingdom near Manchester, so am leaving work for the weekend within half an hour so don't prioritise thoughts on this if you have more urgent tasks)

Again ... many thanks.

 

[tlbroadbent]

Try this.

SELECT lcd.ClaimNo, tf.WorkSourceRef
FROM lawCallDataRaw lcd
INNER JOIN tFile tf
ON ltrim(str(cast(lcd.ClaimNo as int),50,0)) = rtrim(tf.WorkSourceRef)
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Windy77]

When I just run this code to display the field from the file it works

SELECT LTRIM(STR(CAST(lcd.ClaimNo AS INT),50,0))

FROM lawCallDataRaw lcd


(but when I run "SELECT lcd.ClaimNo FROM lawCallDataRaw lcd" it displays with the " .0 ".

When I add it to the full query as you suggested then it still displays " .0 ".

I have to go home in 5 minutes, and I'm not back in work until Tuesday - I can now see the syntax of the combined STR / CAST commands so I have more to work with. Leave it with me and I'll try a few things and get back to you next week with an update.

Have a great weekend.

Thanks, Paul

 

[tlbroadbent]

I've muissed the point along the way. If you want the value to display as an interger you must convert it in the select list also. Changing it in the WHERE clause doesn't change the display.

SELECT Cast(lcd.ClaimNo As int), tf.WorkSourceRef
FROM lawCallDataRaw lcd
INNER JOIN tFile tf
ON ltrim(str(cast(lcd.ClaimNo as int),50,0)) = rtrim(tf.WorkSourceRef)
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

Terry as usual has the fix for your problem. Windy77, I'm sure you are working with a pre-exsitng design problem here, so I'm not expecting you to change the overall design. But for people reading this thread who are trying to learn, this is a good example of a problem caused by the database design.

If I have fields that will contain the same information in different tables that I will need to match up at some later point, I try to always define them with the same data type. This is not always possible if you are trying to connect two pre-existing databases together, but you should think about it when designing your tables.

A good way to ensure data type consistency for fields you know will go in multiple tables like phone number fields, or state abrreviations or even alpha numeric type identification fields (project number or in my case AirportID) is to set up a user defined data type and then use it whenever you need this kind of field.

Another thing to think about is which is the correct type to use for your information. Don't use a float data type if the data in it will always be integer data. Don't use a text data type if you are storing only numbers that might need calculations (or in comparison with a numeric field)later on.

I know I sometimes harp on design when all people want to do is solve their current problem. But poor design leads to a series of problems and something fixed in the design phase will save you 100-200 times the amount of time it would cost to fix it later or to keep inventing work-arounds. And these workarounds can little by little slow your system until it becomes annoying or even unuseable.

Think just about this case. From the information given, it seems this is integer data. Integer joins are the fastest joins you can get and now, not only are we not using the join on the integer, we have to perform conversion functions both to make the join work and to display the data. This type of query will take longer to process than a straight join with no data conversion would.

No big problem here running it just once (it still probably runs in milliseconds). But what happens when this is a web site and thousands of people are connected simultaneously and there are millions of records to evaluate? Especially if this is going to be a common join used in humdreds of places through the application. How happy is your boss gonna be two years after implementation when you tell him the only way to fix what is wrong is to completely redesign and rebuild the system or buy a whole lot more hardware to spread the load.

 

[Windy77]

Many thanks Terry for your persistence, my problem is now cured, and I've learned a lot.

I thoroughly agree with SQLSister's comments, I'm coming across several interesting design "features" like this one, but unfortunately I'm not in a position to do anything about them. At this stage I suppose the best thing to do is make a log of them all and make the database administrator aware of them. I am more familiar with AS/400 database but the principles of design and data type consistency apply in all instances.

Thanks again.