Syntax error converting the varchar value '510.3' to a column of data type int.

Question

Post reply

Syntax error converting the varchar value '510.3' to a column of data type int.

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
June 25, 2010 at 4:47 am

#72050

Hi there,
Getting the following error message when running the following code.
SELECT [TenancyReference]
,[PlaceReference]
,[PersonReference]
,[Person Name]
,[NameOnTenancy]
,[TenantOrOccupant]
,[TenancyType]
,[TenancyCategory]
,[TenancyStartDate]
,[Current Balance]
,[EstateCode]
,[LocationType]
,[BuildingType]
,[ManagementType]
,[Scheme]
,[Patch / Arrears Officer]
,[NumberOfBedrooms]
,[Address]
,[PostCode]
,[Age]
,[Gender]
,[OriginCode]
,[Vulnerable]
,[LoggedComplaint0910]
,[LoggedComplaint1011]
,[MethodOfPayment]
,[Age Range] = CASE
WHEN [Age] BETWEEN 16 AND 24 THEN '18-24'
WHEN [Age] BETWEEN 25 AND 44 THEN '25-44'
WHEN [Age] BETWEEN 45 AND 59 THEN '45-59'
WHEN [Age] BETWEEN 60 AND 64 THEN '60-64'
WHEN [Age] BETWEEN 65 AND 74 THEN '65-74'
WHEN [Age] > 75 THEN '75+'
ELSE 'No Age Range Available' END
,[Estate Officer]
,[Arrears]
,[Arrears Inc HB Excluding Credits]
,[Arrears Inc HB and SP Excluding Credits]
,[WONT WORK]
FROM
(
SELECT TEN.[tenancy-ref] AS TenancyReference
, LOC.[place-ref] AS PlaceReference
, PER.[PERSON-REF] AS PersonReference
, PER.[PERSON-TITLE] + ' ' + PER.FORENAMES + ' ' + PER.SURNAME AS [Person Name]
, TEN.[corr-name1] AS NameOnTenancy
, CASE WHEN TENPER.[on-tncy] = 'yes' THEN 'Tenant' ELSE 'Occupant' END AS TenantOrOccupant
, TEN.[tncy-type] AS TenancyType
, TEN.[tncy-cat] AS TenancyCategory
, TEN.[tncy-start] AS TenancyStartDate
, TEN.[curr-balance] AS [Current Balance]
, LOC.[estate-code] AS EstateCode
, LOC.[location-type] AS LocationType
, LOC.[building-type] AS BuildingType
, LOC.[mgt-area] AS ManagementType
, LOC.scheme AS Scheme
, LOC.[arrears-ofcr] AS [Patch / Arrears Officer]
, LOC.bedrooms AS NumberOfBedrooms
, PLA.address1 + ',' + PLA.address2 + ',' + PLA.address3 AS Address
, PLA.[post-code] AS PostCode
, PER.[D-O-B] AS DateOfBirth, DATEDIFF([year]
, PER.[D-O-B], GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(PER.[D-O-B])
THEN 0 WHEN MONTH(GETDATE()) = MONTH(PER.[D-O-B]) AND
DAY(GETDATE()) >= DAY(PER.[D-O-B])
THEN 0 ELSE 1 END AS Age
, PER.GENDER AS Gender
, PER.[ORIGIN-CODE] AS OriginCode
, CASE WHEN EXTEN.[POTENTIALLY-VULNERABLE] IS NULL
THEN 'No' ELSE 'Yes'
END AS 'Vulnerable'
,CASE WHEN COMPLAINTS09.[PERSON-REF] IS NULL
THEN 'No' ELSE 'Yes'
END AS 'LoggedComplaint0910'
,CASE WHEN COMPLAINTS10.[PERSON-REF] IS NULL
THEN 'No' ELSE 'Yes'
END AS 'LoggedComplaint1011'
, TEN.[pmt-method] AS [MethodOfPayment]
, LOC.[estate-ofcr] AS 'Estate Officer'
,CASE WHEN TEN.[curr-balance] >0
THEN TEN.[curr-balance]
ELSE 0 END AS 'Arrears'
,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears] <0
THEN 0
ELSE TEN.[curr-balance]+ TEN.[hb-arrears]END AS 'Arrears Inc HB Excluding Credits'
,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears] <0
THEN 0
ELSE TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears]END AS 'Arrears Inc HB and SP Excluding Credits'
,CASE WHEN [Arrears] >0
THEN '1'
ELSE '2' END AS 'WONT WORK'
FROM
dbo.[IH_RE-TENANCY] TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] TENPL
ON
TEN.[tncy-sys-ref] = TENPL.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] LOC
ON
TENPL.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE] PLA
ON
LOC.[place-ref] = PLA.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PERSON] TENPER
ON
TEN.[tncy-sys-ref] = TENPER.[TNCY-SYS-REF]
INNER JOIN
dbo.[CORE_CO-PERSON] PER
ON
TENPER.[PERSON-REF] = PER.[PERSON-REF]
LEFT OUTER JOIN
dbo.[CORE_CO-PERSON-EXTENSION] EXTEN
ON
EXTEN.[PERSON-REF] = PER.[PERSON-REF]
LEFT OUTER JOIN
(SELECT DISTINCTORIG.[PERSON-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND COM.[crt-date] >= CONVERT(DATETIME, '2009-04-01 00:00:00', 102)
AND COM.[crt-date] <= CONVERT(DATETIME, '2010-03-31 00:00:00', 102)
AND ORIG.[PERSON-TYPE] = 'O'
AND ORIG.[PLACE-REF] not like '*%') COMPLAINTS09
ON PER.[PERSON-REF] = COMPLAINTS09.[PERSON-REF]
LEFT OUTER JOIN
(SELECT DISTINCTORIG.[PERSON-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND COM.[crt-date] >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102)
AND COM.[crt-date] <= CONVERT(DATETIME, '2011-03-31 00:00:00', 102)
AND ORIG.[PERSON-TYPE] = 'O'
AND ORIG.[PLACE-REF] not like '*%') COMPLAINTS10
ON PER.[PERSON-REF] = COMPLAINTS10.[PERSON-REF]
WHERE (TEN.[tncy-status] = 'CUR')
AND (TEN.[tenancy-ref] NOT LIKE 'FS%')
AND (TENPL.[prime-place] = 'yes')
AND (TENPER.[END-DATE] IS NULL)
)alias
WHERE(TenantOrOccupant = 'Tenant')
It does return results when I take out the following -
SELECT [TenancyReference]
,[PlaceReference]
,[PersonReference]
,[Person Name]
,[NameOnTenancy]
,[TenantOrOccupant]
,[TenancyType]
,[TenancyCategory]
,[TenancyStartDate]
,[Current Balance]
,[EstateCode]
,[LocationType]
,[BuildingType]
,[ManagementType]
,[Scheme]
,[Patch / Arrears Officer]
,[NumberOfBedrooms]
,[Address]
,[PostCode]
,[Age]
,[Gender]
,[OriginCode]
,[Vulnerable]
,[LoggedComplaint0910]
,[LoggedComplaint1011]
,[MethodOfPayment]
,[Age Range] = CASE
WHEN [Age] BETWEEN 16 AND 24 THEN '18-24'
WHEN [Age] BETWEEN 25 AND 44 THEN '25-44'
WHEN [Age] BETWEEN 45 AND 59 THEN '45-59'
WHEN [Age] BETWEEN 60 AND 64 THEN '60-64'
WHEN [Age] BETWEEN 65 AND 74 THEN '65-74'
WHEN [Age] > 75 THEN '75+'
ELSE 'No Age Range Available' END
,[Estate Officer]
,[Arrears]
,[Arrears Inc HB Excluding Credits]
,[Arrears Inc HB and SP Excluding Credits]
--,[WONT WORK]
FROM
(
SELECT TEN.[tenancy-ref] AS TenancyReference
, LOC.[place-ref] AS PlaceReference
, PER.[PERSON-REF] AS PersonReference
, PER.[PERSON-TITLE] + ' ' + PER.FORENAMES + ' ' + PER.SURNAME AS [Person Name]
, TEN.[corr-name1] AS NameOnTenancy
, CASE WHEN TENPER.[on-tncy] = 'yes' THEN 'Tenant' ELSE 'Occupant' END AS TenantOrOccupant
, TEN.[tncy-type] AS TenancyType
, TEN.[tncy-cat] AS TenancyCategory
, TEN.[tncy-start] AS TenancyStartDate
, TEN.[curr-balance] AS [Current Balance]
, LOC.[estate-code] AS EstateCode
, LOC.[location-type] AS LocationType
, LOC.[building-type] AS BuildingType
, LOC.[mgt-area] AS ManagementType
, LOC.scheme AS Scheme
, LOC.[arrears-ofcr] AS [Patch / Arrears Officer]
, LOC.bedrooms AS NumberOfBedrooms
, PLA.address1 + ',' + PLA.address2 + ',' + PLA.address3 AS Address
, PLA.[post-code] AS PostCode
, PER.[D-O-B] AS DateOfBirth, DATEDIFF([year]
, PER.[D-O-B], GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(PER.[D-O-B])
THEN 0 WHEN MONTH(GETDATE()) = MONTH(PER.[D-O-B]) AND
DAY(GETDATE()) >= DAY(PER.[D-O-B])
THEN 0 ELSE 1 END AS Age
, PER.GENDER AS Gender
, PER.[ORIGIN-CODE] AS OriginCode
, CASE WHEN EXTEN.[POTENTIALLY-VULNERABLE] IS NULL
THEN 'No' ELSE 'Yes'
END AS 'Vulnerable'
,CASE WHEN COMPLAINTS09.[PERSON-REF] IS NULL
THEN 'No' ELSE 'Yes'
END AS 'LoggedComplaint0910'
,CASE WHEN COMPLAINTS10.[PERSON-REF] IS NULL
THEN 'No' ELSE 'Yes'
END AS 'LoggedComplaint1011'
, TEN.[pmt-method] AS [MethodOfPayment]
, LOC.[estate-ofcr] AS 'Estate Officer'
,CASE WHEN TEN.[curr-balance] >0
THEN TEN.[curr-balance]
ELSE 0 END AS 'Arrears'
,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears] <0
THEN 0
ELSE TEN.[curr-balance]+ TEN.[hb-arrears]END AS 'Arrears Inc HB Excluding Credits'
,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears] <0
THEN 0
ELSE TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears]END AS 'Arrears Inc HB and SP Excluding Credits'
--CASE WHEN [Arrears] >0
--THEN '1'
--ELSE '2' END AS 'WONT WORK'
-
FROM
dbo.[IH_RE-TENANCY] TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] TENPL
ON
TEN.[tncy-sys-ref] = TENPL.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] LOC
ON
TENPL.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE] PLA
ON
LOC.[place-ref] = PLA.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PERSON] TENPER
ON
TEN.[tncy-sys-ref] = TENPER.[TNCY-SYS-REF]
INNER JOIN
dbo.[CORE_CO-PERSON] PER
ON
TENPER.[PERSON-REF] = PER.[PERSON-REF]
LEFT OUTER JOIN
dbo.[CORE_CO-PERSON-EXTENSION] EXTEN
ON
EXTEN.[PERSON-REF] = PER.[PERSON-REF]
LEFT OUTER JOIN
(SELECT DISTINCTORIG.[PERSON-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND COM.[crt-date] >= CONVERT(DATETIME, '2009-04-01 00:00:00', 102)
AND COM.[crt-date] <= CONVERT(DATETIME, '2010-03-31 00:00:00', 102)
AND ORIG.[PERSON-TYPE] = 'O'
AND ORIG.[PLACE-REF] not like '*%') COMPLAINTS09
ON PER.[PERSON-REF] = COMPLAINTS09.[PERSON-REF]
LEFT OUTER JOIN
(SELECT DISTINCTORIG.[PERSON-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND COM.[crt-date] >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102)
AND COM.[crt-date] <= CONVERT(DATETIME, '2011-03-31 00:00:00', 102)
AND ORIG.[PERSON-TYPE] = 'O'
AND ORIG.[PLACE-REF] not like '*%') COMPLAINTS10
ON PER.[PERSON-REF] = COMPLAINTS10.[PERSON-REF]
WHERE (TEN.[tncy-status] = 'CUR')
AND (TEN.[tenancy-ref] NOT LIKE 'FS%')
AND (TENPL.[prime-place] = 'yes')
AND (TENPER.[END-DATE] IS NULL)
)alias
WHERE(TenantOrOccupant = 'Tenant')
Does anyone have any ideas where I need to do the conversion?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 1

Just a bit more information the [Arrears] is calculated via a CASE statement which uses the TEN.[curr-balance].

The TEN.[curr-balance] is held as a data type of Money.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Ryan

The column 'Arrears' is generated by a CASE in the inner query.

CASE WHEN TEN.[curr-balance] >0

THEN TEN.[curr-balance]

ELSE 0 END AS 'Arrears'

There are two ways that you could operate on the value in this column: by referring to the code which generates it, in the inner query, something like this:

CASE WHEN TEN.[curr-balance] > 0

THEN TEN.[curr-balance]

ELSE 0 END AS 'Arrears',

CASE WHEN (CASE WHEN TEN.[curr-balance] > 0

THEN TEN.[curr-balance]

ELSE 0 END) >0

THEN '1'

ELSE '2' END AS 'WONT WORK'

or by referring to it in the outer SELECT directly, like this:

CASE WHEN [Arrears] >0

THEN '1'

ELSE '2' END AS 'WONT WORK'

There's an obvious error in your code:

WHEN [Age] BETWEEN 16 AND 24 THEN '18-24'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

Thank You Chris. Now all working.

Please see updated code.

Oh and thanks for the obvious error that you found 🙂

SELECT [TenancyReference]

,[PlaceReference]

,[PersonReference]

,[Person Name]

,[NameOnTenancy]

,[TenantOrOccupant]

,[TenancyType]

,[TenancyCategory]

,[TenancyStartDate]

,[Current Balance]

,[EstateCode]

,[LocationType]

,[BuildingType]

,[ManagementType]

,[Scheme]

,[Patch / Arrears Officer]

,[NumberOfBedrooms]

,[Address]

,[PostCode]

,[Age]

,[Gender]

,[OriginCode]

,[Vulnerable]

,[LoggedComplaint0910]

,[LoggedComplaint1011]

,[MethodOfPayment]

,[Age Range] = CASE

WHEN [Age] BETWEEN 16 AND 24 THEN '18-24'

WHEN [Age] BETWEEN 25 AND 44 THEN '25-44'

WHEN [Age] BETWEEN 45 AND 59 THEN '45-59'

WHEN [Age] BETWEEN 60 AND 64 THEN '60-64'

WHEN [Age] BETWEEN 65 AND 74 THEN '65-74'

WHEN [Age] > 75 THEN '75+'

ELSE 'No Age Range Available' END

,[Estate Officer]

,[Arrears]

,[Arrears Inc HB Excluding Credits]

,[Arrears Inc HB and SP Excluding Credits]

,CASE WHEN [Arrears] >0

THEN '1'

ELSE '2' END AS 'NOW WORKING'

FROM

(

SELECT TEN.[tenancy-ref] AS TenancyReference

, LOC.[place-ref] AS PlaceReference

, PER.[PERSON-REF] AS PersonReference

, PER.[PERSON-TITLE] + ' ' + PER.FORENAMES + ' ' + PER.SURNAME AS [Person Name]

, TEN.[corr-name1] AS NameOnTenancy

, CASE WHEN TENPER.[on-tncy] = 'yes' THEN 'Tenant' ELSE 'Occupant' END AS TenantOrOccupant

, TEN.[tncy-type] AS TenancyType

, TEN.[tncy-cat] AS TenancyCategory

, TEN.[tncy-start] AS TenancyStartDate

, TEN.[curr-balance] AS [Current Balance]

, LOC.[estate-code] AS EstateCode

, LOC.[location-type] AS LocationType

, LOC.[building-type] AS BuildingType

, LOC.[mgt-area] AS ManagementType

, LOC.scheme AS Scheme

, LOC.[arrears-ofcr] AS [Patch / Arrears Officer]

, LOC.bedrooms AS NumberOfBedrooms

, PLA.address1 + ',' + PLA.address2 + ',' + PLA.address3 AS Address

, PLA.[post-code] AS PostCode

, PER.[D-O-B] AS DateOfBirth, DATEDIFF([year]

, PER.[D-O-B], GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(PER.[D-O-B])

THEN 0 WHEN MONTH(GETDATE()) = MONTH(PER.[D-O-B]) AND

DAY(GETDATE()) >= DAY(PER.[D-O-B])

THEN 0 ELSE 1 END AS Age

, PER.GENDER AS Gender

, PER.[ORIGIN-CODE] AS OriginCode

, CASE WHEN EXTEN.[POTENTIALLY-VULNERABLE] IS NULL

THEN 'No' ELSE 'Yes'

END AS 'Vulnerable'

,CASE WHEN COMPLAINTS09.[PERSON-REF] IS NULL

THEN 'No' ELSE 'Yes'

END AS 'LoggedComplaint0910'

,CASE WHEN COMPLAINTS10.[PERSON-REF] IS NULL

THEN 'No' ELSE 'Yes'

END AS 'LoggedComplaint1011'

, TEN.[pmt-method] AS [MethodOfPayment]

, LOC.[estate-ofcr] AS 'Estate Officer'

,CASE WHEN TEN.[curr-balance] >0

THEN TEN.[curr-balance]

ELSE 0 END AS 'Arrears'

,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears] <0

THEN 0

ELSE TEN.[curr-balance]+ TEN.[hb-arrears]END AS 'Arrears Inc HB Excluding Credits'

,CASE WHEN TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears] <0

THEN 0

ELSE TEN.[curr-balance]+ TEN.[hb-arrears]+ TEN.[sp-arrears]END AS 'Arrears Inc HB and SP Excluding Credits'

FROM

dbo.[IH_RE-TENANCY] TEN

INNER JOIN

dbo.[IH_RE-TNCY-PLACE] TENPL

ON

TEN.[tncy-sys-ref] = TENPL.[tncy-sys-ref]

INNER JOIN

dbo.[IH_IH-LOCATION] LOC

ON

TENPL.[place-ref] = LOC.[place-ref]

INNER JOIN

dbo.[CORE_CO-PLACE] PLA

ON

LOC.[place-ref] = PLA.[place-ref]

INNER JOIN

dbo.[IH_RE-TNCY-PERSON] TENPER

ON

TEN.[tncy-sys-ref] = TENPER.[TNCY-SYS-REF]

INNER JOIN

dbo.[CORE_CO-PERSON] PER

ON

TENPER.[PERSON-REF] = PER.[PERSON-REF]

LEFT OUTER JOIN

dbo.[CORE_CO-PERSON-EXTENSION] EXTEN

ON

EXTEN.[PERSON-REF] = PER.[PERSON-REF]

LEFT OUTER JOIN

(SELECT DISTINCTORIG.[PERSON-REF]

FROM dbo.[IH_EM-COMMUNICATION]AS COM

INNER JOIN

dbo.[IH_EM-ORIGINATOR]AS ORIG

ON

COM.[comm-reference] = ORIG.[comm-reference]

WHERE COM.[comm-type] = 'COMP'

AND COM.[crt-date] >= CONVERT(DATETIME, '2009-04-01 00:00:00', 102)

AND COM.[crt-date] <= CONVERT(DATETIME, '2010-03-31 00:00:00', 102)

AND ORIG.[PERSON-TYPE] = 'O'

AND ORIG.[PLACE-REF] not like '*%') COMPLAINTS09

ON PER.[PERSON-REF] = COMPLAINTS09.[PERSON-REF]

LEFT OUTER JOIN

(SELECT DISTINCTORIG.[PERSON-REF]

FROM dbo.[IH_EM-COMMUNICATION]AS COM

INNER JOIN

dbo.[IH_EM-ORIGINATOR]AS ORIG

ON

COM.[comm-reference] = ORIG.[comm-reference]

WHERE COM.[comm-type] = 'COMP'

AND COM.[crt-date] >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102)

AND COM.[crt-date] <= CONVERT(DATETIME, '2011-03-31 00:00:00', 102)

AND ORIG.[PERSON-TYPE] = 'O'

AND ORIG.[PLACE-REF] not like '*%') COMPLAINTS10

ON PER.[PERSON-REF] = COMPLAINTS10.[PERSON-REF]

WHERE (TEN.[tncy-status] = 'CUR')

AND (TEN.[tenancy-ref] NOT LIKE 'FS%')

AND (TENPL.[prime-place] = 'yes')

AND (TENPER.[END-DATE] IS NULL)

)alias

WHERE(TenantOrOccupant = 'Tenant')