code help with user defined funtion

[charlestdenver]

I'm having trouble with the following query. If I remove the comments from around line 5 I get the following error.

Server: Msg 8114, Level 16, State 5, Procedure ordervalue, Line 7
Error converting data type varchar to numeric.

But it runs fine without line 5 of the query.

usrdef03 and userdef1 are both char 21 fields with numeric text.

It would be great just to change the fields to numeric, but its part of a Great Plains database and cant be done.

An explanation would be appreciated.

Charles Terry

SELECT SOP10100.SOPNUMBE, SOP10100.DOCDATE,
SOP10100.ReqShipDate, SOP10100.CUSTNMBR,
SOP10100.CUSTNAME, SOP10100.SLPRSNID,
SOP10100.ShipToName, SOP10100.DOCAMNT,
/*sop10106.usrdef03,*/
dbo.ordervalue(sop10106.userdef1,SOP10100.DOCAMNT)
FROM SOP10100 LEFT OUTER JOIN
SOP10106 ON
SOP10100.SOPNUMBE = SOP10106.SOPNUMBE



alter function ordervalue (@value1 as char(21) , @value2 numeric (19,2))
returns numeric(19,2)
as begin
declare @ordervalue as numeric (19,2)
set @ordervalue = @value2
if (cast(@value1 as numeric(19,2)) > 0)
set @ordervalue = cast(@value1 as numeric (19,2))
return(@ordervalue)
end

 

[gmmastros]

Can you determine the parameter values that are causing this error?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[charlestdenver]

If I just use the ordervalue function- no error it returns numbers, if I just use the usrdef03 field, the userdef1 field, or both together they return text such as 97.50. Its just when I used the ordervalue function with either of the fields as in the original query above that I get the error.