June 25, 2010 at 12:18 pm
Hey guys,
I keep getting Invalid length parameter passed to the substring function error.
What am missing????
SELECT
A.PropertyName1 as 'Property'
, CASE WHEN SUBSTRING('Property',1,(CHARINDEX('APARTMENT',A.PROPERTYNAME1)-1)) = 'Villa Siena'
then 'Villa Siena**'
ELSE SUBSTRING('Property',1,(CHARINDEX('APARTMENT',A.PROPERTYNAME1)-1))
END AS PropertyName
, B.BLDGID+'-'+B.UNITID AS 'Apt'
, B.UNITTYPE AS 'Apt Type'
, B.CURRUSESQFTG AS 'Square Feet'
, B.MARKETRENT AS 'Market Rent'
, ISNULL(CONVERT(VARCHAR(10),B.VACATEDATE,101),'') AS 'Date Vacated'
, tic_objects.DBO.UDF_CONCAT_AMENITIES(B.PROPERTYID, B.UNITID, B.BLDGID) AS 'Amenities'
, ISNULL(CONVERT(VARCHAR(10),B.ActMadeReadyDate,101),'') AS 'Date Ready'
, D.MEMOTEXT
, CONVERT(VARCHAR(25),GETDATE(),107) as TodaysDate
, SUBSTRING(B.UNITTYPE,1,3) as 'BMR'
FROM IAMC_PROD.DBO.PROPERTY A
INNER JOIN IAMC_PROD.DBO.UNITWITHSTATUS B
ON A.PROPERTYID=B.PROPERTYID
AND CALCDATE = CONVERT(VARCHAR(10),GETDATE(),101)
LEFT JOIN IAMC_PROD.DBO.MemoHeader C
ON B.PROPERTYID=C.PROPERTYID
AND B.BLDGID=C.BLDGID
AND B.UNITID=C.UNITID
AND C.FORMCODE = '@DUS'
LEFT JOIN IAMC_PROD.DBO.MemoDetail D
ON C.PROPERTYID=D.PROPERTYID
AND C.MEMOID=D.MEMOID
AND C.FORMCODE=D.FORMCODE
INNER JOIN IAMC_PROD.DBO.ADDRESSBOOK E
ON A.PROPERTYADDRID=E.ADDRID
WHERE E.ADDRCITY IN ('Tustin','Irvine','Newport Beach','Newport Coast','Costa Mesa') -- Orange County
AND B.OCCUSTATUS = 'VA'
AND B.UNITSTATUS NOT IN ('W','D','M')
--AND C.FORMCODE = '@DUS'
AND SUBSTRING(UNITTYPE,3,1) <> 'C'
AND A.PROPERTYID NOT IN ('20430','20410','20440')
ORDER BY B.UNITTYPE, A.PROPERTYNAME1, B.BLDGID, B.UNITID
June 25, 2010 at 12:29 pm
You're getting a length of -1, which is invalid for SUBSTRING.
You need to use "PropertyName1" and not the alias name in your SUBSTRING -- 'Property' is a literal.
SELECT
A.PropertyName1 as 'Property'
, CASE WHEN SUBSTRING(A.PropertyName1,1,(CHARINDEX('APARTMENT',A.PROPERTYNAME1)-1)) = 'Villa Siena'
then 'Villa Siena**'
ELSE SUBSTRING(A.PropertyName1,1,(CHARINDEX('APARTMENT',A.PROPERTYNAME1)-1))
END AS PropertyName
Scott Pletcher, SQL Server MVP 2008-2010
June 25, 2010 at 12:30 pm
Assuming that the string
'APARTMENT'
is always in that column.
Otherwise you need to test it, and if not present, change the result so you don't pass -1 to a SUBSTRING function.
Scott Pletcher, SQL Server MVP 2008-2010
June 25, 2010 at 12:35 pm
Awesome, thanks SSC. Your second thought was correct. There was an instance where there was no apartment in the string. That was the error. Its an invalid length because its trying to look further to the left of -1. 🙂
June 25, 2010 at 12:38 pm
Btw, I'm Scott.
The SSC they show is for the site itself, SqlServerCentral 🙂
Scott Pletcher, SQL Server MVP 2008-2010
June 25, 2010 at 12:40 pm
Oh, my bad.
Thanks Scott! 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply