November 7, 2013 at 4:30 am
Hi there,
I am currently looking at someone else code and am getting an error when I try and run the view.
When I run the code directly in Management Studio it works with now errors - really confused.
The script is -
SELECT
TEN.[tenancy-ref] AS 'Tenancy Reference'
,CASE WHEN TEN.[curr-balance] >0
THEN TEN.[curr-balance]
ELSE 0 END AS 'Arrears'
,TEN.[curr-balance]+ TEN.[hb-arrears] AS 'Arrears Inc HB'
,TEN.[curr-balance]+ TEN.[hb-arrears] + TEN.[sp-arrears]AS 'Arrears Inc HB and SP'
,TEN.[net-rent] AS 'Net Rent'
,TEN.[pmt-method] AS 'Payment Method'
,TEN.[tncy-status] AS 'Tenancy Status'
,TEN.[corr-name1] AS 'Tenant Name'
,PLAC.[address1] AS 'Address 1'
,PLAC.[address2] AS 'Address 2'
,PLAC.[address3] AS 'Address 3'
,PLAC.[address4] AS 'Address 4'
,PLAC.[address5] AS 'Address 5'
,PLAC.[post-code] AS 'PostCode'
,CASE WHEN TEN.[curr-balance] <0
THEN TEN.[curr-balance]
ELSE 0 END AS 'PrePaid'
,TEN.[rent-group] AS 'Rent Group'
,TEN.[tncy-type] AS 'Tenancy Type'
,CASE WHEN TEN.[tncy-status]= 'FOR'
THEN LOC.[former-arrs-ofcr]
ELSE LOC.[arrears-ofcr]END AS 'Arrears Officer'
,OFFI.[OFCR-TITLE]+ ' '+ INITIALS + ' ' + [SURNAME] AS 'Arrears Officer Name'
,PLAC.[parish-code]AS 'Parish Code'
,LOC.[bedrooms] AS 'Bedrooms'
,LOC.[location-type] AS 'Location Type'
,LOC.[building-type] AS 'Building Type'
,LOC.[mgt-area]AS 'Management Area'
,LOC.[scheme] AS 'Scheme'
,LOC.[accounts-company]AS 'Accounts Company'
,TEN.[gross-rent] AS 'Gross Rent'
,TEN.[tncy-start] AS 'Tenancy StartDate'
,TEN.[tncy-end] AS 'Tenancy EndDate'
,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'
,TEN.[tncy-cat] AS 'Tenancy Category'
,LOC.[former-arrs-ofcr] AS 'Former Arrears Officer'
/* added by Mandy 19-11-12 ----------------------------*/
,TEN.[arrears-stage]AS'Arrears Stage'
,CASE WHEN (TEN.[net-rent]<=0 OR TEN.[net-rent] IS NULL OR (TEN.[curr-balance]+ TEN.[hb-arrears] + TEN.[sp-arrears]<=0))
THEN 0
ELSE (TEN.[curr-balance]+ TEN.[hb-arrears] + TEN.[sp-arrears])/TEN.[net-rent]
ENDAS 'Net Arrears Div By Net Rent'
,SUM(TRAS6.[TRANS-AMT])AS 'Week6'
,SUM(TRAS5.[TRANS-AMT])AS 'Week5'
,SUM(TRAS4.[TRANS-AMT])AS 'Week4'
,SUM(TRAS3.[TRANS-AMT])AS 'Week3'
,SUM(TRAS2.[TRANS-AMT])AS 'Week2'
,SUM(TRAS1.[TRANS-AMT])AS 'Week1'
/* ---------------------------------------------------*/
FROM dbo.[IH_RE-TENANCY] AS TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPLAC
ON
TEN.[tncy-sys-ref] = TENPLAC.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] AS LOC
ON
TENPLAC.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE]AS PLAC
ON
LOC.[place-ref] = PLAC.[place-ref]
LEFT OUTER JOINdbo.[IH_OFFICER] AS OFFI
ON
LOC.[arrears-ofcr] = OFFI.[OFFICER-CODE]
---Week Join - Week 6 Added by Mandy 19-11-12--------
LEFT OUTER JOINdbo.[IH_RE-TNCY-TRANS]AS TRAS6
ONTRAS6.[TNCY-SYS-REF]=TEN.[tncy-sys-ref]
ANDTRAS6.[TRANS-WEEK]=
(
SELECT MAX(WK.[TRASWEEK])
FROM(
SELECTDISTINCTTRA6.[TRANS-WEEK]AS'TRASWEEK'
FROMdbo.[IH_RE-TNCY-TRANS]AS TRA6
WHERETRA6.[TRANS-DATE]>GETDATE()-42
ANDTRA6.[ACCOUNT-TYPE]='IN'
GROUP BYTRA6.[TRANS-WEEK]
) AS WK
)
ANDTRAS6.[ACCOUNT-TYPE]='IN'
ANDTRAS6.[TRANS-DATE]>GETDATE()-42
-----------------------------------
---Week Join - Week 5 Added by Mandy 19-11-12--------
LEFT OUTER JOINdbo.[IH_RE-TNCY-TRANS]AS TRAS5
ONTRAS5.[TNCY-SYS-REF]=TEN.[tncy-sys-ref]
ANDTRAS5.[TRANS-WEEK]=
(
SELECT MAX(WK.[TRASWEEK])-1
FROM(
SELECTDISTINCTTRA5.[TRANS-WEEK]AS'TRASWEEK'
FROMdbo.[IH_RE-TNCY-TRANS]AS TRA5
WHERETRA5.[TRANS-DATE]>GETDATE()-42
ANDTRA5.[ACCOUNT-TYPE]='IN'
GROUP BYTRA5.[TRANS-WEEK]
) AS WK
)
ANDTRAS5.[ACCOUNT-TYPE]='IN'
ANDTRAS5.[TRANS-DATE]>GETDATE()-42
-----------------------------------
---Week Join - Week 4- Added by Mandy 19-11-12-------
LEFT OUTER JOINdbo.[IH_RE-TNCY-TRANS]AS TRAS4
ONTRAS4.[TNCY-SYS-REF]=TEN.[tncy-sys-ref]
ANDTRAS4.[TRANS-WEEK]=
(
SELECT MAX(WK.[TRASWEEK])-2
FROM(
SELECTDISTINCTTRA4.[TRANS-WEEK]AS'TRASWEEK'
FROMdbo.[IH_RE-TNCY-TRANS]AS TRA4
WHERETRA4.[TRANS-DATE]>GETDATE()-42
ANDTRA4.[ACCOUNT-TYPE]='IN'
GROUP BYTRA4.[TRANS-WEEK]
) AS WK
)
ANDTRAS4.[ACCOUNT-TYPE]='IN'
ANDTRAS4.[TRANS-DATE]>GETDATE()-42
-----------------------------------
---Week Join - Week 3- Added by Mandy 19-11-12-------
LEFT OUTER JOINdbo.[IH_RE-TNCY-TRANS]AS TRAS3
ONTRAS3.[TNCY-SYS-REF]=TEN.[tncy-sys-ref]
ANDTRAS3.[TRANS-WEEK]=
(
SELECT MAX(WK.[TRASWEEK])-3
FROM(
SELECTDISTINCTTRA3.[TRANS-WEEK]AS'TRASWEEK'
FROMdbo.[IH_RE-TNCY-TRANS]AS TRA3
WHERETRA3.[TRANS-DATE]>GETDATE()-42
ANDTRA3.[ACCOUNT-TYPE]='IN'
GROUP BYTRA3.[TRANS-WEEK]
) AS WK
)
ANDTRAS3.[ACCOUNT-TYPE]='IN'
ANDTRAS3.[TRANS-DATE]>GETDATE()-42
-----------------------------------
---Week Join - Week 2-Added by Mandy 19-11-12 -------
LEFT OUTER JOINdbo.[IH_RE-TNCY-TRANS]AS TRAS2
ONTRAS2.[TNCY-SYS-REF]=TEN.[tncy-sys-ref]
ANDTRAS2.[TRANS-WEEK]=
(
SELECT MAX(WK.[TRASWEEK])-4
FROM(
SELECTDISTINCTTRA2.[TRANS-WEEK]AS'TRASWEEK'
FROMdbo.[IH_RE-TNCY-TRANS]AS TRA2
WHERETRA2.[TRANS-DATE]>GETDATE()-42
ANDTRA2.[ACCOUNT-TYPE]='IN'
GROUP BYTRA2.[TRANS-WEEK]
) AS WK
)
ANDTRAS2.[ACCOUNT-TYPE]='IN'
ANDTRAS2.[TRANS-DATE]>GETDATE()-42
-----------------------------------
---Week Join - Week 1-Added by Mandy 19-11-12 -------
LEFT OUTER JOINdbo.[IH_RE-TNCY-TRANS]AS TRAS1
ONTRAS1.[TNCY-SYS-REF]=TEN.[tncy-sys-ref]
ANDTRAS1.[TRANS-WEEK]=
(
SELECT MAX(WK.[TRASWEEK])-5
FROM(
SELECTDISTINCTTRA1.[TRANS-WEEK]AS'TRASWEEK'
FROMdbo.[IH_RE-TNCY-TRANS]AS TRA1
WHERETRA1.[TRANS-DATE]>GETDATE()-42
ANDTRA1.[ACCOUNT-TYPE]='IN'
GROUP BYTRA1.[TRANS-WEEK]
) AS WK
)
ANDTRAS1.[ACCOUNT-TYPE]='IN'
ANDTRAS1.[TRANS-DATE]>GETDATE()-42
-----------------------------------
WHERE TENPLAC.[prime-place] = '1'
GROUP BYTEN.[tenancy-ref] ,TEN.[pmt-method] ,TEN.[curr-balance],TEN.[tncy-status]
,LOC.[arrears-ofcr],LOC.[scheme],LOC.[mgt-area],TEN.[corr-name1]
,PLAC.[address1],PLAC.[address2],PLAC.[address3],PLAC.[address4],PLAC.[address5]
,PLAC.[post-code],LOC.[mgt-area],TEN.[tncy-cat],TEN.[tncy-type]
,TEN.[hb-arrears],TEN.[sp-arrears],TEN.[net-rent] ,TEN.[gross-rent],TEN.[rent-group],LOC.[former-arrs-ofcr]
,OFFI.[OFCR-TITLE],OFFI.[INITIALS],OFFI.[SURNAME],PLAC.[parish-code],LOC.[bedrooms],LOC.[location-type]
,LOC.[building-type],LOC.[accounts-company],TEN.[tncy-start],TEN.[tncy-end],TEN.[arrears-stage]
When I save this as a view and then run the view or try and attach it to a source in ssis I get the error -
Error Message: Conversion failed when converting the varchar value 'TRASWEEK to data type int
As soon as I take the following out the SELECT statement - the view works -
,SUM(TRAS6.[TRANS-AMT])AS 'Week6'
,SUM(TRAS5.[TRANS-AMT])AS 'Week5'
,SUM(TRAS4.[TRANS-AMT])AS 'Week4'
,SUM(TRAS3.[TRANS-AMT])AS 'Week3'
,SUM(TRAS2.[TRANS-AMT])AS 'Week2'
,SUM(TRAS1.[TRANS-AMT])AS 'Week1'
Can some one please advise as I'm totally at a loss. Thanks
November 7, 2013 at 5:34 am
Bit more information as I'm trying to get to the bottom of this -
When I run the following part of the script in "New Query" window - it works.
SELECT MAX (WK.[TRASWEEK])AS 'TRASWEEK'
FROM(
SELECTDISTINCTTRA6.[TRANS-WEEK] AS'TRASWEEK'
FROMdbo.[IH_RE-TNCY-TRANS]AS TRA6
WHERETRA6.[TRANS-DATE]>GETDATE()-42
ANDTRA6.[ACCOUNT-TYPE]='IN'
GROUP BYTRA6.[TRANS-WEEK]
) AS WK
This pulls me back a result. The column heading being "TRASWEEK" and the result is just one item. That being "27".
I copy and paste the exact same query into a view and execute it -
I don't get a result of "27" I get a result of "TRASWEEK" ?? Why would it be doing this.
In SQL2000 I have no issues with this code at all. But in 2012 it keeps changing the output in the View, but not in the "New Query" window in Management Studio.
November 7, 2013 at 5:53 am
Ok.
The view is taking what I am pasting and replacing it with MAX('TRASWEEK') AS TRASWEEK
Which means my result is coming out as text 'TRASWEEK'. How do I get it to pull the Maximum TRASWEEK from the sub query "WK" below
SELECT MAX(WK.[TRASWEEK]) AS TRASWEEK
FROM (SELECT DISTINCT [TRANS-WEEK] AS 'TRASWEEK'
FROM dbo.[IH_RE-TNCY-TRANS] AS TRA6
WHERE ([TRANS-DATE] > GETDATE() - 42) AND ([ACCOUNT-TYPE] = 'IN')
GROUP BY [TRANS-WEEK]) AS WK
November 8, 2013 at 1:39 am
To anyone interested - all I had to do was the following -
SELECT MAX([WK].[TRASWEEK]) AS Test
FROM
(
SELECT DISTINCT [TRANS-WEEK] AS TRASWEEK
FROM dbo.[IH_RE-TNCY-TRANS] AS TRA6
WHERE ([TRANS-DATE] > GETDATE() - 42) AND ([ACCOUNT-TYPE] = 'IN')
GROUP BY [TRANS-WEEK]
) AS WK
Didn't like TRASWEEK being in single quotes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply