Link to home
Start Free TrialLog in
Avatar of Sheritlw
SheritlwFlag for United States of America

asked on

View and Function Conversion

Hi EE,

I have a view with a field avgservicetime.  The datatype is smallint.
From this view, I send the avgservicetime value to a function in order to do calculations and return a string.
In the view I get a conversion error.  
Where and how should convert the smallint value to nchar(20)
Thanks
ALTER function [dbo].[fn_ReturnHoursFromMinutes]
(@AvgMinutes smallint)

returns nchar(20)

as

begin
declare @MinToHours nchar(20)

	if @avgminutes = null or @avgminutes = 0
		begin
			set @MinToHours = 'Not Specified'
		end

	if @avgminutes > 59
		begin
			set @MinToHours = (@avgminutes / 60) + ' Hours'
		end

	else
		begin
			set @MinToHours =  @avgminutes  + ' Minutes'
		end

return @mintohours

end

Open in new window

SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
use cast instead of convert
Try it this way:
Alter FUNCTION [dbo].[fn_ReturnHoursFromMinutes] (
						@AvgMinutes smallint
						)

RETURNS nchar(20)

AS 
BEGIN
RETURN (
	CASE 
		WHEN @avgminutes IS NULL OR @avgminutes = 0 THEN 'Not Specified'
		WHEN @avgminutes > 59 THEN CAST(@avgminutes / 60 AS nvarchar(10)) + ' Hours'
		ELSE CAST(@avgminutes AS nvarchar(10)) + ' Minutes'
	END
	)
END

Open in new window

>>use cast instead of convert <<
I suspect you posted in the wrong thread.
Avatar of Sheritlw

ASKER

Thank you, I did correct the function, but I believe the error is coming from the view.  
I have posted a screen shot of the error.  I believe in the view I have to do a cast or convert... just don't remember... it's been a while.
Thanks




ViewServicesLU.png
Avatar of antonybrahin
antonybrahin

the following code should work .. ;)  
ALTER function [dbo].[fn_ReturnHoursFromMinutes]
(@AvgMinutes smallint)

returns nchar(20)

as

begin
declare @MinToHours nchar(20)

	if @avgminutes = null or @avgminutes = 0
		begin
			set @MinToHours = 'Not Specified'
		end

	if @avgminutes > 59
		begin
			set @MinToHours = cast((@avgminutes / 60) as nchar(10)) + ' Hours'
		end

	else
		begin
			set @MinToHours =  cast(@avgminutes as nchar(10))  + ' Minutes'
		end

return @mintohours

end

Open in new window

post ur view here.
I've added the view to the code section.
Look for the dbo.fn_ReturnHoursFromMinutes(dbo.ServicesLU.AvgServiceTime

dbo.ServicesLU.AvgServiceTime  dbtype is smallint

Thanks
SELECT     TOP (100) PERCENT dbo.ServicesLU.LUServiceID, dbo.ServicesLU.UserID, dbo.ServicesLU.ServiceOffered, dbo.ServicesLU.Price, dbo.ServicesLU.Describe, 
                      dbo.ServicesLU.MultiServ, dbo.ServicesLU.BackColor, dbo.ServicesLU.FontColor, dbo.ServicesLU.BorderColor, 
                      dbo.fn_ReturnHoursFromMinutes(dbo.ServicesLU.AvgServiceTime) AS ServiceHours, dbo.ServicesLU.AvgWaitTime, dbo.Stylists.StylistID
FROM         dbo.ServicesLU INNER JOIN
                      dbo.Stylists ON dbo.ServicesLU.UserID = dbo.Stylists.UserID
ORDER BY dbo.ServicesLU.ServiceOffered

Open in new window

>>I believe in the view I have to do a cast or convert... just don't remember... it's been a while<<
Either one would do it.  But post your VIEW and we can be more precise.

Unrelated, but if you are attempting to get the results sorted doing TOP (100) PERCENT ... ORDER BY is totally pointless and will not achieve your goal.  The QO is smart enough to know that you have requested all the rows and will promptly ignore the ORDER BY clause.
If AvgServiceTime is in fact smallint then there is nothing wrong with your VIEW.
You may find this a tad easier to read (notice how I have dropped the useless TOP (100) PERCENT ... ORDER BY):
SELECT  slu.LUServiceID,
        slu.UserID,
        slu.ServiceOffered,
        slu.Price,
        slu.Describe,
        slu.MultiServ,
        slu.BackColor,
        slu.FontColor,
        slu.BorderColor,
        dbo.fn_ReturnHoursFromMinutes(slu.AvgServiceTime) AS ServiceHours,
        slu.AvgWaitTime,
        s.StylistID
FROM    dbo.ServicesLU slu
        INNER JOIN dbo.Stylists s ON slu.UserID = s.UserID

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
my solution was exactly same :( only difference was cast instead of convert.. this should not make any difference.
yes, but that didn't work in the view. I received an error with the word "as".

The actual solution  required convert in the Function itself.

I gave you points even though your answer did not work because you had responded to this post.

Best Regards
HI,

Its good that you already got the solution.
But still one comment.
You must beknowing differecne between CHAR & VARCHAR.

declare @var1 char(20)
set @var1 = 'temp'

char datatype always use full given value.
I mean, here you gave 20, so @var1 will holds 'temp              '

If you not using for some purpose then better to used nvarchar.
>>Well I appreciate everyone's help, but I wasn't really getting the answers I needed<<
Not cool.
I found the solution and posted it.