SQL geography::point null parameter error when parameter is not null?

6 votes

5 answers

5852 views

sql-server t-sql sql-server-2016 spatial

                          I was getting this error:

    'geography::Point' failed because parameter 1 is not allowed to be null.
On this sql:

    SELECT [ID], geography::Point([lat], [long], 4326) AS [loc]
    FROM (
      SELECT [ID], CONVERT(float, [lat]) AS [lat], CONVERT(float, [long]) AS [long]
      FROM (
    	SELECT [ID], [lat], 
    	[long], ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
    	FROM [GEO]
      ) AS temp1
      WHERE [sequence] = 1
      AND [lat] IS NOT NULL
      AND [long] IS NOT NULL
    ) AS temp2
    ORDER BY [ID]

But there where no null values, and I was only getting the error on our production machine (Production 13.0.4422.0) and not on our development machine (Dev 13.0.1728.2). After hours of searching and retrying I found that by reordering some stuff this would work:

	SELECT [ID], [loc]
	FROM (
	  SELECT [ID], 
        geography::Point([lat], [long], 4326) AS [loc],
	    ,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
	  FROM [GEO]
	  WHERE [lat] IS NOT NULL
	  AND [long] IS NOT NULL
	) AS temp
	WHERE [sequence] = 1

I would really like to understand what I was doing wrong in the first query and why this worked in the second query?

The first query I tried to get only the rows that have the most recent records based on a sequence number calculated using an effective date column, and then convert lat and long data to a geography type.

In the second query I convert the lat and long data and then filter. 

I thought I was being more efficient with the first query.
                        

Asked by user802599 (463 rep)

Oct 9, 2017, 02:58 AM
Last activity: Jan 8, 2024, 08:49 PM

SQL geography::point null parameter error when parameter is not null?

Related Questions