|
|
You're in the
sql server programming
group:my first attemp at a function... one error left, but not sure why
sql server programming:
'ello again :) It looks like you're quite close, this might be what
you're after. CREATE FUNCTION dbo.func_CnvtHMS2DD( @HMS nvarchar(20) ) RETURNS NUMERIC(10,7) AS BEGIN Declare @hours NUMERIC(10,7), @minutes NUMERIC(10,7), @seconds NUMERIC(10,7), @result NUMERIC(10,7) Set @hours = Substring(@HMS, 2, CharIndex('-', @HMS) - 2) Set @minutes = Substring(@HMS, CharIndex('-', @HMS) + 1, CharIndex('.',@HMS) - 5) Set @seconds = Substring(@HMS, CharIndex('.', @HMS) + 1, Len(@HMS)) set @minutes = @minutes / 60 set @seconds = @seconds / 3600 SET @result = @hours + @minutes + @seconds RETURN @result END I've reworked it for the sake of SQL but I haven't checked the maths. Use like this; SELECT dbo.func_CnvtHMS2DD('N51-40.4.') Good luck, Rhys [quoted text, click to view] On Sep 20, 10:17 pm, "Aussie Rules" <AussieRu...@nospam.nospam> wrote: > Hi, > > I have some data in a field that I need to convert into a new value. I want > to be able to select from my table, but have the resultset have the > converted values, not the actual table values. > > for example, a normal select against the table with the actual values would > be > > select field1, field2, field3, field4, field5 > > this would return > > value1, value2, value3, value5, value5. > > I want to do some math, and convert value3 and value4 to new values. > > select field1, field2, func_CnvtHMS2DD(field3), func_CnvtHMS2DD(field4), > field5 > > this would return > > value1, value2, value9, value45, value5. > > From reading a bit, using a function would do this (I think) so I have had a > go at do my first function, however I get the message 'A RETURN statement > with a return value cannot be used in this context.' > > What have i done wrong below ? > > CREATE FUNCTION func_CnvtHMS2DD > ( @HMS nvarchar(20) > > ) > RETURNS @tbl TABLE (val nvarchar(30)) > AS > > BEGIN > Declare @hours int, > @minutes int, > @seconds int > > Set @hours = Substring(@HMS, 2, CharIndex('-', @HMS) - 2) > Set @minutes = Substring(@HMS, CharIndex('-', @HMS) + 1, CharIndex('.', > @HMS) - 5) > Set @seconds = Substring(@HMS, CharIndex('.', @HMS) + 1, Len(@HMS)) > > set @minutes = @minutes /60 > set @seconds = @seconds/3600 > > RETURN > ( > -- Add the SELECT statement with parameter references here > Select @hours + @minutes + @seconds As DDValue > > ) > > END
You need to qualify the function name with it's schema, so it is
called dbo.func_CnvtHMS2DD and not just func_CnvtHMS2DD. Off the top of my head I can't remember the reason for the two part naming requirement, but it's the answer anyway! Regards, Rhys [quoted text, click to view] On Sep 20, 10:47 pm, "Aussie Rules" <AussieRu...@nospam.nospam> wrote: > Hi, > > Thanks for you help. > > Almost perfect, however is it possible, to a field name in. > > The database looks like this > longitude latitude name notes > S33-56.8 E151-10.6 Sydney king > > i want to be able to select back a resultset with the fields longitude and > latitude converted. > > You sample select statement works ' SELECT > dbo.func_CnvtHMS2DD('N51-40.4.')' > > however I need to do this : > > select longitude , latitude, func_CnvtHMS2DD(longitude) from tbl_location > > but i get this error : > 'func_CnvtHMS2DD' is not a recognized built-in function name. > > "RMJCS" <r...@rmjcs.com> wrote in message > > news:1190324577.596258.210200@19g2000hsx.googlegroups.com... > > > 'ello again :) It looks like you're quite close, this might be what > > you're after. > > > CREATE FUNCTION dbo.func_CnvtHMS2DD( @HMS nvarchar(20) ) RETURNS > > NUMERIC(10,7) > > AS > > BEGIN > > Declare @hours NUMERIC(10,7), > > @minutes NUMERIC(10,7), > > @seconds NUMERIC(10,7), > > @result NUMERIC(10,7) > > > Set @hours = Substring(@HMS, 2, CharIndex('-', @HMS) - 2) > > Set @minutes = Substring(@HMS, CharIndex('-', @HMS) + 1, > > CharIndex('.',@HMS) - 5) > > Set @seconds = Substring(@HMS, CharIndex('.', @HMS) + 1, Len(@HMS)) > > > set @minutes = @minutes / 60 > > set @seconds = @seconds / 3600 > > > SET @result = @hours + @minutes + @seconds > > > RETURN @result > > > END > > > I've reworked it for the sake of SQL but I haven't checked the maths. > > Use like this; > > > SELECT dbo.func_CnvtHMS2DD('N51-40.4.') > > > Good luck, Rhys > > > On Sep 20, 10:17 pm, "Aussie Rules" <AussieRu...@nospam.nospam> wrote: > >> Hi, > > >> I have some data in a field that I need to convert into a new value. I > >> want > >> to be able to select from my table, but have the resultset have the > >> converted values, not the actual table values. > > >> for example, a normal select against the table with the actual values > >> would > >> be > > >> select field1, field2, field3, field4, field5 > > >> this would return > > >> value1, value2, value3, value5, value5. > > >> I want to do some math, and convert value3 and value4 to new values. > > >> select field1, field2, func_CnvtHMS2DD(field3), func_CnvtHMS2DD(field4), > >> field5 > > >> this would return > > >> value1, value2, value9, value45, value5. > > >> From reading a bit, using a function would do this (I think) so I have > >> had a > >> go at do my first function, however I get the message 'A RETURN statement > >> with a return value cannot be used in this context.' > > >> What have i done wrong below ? > > >> CREATE FUNCTION func_CnvtHMS2DD > >> ( @HMS nvarchar(20) > > >> ) > >> RETURNS @tbl TABLE (val nvarchar(30)) > >> AS > > >> BEGIN > >> Declare @hours int, > >> @minutes int, > >> @seconds int > > >> Set @hours = Substring(@HMS, 2, CharIndex('-', @HMS) - 2) > >> Set @minutes = Substring(@HMS, CharIndex('-', @HMS) + 1, CharIndex('.', > >> @HMS) - 5) > >> Set @seconds = Substring(@HMS, CharIndex('.', @HMS) + 1, Len(@HMS)) > > >> set @minutes = @minutes /60 > >> set @seconds = @seconds/3600 > > >> RETURN > >> ( > >> -- Add the SELECT statement with parameter references here > >> Select @hours + @minutes + @seconds As DDValue > > >> ) > > >> END
Hi,
I have some data in a field that I need to convert into a new value. I want to be able to select from my table, but have the resultset have the converted values, not the actual table values. for example, a normal select against the table with the actual values would be select field1, field2, field3, field4, field5 this would return value1, value2, value3, value5, value5. I want to do some math, and convert value3 and value4 to new values. select field1, field2, func_CnvtHMS2DD(field3), func_CnvtHMS2DD(field4), field5 this would return value1, value2, value9, value45, value5. From reading a bit, using a function would do this (I think) so I have had a go at do my first function, however I get the message 'A RETURN statement with a return value cannot be used in this context.' What have i done wrong below ? CREATE FUNCTION func_CnvtHMS2DD ( @HMS nvarchar(20) ) RETURNS @tbl TABLE (val nvarchar(30)) AS BEGIN Declare @hours int, @minutes int, @seconds int Set @hours = Substring(@HMS, 2, CharIndex('-', @HMS) - 2) Set @minutes = Substring(@HMS, CharIndex('-', @HMS) + 1, CharIndex('.', @HMS) - 5) Set @seconds = Substring(@HMS, CharIndex('.', @HMS) + 1, Len(@HMS)) set @minutes = @minutes /60 set @seconds = @seconds/3600 RETURN ( -- Add the SELECT statement with parameter references here Select @hours + @minutes + @seconds As DDValue ) END
Hi,
Thanks for you help. Almost perfect, however is it possible, to a field name in. The database looks like this longitude latitude name notes S33-56.8 E151-10.6 Sydney king i want to be able to select back a resultset with the fields longitude and latitude converted. You sample select statement works ' SELECT dbo.func_CnvtHMS2DD('N51-40.4.')' however I need to do this : select longitude , latitude, func_CnvtHMS2DD(longitude) from tbl_location but i get this error : 'func_CnvtHMS2DD' is not a recognized built-in function name. [quoted text, click to view] "RMJCS" <rhys@rmjcs.com> wrote in message
news:1190324577.596258.210200@19g2000hsx.googlegroups.com... > 'ello again :) It looks like you're quite close, this might be what > you're after. > > CREATE FUNCTION dbo.func_CnvtHMS2DD( @HMS nvarchar(20) ) RETURNS > NUMERIC(10,7) > AS > BEGIN > Declare @hours NUMERIC(10,7), > @minutes NUMERIC(10,7), > @seconds NUMERIC(10,7), > @result NUMERIC(10,7) > > Set @hours = Substring(@HMS, 2, CharIndex('-', @HMS) - 2) > Set @minutes = Substring(@HMS, CharIndex('-', @HMS) + 1, > CharIndex('.',@HMS) - 5) > Set @seconds = Substring(@HMS, CharIndex('.', @HMS) + 1, Len(@HMS)) > > set @minutes = @minutes / 60 > set @seconds = @seconds / 3600 > > SET @result = @hours + @minutes + @seconds > > RETURN @result > > END > > I've reworked it for the sake of SQL but I haven't checked the maths. > Use like this; > > SELECT dbo.func_CnvtHMS2DD('N51-40.4.') > > Good luck, Rhys > > On Sep 20, 10:17 pm, "Aussie Rules" <AussieRu...@nospam.nospam> wrote: >> Hi, >> >> I have some data in a field that I need to convert into a new value. I >> want >> to be able to select from my table, but have the resultset have the >> converted values, not the actual table values. >> >> for example, a normal select against the table with the actual values >> would >> be >> >> select field1, field2, field3, field4, field5 >> >> this would return >> >> value1, value2, value3, value5, value5. >> >> I want to do some math, and convert value3 and value4 to new values. >> >> select field1, field2, func_CnvtHMS2DD(field3), func_CnvtHMS2DD(field4), >> field5 >> >> this would return >> >> value1, value2, value9, value45, value5. >> >> From reading a bit, using a function would do this (I think) so I have >> had a >> go at do my first function, however I get the message 'A RETURN statement >> with a return value cannot be used in this context.' >> >> What have i done wrong below ? >> >> CREATE FUNCTION func_CnvtHMS2DD >> ( @HMS nvarchar(20) >> >> ) >> RETURNS @tbl TABLE (val nvarchar(30)) >> AS >> >> BEGIN >> Declare @hours int, >> @minutes int, >> @seconds int >> >> Set @hours = Substring(@HMS, 2, CharIndex('-', @HMS) - 2) >> Set @minutes = Substring(@HMS, CharIndex('-', @HMS) + 1, CharIndex('.', >> @HMS) - 5) >> Set @seconds = Substring(@HMS, CharIndex('.', @HMS) + 1, Len(@HMS)) >> >> set @minutes = @minutes /60 >> set @seconds = @seconds/3600 >> >> RETURN >> ( >> -- Add the SELECT statement with parameter references here >> Select @hours + @minutes + @seconds As DDValue >> >> ) >> >> END > >
Hi Aussie and Rhys,
The reason is that Scalar-valued functions must be invoked by using at least the two-part name of the function. You may refer to the section "Function Invocation" in this article: CREATE FUNCTION (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms186755.aspx Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |