Could anyone spare a moment for a 2nd look at a SP

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

Transact-SQL (2000)

Could anyone spare a moment for a 2nd look at a SP

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 18:12:44

PLease >?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 18:13:58

Sure. But where is the SP ?

-----------------
[KH]

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 18:20:07

Thanks it is a bit of transac sql actually that has a problem with conversion issue and it is really puzzling me here it is the problem lies at the bottom of the AND clause

SELECT l.eannumber, l.prodcode, l.[description] ,l.jerseysupplierid ,b.week31salescost ,b.week32salescost ,b.week33salescost ,b.week34salescost ,b.week35salescost ,b.week36salescost ,b.week37salescost ,b.week38salescost ,b.week39salescost ,b.week40salescost ,b.week41salescost ,b.week42salescost ,b.week43salescost ,b.week44salescost

from sanitaUnitCost s inner join lrho1 l on

(s.EAN = l.eannumber)

inner join bell b on

(l.eannumber = b.barcode)

WHERE l.instorejersey = 'Y'

And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 18:24:02

the column l.JerseyUnitPrice is a numeric(28,4) data type which I am trying to compare to a char(10) datatype.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 18:44:38

are you getting any error with your query ?

-----------------
[KH]

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 18:49:09

yeah cheers for the prompt response.

this is the error I am getting :

Server: Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-12-12 : 20:00:28

Well, most likely there is some value in your "UnitPrice" column that is unable to be converted to numeric. Try running this statement to find the offending value(s):

SELECT UnitPrice FROM sanitaUnitCost
WHERE ISNUMERIC(UnitPrice) = 0

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 20:12:05

yeah cheers .if this value returns a true then what can I use a select case in the where clause .Waht do I do after...sorry I got no server to test ! ?

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 20:30:17

there is a interesting link related to this conversion topic in this forum here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41419&SearchTerms=conversion,error

but I wonder if Jonas 's function will work in my WHERE clause if the case won't ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 20:50:14

Looks like you have non numeric in your s.UnitPrice. The Question now is : Are these non numeric value important and contributing to your results ? Can it be ingore and excluded from your where conditions ?

You can use the isnumeric in your where statement before your comparision

and isnumeric(s.UnitPrice) = 1
And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)

and NOT

And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)
and isnumeric(s.UnitPrice) = 1

The isnumeric should come first before your <> else you will get the conversion error
-----------------
[KH]

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 20:58:50

Hi khtan Unfortunaltley I am comparing two tables containing product cost prices and
I need the where clause to show any discrepencies in the these prices.If there is a better
way I am all ears..I have 120000 lines in each table so it is going to be alot of manual checking but
all values look like numbers but are contained as varchar.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-12-12 : 21:10:27

quote:
Originally posted by Jim77

Hi khtan Unfortunaltley I am comparing two tables containing product cost prices and
I need the where clause to show any discrepencies in the these prices.If there is a better
way I am all ears..I have 120000 lines in each table so it is going to be alot of manual checking but
all values look like numbers but are contained as varchar.

use cast to convert the numeric column to a char column instead and then do the comparison.

-ec

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-12 : 21:15:51

that doesn't I have tried all types of cast statements and I get the same error message...

Server: Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-12-12 : 21:23:45

quote:
Originally posted by Jim77

that doesn't I have tried all types of cast statements and I get the same error message...

Server: Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

just to clarify, you have tried casting the JerseyUnitPrice column to a char and still get the same error?

-ec

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-12 : 21:38:11

quote:
You can use the isnumeric in your where statement before your comparision

and isnumeric(s.UnitPrice) = 1
And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)

and NOT

And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)
and isnumeric(s.UnitPrice) = 1

if this is the problem I think you'll need to do this instead:


SELECT l.eannumber, l.prodcode, l.[description] ,l.jerseysupplierid ,b.week31salescost 
,b.week32salescost ,b.week33salescost ,b.week34salescost ,b.week35salescost ,b.week36salescost 
,b.week37salescost ,b.week38salescost ,b.week39salescost ,b.week40salescost ,b.week41salescost 
,b.week42salescost ,b.week43salescost ,b.week44salescost

from	(
	select	ean
		,UnitPrice
	from	sanitaUnitCost
	where	isNumeric(UnitCost) = 1
	)  s inner join lrho1 l on

(s.EAN = l.eannumber)

inner join bell b on

(l.eannumber = b.barcode) 

WHERE l.instorejersey = 'Y'

And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)

If this doesn't do it, you should post some DDL/DML that will generate the error. If we had that to begin with there wouldn't need to be so much "back and forth".

Be One with the Optimizer
TG

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-12-12 : 21:41:33

quote:
Originally posted by TG

quote:
You can use the isnumeric in your where statement before your comparision

and isnumeric(s.UnitPrice) = 1
And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)

and NOT

And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)
and isnumeric(s.UnitPrice) = 1

if this is the problem I think you'll need to do this instead:

SELECT l.eannumber, l.prodcode, l.[description] ,l.jerseysupplierid ,b.week31salescost
,b.week32salescost ,b.week33salescost ,b.week34salescost ,b.week35salescost ,b.week36salescost
,b.week37salescost ,b.week38salescost ,b.week39salescost ,b.week40salescost ,b.week41salescost
,b.week42salescost ,b.week43salescost ,b.week44salescost

from (
select ean
,UnitPrice
from sanitaUnitCost
where isNumeric(UnitCost) = 1
) s inner join lrho1 l on

(s.EAN = l.eannumber)

inner join bell b on

(l.eannumber = b.barcode)

WHERE l.instorejersey = 'Y'

And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)
If this doesn't do it, you should post some DDL/DML that will generate the error. If we had that to begin with there wouldn't need to be so much "back and forth".

Be One with the Optimizer
TG

TG, that is the approach I would have taken, however the comment before seems to indicate that all columns need to be compared regardless of if they are numeric or not.

Using ISNUMERIC will strip out some columns from the comparison. THis will allow the query to work, but some data will be dropped.

I think that is the issue that Jim77 is having, but I might be wrong. That is why I made the suggestion of casting the numeric column to a CHAR and then comparing columns.

-ec

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-12 : 21:45:32

yeah, I think Jim should find what the offending values are with:
select Unitprice where isnumeric(unitprice) = 0

then if it's a matter of a few dollar signs or something, he could:
<> convert(numeric(18,3), replace(s.UnitPrice, '$', ''))

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-12 : 21:52:06

Sighhhh......my wife is watching figure skating....I'm bored

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 22:09:21

quote:
If this doesn't do it, you should post some DDL/DML that will generate the error. If we had that to begin with there wouldn't need to be so much "back and forth".

Jim77, Maybe you should also include some sample data like


select s.UnitPrice
from   sanitaUnitCost s
where  isnumeric(s.UnitPrice) = 0

let's see what kind of data you have in that UnitPrice column.

-----------------
[KH]

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-13 : 00:38:40

I think that is the issue that Jim77 is having, but I might be wrong. That is why I made the suggestion of casting the numeric column to a CHAR and then comparing columns.

Thanks guys for the help.about the above statement made by eyechart I don't understand if the column unitprice has been set as char(10) in the table would you need to explicitly convert the char to a char ....if you know what I mean as I thought this was char already..

secondly I do realise alot of the unitprices (which is a calculated field in my table defined as: [JerseyUnitPrice] AS ([JerseyCost] / [JerseyQuantity]) are either empty or null so I definitly want to ignore these ..

Would this still work then do you think guys:
and isnumeric(s.UnitPrice) = 1
And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-13 : 01:13:38

quote:
Would this still work then do you think guys:
and isnumeric(s.UnitPrice) = 1
And l.JerseyUnitPrice <> convert(numeric(18,3), s.UnitPrice)

Yes. It will work

-----------------
[KH]
Learn something new everyday

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources