SQLTeam.com | Weblogs | Forums

Null values and order statements

Pretty new to SQL and I can't get my statement to work. I know this part works fine:

SELECT
--SUBSTRING (rm.REM_PIN, 1,12) AS GPIN,
REM_PIN AS CAMA_GPIN,
REM_PID AS PID,
MVP_CONSTRSUM.MVP_BID AS BID,
MVP_CONSTRSUM.MVP_SECT_NUM AS SECTION_NUM,
MVP_CONSTRSUM.MVP_OCC_1 AS OCCUPANCY_CODE,
MVP_CONSTRSUM.MVP_OCC_DESC_1 AS OCCUPANCY_DESCRIPTION,
MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_1 AS PERCENTAGE_1,
MVP_CONSTRSUM.MVP_OCC_2 AS OCCUPANCY_CODE_2,
MVP_CONSTRSUM.MVP_OCC_DESC_2 AS OCCUPANCY_DESCRIPTION_2,
MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_2 AS PERCENTAGE_2,
MVP_CONSTRSUM.MVP_OCC_3 AS OCCUPANCY_CODE_3,
MVP_CONSTRSUM.MVP_OCC_DESC_3 AS OCCUPANCY_DESCRIPTION_3,
MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_3 AS PERCENTAGE_3,
MVP_CONSTRSUM.MVP_OCC_4 AS OCCUPANCY_CODE_4,
MVP_CONSTRSUM.MVP_OCC_DESC_4 AS OCCUPANCY_DESCRIPTION_4,
MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_4 AS OCCUPANCY_PERCENTAGE_4,

GIS_EXT.GIS.TAX_PARCEL.SHAPE
FROM
VISION_DEV.Vision.Real_Prop.REALMAST

inner JOIN
VISION_DEV.Vision.Real_Prop.MVP_CONSTRSUM

ON
REM_PID = MVP_PID

LEFT JOIN
gis_ext.gis.TAX_PARCEL
ON
GPIN = SUBSTRING (REM_PIN, 1,12)
GO

The problem is I want to put this clause in to get rid of null values and order the data but my statement keeps breaking wherever I put it.
WHERE (((REAL_PROP_MVP_CONSTRSUM.MVP_BID)>0) AND ((REAL_PROP_MVP_CONSTRSUM.MVP_OCC_1) Is Not Null))
ORDER BY REAL_PROP_MVP_CONSTRSUM.MVP_PID, REAL_PROP_MVP_CONSTRSUM.MVP_BID, REAL_PROP_MVP_CONSTRSUM.MVP_SECT_NUM;

can you show the complete query after you add the where block.

I don't know where to add it in my query. I've tried adding it everywhere and it won't work

i have formatted it

what error are you getting where is it breaking

SELECT
	--SUBSTRING (rm.REM_PIN, 1,12) AS GPIN,
	REM_PIN AS CAMA_GPIN,
	REM_PID AS PID,
	MVP_CONSTRSUM.MVP_BID AS BID,
	MVP_CONSTRSUM.MVP_SECT_NUM AS SECTION_NUM,
	MVP_CONSTRSUM.MVP_OCC_1 AS OCCUPANCY_CODE,
	MVP_CONSTRSUM.MVP_OCC_DESC_1 AS OCCUPANCY_DESCRIPTION,
	MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_1 AS PERCENTAGE_1,
	MVP_CONSTRSUM.MVP_OCC_2 AS OCCUPANCY_CODE_2,
	MVP_CONSTRSUM.MVP_OCC_DESC_2 AS OCCUPANCY_DESCRIPTION_2,
	MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_2 AS PERCENTAGE_2,
	MVP_CONSTRSUM.MVP_OCC_3 AS OCCUPANCY_CODE_3,
	MVP_CONSTRSUM.MVP_OCC_DESC_3 AS OCCUPANCY_DESCRIPTION_3,
	MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_3 AS PERCENTAGE_3,
	MVP_CONSTRSUM.MVP_OCC_4 AS OCCUPANCY_CODE_4,
	MVP_CONSTRSUM.MVP_OCC_DESC_4 AS OCCUPANCY_DESCRIPTION_4,
	MVP_CONSTRSUM.MVP_OCC_PERCENTAGE_4 AS OCCUPANCY_PERCENTAGE_4,
	GIS_EXT.GIS.TAX_PARCEL.SHAPE
FROM
	VISION_DEV.Vision.Real_Prop.REALMAST
		INNER JOIN
	VISION_DEV.Vision.Real_Prop.MVP_CONSTRSUM	ON REM_PID = MVP_PID
        LEFT JOIN
    GIS_EXT.gis.TAX_PARCEL	ON GPIN = SUBSTRING (REM_PIN, 1,12)
WHERE 
      REAL_PROP_MVP_CONSTRSUM.MVP_BID > 0
	      AND 
      REAL_PROP_MVP_CONSTRSUM.MVP_OCC_1 Is Not Null
ORDER BY 
        REAL_PROP_MVP_CONSTRSUM.MVP_PID
	  , REAL_PROP_MVP_CONSTRSUM.MVP_BID
	  , REAL_PROP_MVP_CONSTRSUM.MVP_SECT_NUM

Thanks! It's still bringing back NULL values

which ever fields are returning nulls .. you have to add in where clause NOT NULL

example:

if ColumnABC and ColumnDEF are returning nulls
and the where clause is WHERE name ='James'

then new where statement would be

WHERE
name = 'James'
AND
ColumnABC is not null
AND
ColumnDEF is not null

hope this helps you understand

Thanks. I got all of that taken care of. Now that my query works, I'm putting it into a create a view window and receive this error now.

"Msg 1033, Level 15, State 1, Procedure SVW_REA_MS_OCC_CODE, Line 49 [Batch Start Line 9]
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."

You need to remove ORDER BY.
However, you can use ORDER BY when you are selecting from the view.