Syntax advise please

Question

Syntax advise please

Viewing 9 posts - 16 through 23 (of 23 total)

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

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

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)

No I got the same float error. As I mentioned earlier the dbo.CustomerChannel table c.sCustomer_number column is a float column, I'm thinking that could be why my script is erroring out.

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

igloo21 (3/4/2014)
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)
No I got the same float error. As I mentioned earlier the dbo.CustomerChannel table c.sCustomer_number column is a float column, I'm thinking that could be why my script is erroring out.

Replace the INNER JOIN part from Gail's or Chris's script with the following:

INNER JOIN dbo.CustomerChannel cc ON CAST(c.sCustomer_number as VARCHAR(8)) = dbo.CustomerChannel.CustomerNumber

ORDER BY Customer_number

Note: The performance could degrade because of this conversion.

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

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

igloo21 (3/4/2014)
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)
No I got the same float error. As I mentioned earlier the dbo.CustomerChannel table c.sCustomer_number column is a float column, I'm thinking that could be why my script is erroring out.

Of course it is. What threw everyone is this

CASE WHEN c.sCustomer_number = 'SL000000' THEN

because it implies that you know there are values of 'SL000000' in column c.sCustomer_number.

^{“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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

its failing on the join condition .... your other table have a data type of float.

you with ensure that data on which you are joining should be of numeric type.

apply a where clause on the column for using ISNumeric function.

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

twin.devil (3/4/2014)
its failing on the join condition .... your other table have a data type of float.
you with ensure that data on which you are joining should be of numeric type.
apply a where clause on the column for using ISNumeric function.

I agree. I scripted out the table and recreated it on another db and made the column nvarchar(255) and my script runs. I just need to figure out how to use this ISNumeric function.

Thanks for the reply!

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

This will get you going

Declare @varTable table ( varField varchar(20))

insert into @varTable

select 'ABC' union all

select '12.00' union all

select '12' union all

select '1' union all

select '0.1' union all

select '121a123' union all

select '12 _'

select *

from @varTable

where isnumeric(varField) = 0

select *

from @varTable

where isnumeric(varField) = 1

hope it helps

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

Keep in mind there are some caveats with the IsNumeric function. Several blogs and articles are written about that. Search for "IsNumeric" on this site.

Take a look at http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

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

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

twin.devil (3/4/2014)
its failing on the join condition .... your other table have a data type of float.
you with ensure that data on which you are joining should be of numeric type.
apply a where clause on the column for using ISNumeric function.

A WHERE clause using the IsNumeric funtion to filter for only numric valus does NOT prevent the conversion error. See the following sample (the code will fail with "Error converting data type varchar to float." dispite the WHERE clause):

create table #one (value float)

create table #two (value varchar(8))

insert into #one (value)

select 12 union all

select 1 union all

select 123 union all

select 12.00 union all

select 5

insert into #two (value)

select 'ABC' union all

select '12.00' union all

select '12' union all

select '1' union all

select '1,1' union all

select '121a123' union all

select '12e2'

select *

from #two

where isnumeric(value) = 0

select *

from #two

where isnumeric(value) = 1

select *

from #one

inner join #two on #one.value = #two.value

where isnumeric(#two.value) = 1

drop table #one

drop table #two

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

CKX SSCrazy Points: 2519 More actions · Answer 9

This underlines the reason why it's best to post ddl as well as expected output.

You would have hand this solved many posts ago if you had done that so always a good idea to provide it.

Read that spackle article by Jeff Moden on the IsNumeric function that HanShi suggested. You'll find it quite informative.