Syntax advise please

Question

Syntax advise please

igloo21

Mr or Mrs. 500

Points: 534
More actions
March 4, 2014 at 4:35 am

#304700

Hello,
I'm trying to write a query and I'm receiving an error and I'm not sure how to get around it. When I run the code below I get an error saying:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
I don't want it to be datatype float- needs to be Char(8) or VarChar(8).
SELECT DISTINCT
TOP (100) PERCENT dbo.abc_Customers.iCustomer_id, dbo.abc_Customers.sCustomer_number AS CustomerNo,
CASE WHEN dbo.abc_Customers.sCustomer_number = 'SL000000' THEN '660' ELSE dbo.abc_Customers.sCustomer_number END AS sCustomer_number,
dbo.abc_Customers.sCustomer_name AS CustomerName, dbo.abc_Customers.sCustomer_state AS Customerstate, dbo.CustomerChannel.CustomerType AS Channel,
dbo.CustomerChannel.CustomersalesRegion AS Region, dbo.abc_Customers.dteffectivedate AS CustomerEffDt, dbo.abc_Customers.btCustomer_active AS Active,
dbo.abc_Customers.sCustomer_contact AS CustomerContact, dbo.abc_Customers.sCustomer_phone_number, dbo.abc_Customers.smobilephone
FROM dbo.abc_Customers
INNER JOIN dbo.CustomerChannel
ON dbo.abc_Customers.sCustomer_number = dbo.CustomerChannel.CustomerNumber
ORDER BY CASE WHEN dbo.abc_Customers.sCustomer_number = 'SL000000' THEN '660' ELSE dbo.abc_Customers.sCustomer_number END
Any assistance is appreciated!:-)

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you comment out the CASE in both the select and the where does the error go away?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

What is the defined datatype for column [sCustomer_number] in table [dbo].[abc_Customers] ?

I expect the error is caused by the CASE statement. Can you confirm this by removing both the CASE statements and run the query? I think it will run without error.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 3

GilaMonster (3/4/2014)
If you comment out the CASE in both the select and the where does the error go away?

Thanks for the reply, yes it goes away, but I need this to work with the CASE statement.

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 4

HanShi (3/4/2014)
What is the defined datatype for column [sCustomer_number] in table [dbo].[abc_Customers] ?
I expect the error is caused by the CASE statement. Can you confirm this by removing both the CASE statements and run the query? I think it will run without error.

Thanks for the reply, the data type is Char(8)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Try this (a bit simplified and cleaned up)

SELECT DISTINCT

c.iCustomer_id ,

c.sCustomer_number AS CustomerNo ,

CASE WHEN c.sCustomer_number = 'SL000000' THEN CAST('660' AS CHAR(8))

ELSE c.sCustomer_number

END AS Customer_number ,

c.sCustomer_name AS CustomerName ,

c.sCustomer_state AS Customerstate ,

cc.CustomerType AS Channel ,

cc.CustomersalesRegion AS Region ,

c.dteffectivedate AS CustomerEffDt ,

c.btCustomer_active AS Active ,

c.sCustomer_contact AS CustomerContact ,

c.sCustomer_phone_number ,

c.smobilephone

FROM dbo.abc_Customers c

INNER JOIN dbo.CustomerChannel cc ON c.sCustomer_number = dbo.CustomerChannel.CustomerNumber

ORDER BY Customer_number

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SELECT DISTINCT TOP (100) PERCENT

c.iCustomer_id,

c.sCustomer_number AS CustomerNo,

CASE WHEN c.sCustomer_number = 'SL000000' THEN 660 ELSE c.sCustomer_number END AS sCustomer_number,

c.sCustomer_name AS CustomerName,

c.sCustomer_state AS Customerstate,

cc.CustomerType AS Channel,

cc.CustomersalesRegion AS Region,

c.dteffectivedate AS CustomerEffDt,

c.btCustomer_active AS Active,

c.sCustomer_contact AS CustomerContact,

c.sCustomer_phone_number,

c.smobilephone

FROM dbo.abc_Customers c

INNER JOIN dbo.CustomerChannel cc

ON c.sCustomer_number = cc.CustomerNumber

ORDER BY CASE WHEN c.sCustomer_number = 'SL000000' THEN 660 ELSE c.sCustomer_number END

-- If you don't need DISTINCT, remove it because it has a cost.

-- Why the TOP (100) PERCENT?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 7

ChrisM@Work (3/4/2014)
SELECT DISTINCT TOP (100) PERCENT c.iCustomer_id, c.sCustomer_number AS CustomerNo, CASE WHEN c.sCustomer_number = 'SL000000' THEN 660 ELSE c.sCustomer_number END AS sCustomer_number, c.sCustomer_name AS CustomerName, c.sCustomer_state AS Customerstate, cc.CustomerType AS Channel, cc.CustomersalesRegion AS Region, c.dteffectivedate AS CustomerEffDt, c.btCustomer_active AS Active, c.sCustomer_contact AS CustomerContact, c.sCustomer_phone_number, c.smobilephone FROM dbo.abc_Customers c INNER JOIN dbo.CustomerChannel cc ON c.sCustomer_number = cc.CustomerNumber ORDER BY CASE WHEN c.sCustomer_number = 'SL000000' THEN 660 ELSE c.sCustomer_number END -- If you don't need DISTINCT, remove it because it has a cost.
-- Why the TOP (100) PERCENT?

Still getting the same float error:

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Did you try mine?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 9

igloo21 (3/4/2014)
HanShi (3/4/2014)
What is the defined datatype for column [sCustomer_number] in table [dbo].[abc_Customers] ?
I expect the error is caused by the CASE statement. Can you confirm this by removing both the CASE statements and run the query? I think it will run without error.
Thanks for the reply, the data type is Char(8)

Thanks but unfortunately I get the same float error 🙁

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

sCustomer_number is float:

SELECT DISTINCT TOP (100) PERCENT

c.iCustomer_id,

c.sCustomer_number AS CustomerNo,

CASE WHEN c.sCustomer_number = 1 THEN 660 ELSE c.sCustomer_number END AS sCustomer_number,

c.sCustomer_name AS CustomerName,

c.sCustomer_state AS Customerstate,

cc.CustomerType AS Channel,

cc.CustomersalesRegion AS Region,

c.dteffectivedate AS CustomerEffDt,

c.btCustomer_active AS Active,

c.sCustomer_contact AS CustomerContact,

c.sCustomer_phone_number,

c.smobilephone

FROM dbo.abc_Customers c

INNER JOIN dbo.CustomerChannel cc

ON c.sCustomer_number = cc.CustomerNumber

ORDER BY CASE WHEN c.sCustomer_number = 1 THEN 660 ELSE c.sCustomer_number END

-- If you don't need DISTINCT, remove it because it has a cost.

-- Why the TOP (100) PERCENT?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 11

GilaMonster (3/4/2014)
Did you try mine?

Yea but I get the same float error 🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Then there's something going on that you're not telling us about, because there are no floats around in case statements I wrote for you and if the columns are CHAR(8), there should be no conversions happening.

Does this run?

SELECT DISTINCT

c.iCustomer_id ,

c.sCustomer_number AS CustomerNo ,

CASE WHEN c.sCustomer_number = 'SL000000' THEN NULL

ELSE c.sCustomer_number

END AS Customer_number ,

c.sCustomer_name AS CustomerName ,

c.sCustomer_state AS Customerstate ,

cc.CustomerType AS Channel ,

cc.CustomersalesRegion AS Region ,

c.dteffectivedate AS CustomerEffDt ,

c.btCustomer_active AS Active ,

c.sCustomer_contact AS CustomerContact ,

c.sCustomer_phone_number ,

c.smobilephone

FROM dbo.abc_Customers c

INNER JOIN dbo.CustomerChannel cc ON c.sCustomer_number = dbo.CustomerChannel.CustomerNumber

ORDER BY Customer_number

What is the data type of sCustomer_number in the abc_Customers table? What is the data type of CustomerNumber in dbo.CustomerChannel?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 13

GilaMonster (3/4/2014)
Then there's something going on that you're not telling us about, because there are no floats around in case statements I wrote for you and if the columns are CHAR(8), there should be no conversions happening.
Does this run?
SELECT DISTINCT
c.iCustomer_id ,
c.sCustomer_number AS CustomerNo ,
CASE WHEN c.sCustomer_number = 'SL000000' THEN NULL
ELSE c.sCustomer_number
END AS Customer_number ,
c.sCustomer_name AS CustomerName ,
c.sCustomer_state AS Customerstate ,
cc.CustomerType AS Channel ,
cc.CustomersalesRegion AS Region ,
c.dteffectivedate AS CustomerEffDt ,
c.btCustomer_active AS Active ,
c.sCustomer_contact AS CustomerContact ,
c.sCustomer_phone_number ,
c.smobilephone
FROM dbo.abc_Customers c
INNER JOIN dbo.CustomerChannel cc ON c.sCustomer_number = dbo.CustomerChannel.CustomerNumber
ORDER BY Customer_number
What is the data type of sCustomer_number in the abc_Customers table? What is the data type of CustomerNumber in dbo.CustomerChannel?

Same float error:

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

There really isn't anymore that I can tell you about the table structure although I did notice that c.sCustomer_number column in the INNER JOIN is a float column. Could that be the issue?

HanShi SSC-Dedicated Points: 33507 More actions · Answer 14

igloo21 (3/4/2014)
GilaMonster (3/4/2014)
If you comment out the CASE in both the select and the where does the error go away?
Thanks for the reply, yes it goes away, but I need this to work with the CASE statement.

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

igloo21 (3/4/2014)
igloo21 (3/4/2014)
HanShi (3/4/2014)
What is the defined datatype for column [sCustomer_number] in table [dbo].[abc_Customers] ?
I expect the error is caused by the CASE statement. Can you confirm this by removing both the CASE statements and run the query? I think it will run without error.
Thanks for the reply, the data type is Char(8)
Thanks but unfortunately I get the same float error 🙁

Above bolded parts in the quotes display a contradiction. Did the query run correct when removing both the CASE statements or did it NOT run correct?

If it didn't run correct the error will most likely come from the JOIN clause (as noted by your previous post)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **