Good Evening Everyone
i have following Query, that gives me an Error
<br />
<br />
<br />
SELECT IDENTITY(int, 1,1) AS REC_NO, LIS_KEY, <br />
FUNC_KEY, <br />
UNIT_NO, RIGHTS_ZONING, USE_CODE, OWNER, RATEABILITY, <br />
EXCLUSION, MARKET_VALUE, EFFECTIVE_DATE, CATEGORY_CODE, <br />
SUPPLE_NUM, AREA, PROPERTY_ID, OLD_MARKET_VALUE, VALUATION_ID, <br />
REASON, SORT_DATE<br />
INTO SDE.EXPORT_OITPS_GV_VUYISWA<br />
FROM<br />
(<br />
SELECT DISTINCT TOP 100 PERCENT P.LIS_KEY, <br />
CASE LEN(FUNC_KEY)<br />
WHEN 8 THEN SUBSTRING(NEW_ATTRIB_CODE,5,1)+ '0' + SUBSTRING(FUNC_KEY, 6, 3)<br />
ELSE SUBSTRING(FUNC_KEY,5, 5)<br />
End AS UNIT_NO,<br />
V.NEW_ATTRIB_CODE, P.ATTRIB_CODE,<br />
SUBSTRING(NEW_ATTRIB_CODE, 1, 2) AS RIGHTS_ZONING, <br />
SUBSTRING(NEW_ATTRIB_CODE, 3, 2) AS USE_CODE, <br />
ISNULL(OWN_NAME, '') AS OWNER, <br />
CASE LEN(FUNC_KEY)<br />
WHEN 8 THEN SUBSTRING(FUNC_KEY, 1, 5) + '0' + SUBSTRING(FUNC_KEY, 6, 3)<br />
ELSE FUNC_KEY<br />
END AS FUNC_KEY,<br />
CASE SUBSTRING(P.ATTRIB_CODE, 7, 1) <br />
WHEN '1' THEN 'R'<br />
WHEN '4' THEN 'E'<br />
ELSE 'N'<br />
END AS RATEABILITY, -- CASE 7TH WHEN 1 = R , 4 = E, ELSE = N<br />
CASE --P.PROP_CATEGORY_ID<br />
WHEN P.PROP_CATEGORY_ID = '2' THEN 'RES'<br />
WHEN P.PROP_CATEGORY_ID = '4' THEN 'PSI'<br />
WHEN SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '41' OR SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '42' THEN 'REL'<br />
ELSE ' ' <br />
END AS EXCLUSION,<br />
CONVERT(DECIMAL, ISNULL(NEW_IMPROVED_VALUE, -1)) AS MARKET_VALUE, <br />
(<br />
CASE WHEN CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE)) < 10<br />
THEN '0' + CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))<br />
ELSE CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))<br />
END +<br />
CASE WHEN CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE)) < 10<br />
THEN '0' + CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))<br />
ELSE CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))<br />
END + <br />
CONVERT(VARCHAR, DATEPART(YYYY, EFFECTIVE_DATE))) AS EFFECTIVE_DATE,<br />
(SELECT SUBSTRING(NEW_ATTRIB_CODE,7,2)<br />
FROM SDE.VALUATION V INNER JOIN SDE.PROPERTY PS<br />
ON V.PROPERTY_ID = PS.PROPERTY_ID<br />
) AS CATEGORY_CODE,<br />
' ' AS SUPPLE_NUM,<br />
CONVERT(INT, ISNULL(P.ACTUAL_EXTENT, 0)) AS AREA, P.PROPERTY_ID,<br />
0 AS OLD_MARKET_VALUE, VALUATION_ID, LU_V.VAL_REASON AS REASON, <br />
V.STATUS_DATE AS SORT_DATE<br />
<br />
FROM sde.PROPERTY P INNER JOIN SDE.VALUATION V--sde.VALUATION V<br />
ON P.PROPERTY_ID = V.PROPERTY_ID<br />
LEFT JOIN ( SELECT GISCODE, MIN(OWN_NAME) AS OWN_NAME<br />
FROM SDE.VW_PROPERTY_DEED<br />
GROUP BY GISCODE) D<br />
ON P.LIS_KEY = D.GISCODE<br />
LEFT JOIN SDE.LU_VAL_REASON LU_V<br />
ON V.VAL_REASON_ID = LU_V.VAL_REASON_ID<br />
WHERE --(<br />
--V.PROCESS_DATE IS NULL) AND <br />
P.ARCHIVE_DATE IS NULL AND<br />
V.ARCHIVE_DATE IS NULL AND<br />
V.VAL_REASON_ID = 1 AND <br />
LU_V.MULTIPLE = 0 AND<br />
V.EFFECTIVE_DATE = '2008/07/01'<br />
ORDER BY SORT_DATE<br />
) TMP
It gives me an Error
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
(0 row(s) affected)
How can i Fix this query
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|