December 21, 2010 at 6:05 pm
Hello everybody,
I am a newbie in SQL Server and I have a strange behaviour with a user-defined function I developped.
This function returns a float, which can be positive or negative.
If I use it in an UPDATE statement or just test it with the following line, it works like a charm :
PRINT LOGSHEET.dbo.GetLatDFromLat('123860S',60);
Returns -12.64
However, if I use it in an SQL SELECT query, it loses its sign :
select Latitude,LOGSHEET.dbo.GetLatDFromLat(Latitude,60)
from LOGSHEET.dbo.boundary
where Latitude='123860S'
Returns 12.64
Suprisingly, if I use the following query, it once again works perfectly :
select Latitude,LOGSHEET.dbo.GetLatDFromLat('123860S',60)
from LOGSHEET.dbo.boundary
where Latitude='123860S'
Returns -12.64
Do you know what is going on ? Is the issue on the Query side or the Function side ?
The function :
ALTER FUNCTION GetLatDfromLat
(
@Lat VARCHAR(10),
@Base INT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @LatD FLOAT;
DECLARE @Deg INT;
DECLARE @min-2 INT;
DECLARE @sec INT;
SET @LatD=null;
IF (LEN(@Lat)=5)
--If no second, put 00
SET @Lat=LEFT(@Lat,4)+'00'+RIGHT(@Lat,1);
IF (LEN(@Lat)<>7)
--Latitude format must be DDMMSSH H:N or S
Return @LatD;
SET @Deg= CAST(LEFT(@Lat,2) as INT);
IF (@Deg>90)
--Latitude Degree must be between 0 and 90
Return @LatD;
SET @min-2=CAST(SUBSTRING(@Lat,3,2) as INT);
IF (@Min>60)
--Latitude Minute must be between 0 and 60
Return @LatD;
SET @sec=CAST(SUBSTRING(@Lat,5,2) as INT);
--IF (@Sec>@Base)
--Latitude Second must be between 0 and base
--Return @LatD;
SET @LatD = @Deg+((CAST(@Min as FLOAT)/60)*100+CAST(@Sec as FLOAT)/@Base)/100;
--If south, need to change the sign of the latitude
IF (RIGHT(@Lat,1)='S')
SET @LatD=@LatD-2*@LatD;
Return @LatD;
END
GO
Thank you 🙂 !
Bruno
December 21, 2010 at 8:15 pm
Hi Falks,
It's getting even weirder ...
I just did the same kind of function, but to compute longitude this time.
Here are my results :
select Longitude, LOGSHEET.dbo.GetLonDFromLon(Longitude,100)
from LOGSHEET.dbo.boundary
where Longitude='1704142W'
Returns 170.69 (wrong value)
PRINT LOGSHEET.dbo.GetLonDFromLon('1704142W',100);
Returns 189.31 (right value)
Am I calling wrongly a user-defined fonction within a Select query ?
It seems like it is not using the right Latitude or Longitude, even when I put it in the Where clause...
Any idea ?
Thanks !
Bruno
December 21, 2010 at 11:40 pm
Please post the schema of the LOGSHEET.dbo.boundary table, and a script to populate a few sample rows and I can have a look at this tomorrow.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 22, 2010 at 2:15 pm
Hi The Dixie Flatine
please find below what you requested.
1. Create Table :
USE [LOGSHEET]
GO
/****** Object: Table [dbo].[BOUNDARY] Script Date: 12/23/2010 07:57:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BOUNDARY](
[Country] [varchar](2) NOT NULL,
[Subcountry] [varchar](2) NOT NULL,
[Latitude] [varchar](15) NULL,
[Longitude] [varchar](15) NULL,
[LatitudeD] [float] NULL,
[LongitudeD] [float] NULL,
[Point] [geography] NULL,
[No] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert script (Generated by SSMS Tool Pack) :
USE [LOGSHEET];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[BOUNDARY]([Country], [Subcountry], [Latitude], [Longitude], [LatitudeD], [LongitudeD], [Point], [No])
SELECT N'AS', N'AS', N'123860S', N'1704142W', N'-12.6433744430542', N'189.690368652344', N'POINT (189.69 -12.6434)', 1 UNION ALL
SELECT N'AS', N'AS', N'123811S', N'1704117W', N'-12.6352367401123', N'189.686187744141', N'POINT (189.686 -12.6352)', 2 UNION ALL
SELECT N'AS', N'AS', N'123713S', N'1703016W', N'-12.6189651489258', N'189.502822875977', N'POINT (189.503 -12.619)', 3 UNION ALL
SELECT N'AS', N'AS', N'123054S', N'1715216W', N'-12.5091257095337', N'188.869384765625', N'POINT (188.869 -12.5091)', 4 UNION ALL
SELECT N'AS', N'AS', N'122786S', N'1714216W', N'-12.4643774032593', N'188.702682495117', N'POINT (188.703 -12.4644)', 5 UNION ALL
SELECT N'AS', N'AS', N'114162S', N'1721199W', N'-11.6937704086304', N'187.199981689453', N'POINT (187.2 -11.6938)', 6 UNION ALL
SELECT N'AS', N'AS', N'113563S', N'1720308W', N'-11.5939531326294', N'187.051345825195', N'POINT (187.051 -11.594)', 7 UNION ALL
SELECT N'AS', N'AS', N'113089S', N'1735620W', N'-11.514931678772', N'186.93669128418', N'POINT (186.937 -11.5149)', 8 UNION ALL
SELECT N'AS', N'AS', N'113077S', N'1735416W', N'-11.5128526687622', N'186.902709960938', N'POINT (186.903 -11.5129)', 9 UNION ALL
SELECT N'AS', N'AS', N'102013S', N'1711483W', N'-10.3355102539063', N'188.247299194336', N'POINT (188.247 -10.3355)', 10 UNION ALL
SELECT N'AS', N'AS', N'100202S', N'1682856W', N'-10.0337333679199', N'191.476089477539', N'POINT (191.476 -10.0337)', 11 UNION ALL
SELECT N'AS', N'AS', N'114349S', N'1683191W', N'-11.7248373031616', N'191.531860351563', N'POINT (191.532 -11.7248)', 12 UNION ALL
SELECT N'AS', N'AS', N'120209S', N'1684986W', N'-12.0349569320679', N'191.831024169922', N'POINT (191.831 -12.035)', 13 UNION ALL
SELECT N'AS', N'AS', N'122826S', N'1673427W', N'-12.4710569381714', N'192.571304321289', N'POINT (192.571 -12.4711)', 14 UNION ALL
SELECT N'AS', N'AS', N'140333S', N'1652258W', N'-14.0555553436279', N'194.376388549805', N'POINT (194.376 -14.0556)', 15 UNION ALL
SELECT N'AS', N'AS', N'153898S', N'1654844W', N'-15.6497478485107', N'194.807373046875', N'POINT (194.807 -15.6497)', 16 UNION ALL
SELECT N'AS', N'AS', N'173411S', N'1662112W', N'-17.5685977935791', N'193.352157592773', N'POINT (193.352 -17.5686)', 17 UNION ALL
SELECT N'AS', N'AS', N'171027S', N'1671115W', N'-17.1712608337402', N'192.185958862305', N'POINT (192.186 -17.1713)', 18 UNION ALL
SELECT N'AS', N'AS', N'165719S', N'1683921W', N'-16.9532089233398', N'191.653549194336', N'POINT (191.654 -16.9532)', 19 UNION ALL
SELECT N'AS', N'AS', N'164905S', N'1681578W', N'-16.8175315856934', N'191.263137817383', N'POINT (191.263 -16.8175)', 20
COMMIT;
RAISERROR (N'[dbo].[BOUNDARY]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
Thanks for your help !
Bruno
December 22, 2010 at 2:41 pm
Change your function's variable declaration to use NVARCHAR, and not Varchar.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 22, 2010 at 3:43 pm
I'm afraid I'm not going to be much help. I built and populated the table using your scripts and then ran the original calls to the function which you first posted.
I get consistent and correct answers from all variations of calls to your latitude function.
In other words, I can't recreate the problem you are experiencing.
I did NOT change the columns to nvarchar.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 22, 2010 at 3:51 pm
Hi Matt Miller and The Dixie Flatline,
thanks for spending some time on my issue.
I actually dropped all my tables and started from scratch with the sample I provided in my previous topic and just like you it worked perfectly.
Then I mass loaded everything and it still works fine now.
I must have done something wrong while developping and testing ...
Thanks a lot anyway for you time, and sorry this issue was actually not one.
Bruno
December 22, 2010 at 4:25 pm
No problem. Thanks for letting us know it's working now. Best of luck to you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply