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.
