July 22, 2014 at 3:22 pm
Hi All,
I've got this CTE and it's giving me fits. I'm getting the following Error which I can't track down.
Conversion failed when converting the varchar value 'Actual' to data type int.
The problem is I have no column in any table called Actual. I'm not even sure where to begin. I've tried breaking the CTE into parts, and it appears to fail in the 1st half.
Any help is appreciated.
WITH MktRevAvg(CustomerId, OpportuniytID, AvgMktRevenue)
AS
--drop table #MktRevAvg
(
SELECT TotMktRev.CustomerServiceNumberID AS CustomerServiceNumberID
,TotMktRev.OpportunityID AS OpportunityID
,Coalesce((SUM(TotMktRev.TotalMktRevenue) * 12),0) / TotMktRev.MonthsInAverage AS AvgMktRevenue
FROM
(
SELECT
csn.CustomerServiceNumberID AS CustomerServiceNumberID --Swap this line and Next line after final Financial Fact table is completed.
--cus.CustomerIDAS CUSCustomerID
-- ,csn.CustomerID AS CustomerID
,co.CompanyID as CompanyID
,o.OpportunityID
,m.ModalityId
,csn.OrgUnitID
,o.Forecasted_Commencement_Renewal_Date__c
,COALESCE(SUM(ff.MktRevenue),0) AS TotalMktRevenue
,per.[Date] AS SnapShotPeriod
,MaxDate.MaxDate
,DATEDIFF(mm, CAST(RTRIM(CAST(MONTH(DATEADD(mm, 1, o.Forecasted_Commencement_Renewal_Date__c)) AS CHAR(2))) + '/01/' + CAST(YEAR(o.Forecasted_Commencement_Renewal_Date__c) AS CHAR(4)) AS DATETIME), MaxDate.MaxDate) + 1 AS MonthsInAverage
FROM
[ADW].sf.Opportunity o
INNER JOIN [ADW].[Dim].[CustomerServiceNumber] csn ON o.Customer_Number2__c = csn.CustomerServiceNumber
--INNER JOIN dim.Customer cus ON cus.CustomerID = csn.CustomerID
INNER JOIN dim.Company co on csn.CompanyID = co.CompanyID
INNER JOIN dim.Modality m on csn.ModalityID = m.ModalityId
INNER JOIN [adw].[fact].[FinancialFact] ff ON csn.CustomerServiceNumberID = ff.CustomerServiceNumberID
INNER JOIN dim.Period per ON ff.DateID = per.DateID
INNER JOIN
(
SELECT DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(YEAR(MAX(per.[Date])) AS CHAR(4)) + '-' + CAST(MONTH(MAX(per.[Date])) AS VARCHAR(2)) + '-01' AS SMALLDATETIME))) AS MaxDate
FROM [adw].[fact].[FinancialFact] ff (NOLOCK)
INNER JOIN dim.Period per (NOLOCK) ON ff.DateID = per.DateID
WHERE Scenario = 1 AND ff.MktRevenue is not null --coalesce(ff.MktRevenue,0) > 0
) MaxDate ON 1 = 1
WHERE per.[Date] BETWEEN CAST(RTRIM(CAST(MONTH(DATEADD(mm, 1, o.Forecasted_Commencement_Renewal_Date__c)) AS CHAR(2))) + '/01/' + CAST(YEAR(o.Forecasted_Commencement_Renewal_Date__c) AS CHAR(4)) AS DATETIME)
AND MaxDate.MaxDate
AND o.Type = 'NAS'
AND o.Commit_Status__c = 'Forecast'
AND ff.Scenario = 1
AND o.Customer_Number2__c IS NOT NULL
AND ff.MktRevenue IS NOT NULL
GROUP BY
csn.CustomerServiceNumberID
--,csn.CustomerID
,co.CompanyID
,o.OpportunityId
,m.ModalityId
,csn.OrgUnitID
,per.[Date]
,o.Forecasted_Commencement_Renewal_Date__c
,MaxDate.MaxDate
) AS TotMktRev
GROUP BY
TotMktRev.CustomerServiceNumberID
,TotMktRev.CompanyID
,TotMktRev.OpportunityID
,TotMktRev.MonthsInAverage
)
,
Sales AS
(
SELECT DISTINCT
COALESCE(csn.CustomerServiceNumberID, '00000') CustomerServiceNumberID
--cus.CustomerID
,co.CompanyID As CompanyID
,COALESCE(m.ModalityID,NULL) ModalityID
,3 As ScenarioID
,COALESCE(csn.OrgUnitID,NULL) SubLatID
,p.DateID
,COALESCE(o.OpportunityID, NULL) OpportunityID
,o.HNI__c HNI
,'SalesForce' LoadSource
,CONVERT(DATE, GETDATE(), 112) LoadDate
,CAST((CAST(YEAR(GETDATE()) AS CHAR(4)) + CASE WHEN LEN(CAST(MONTH(GETDATE()) AS CHAR(2))) < 2 THEN '-0' + CAST(MONTH(GETDATE()) AS CHAR(1)) ELSE '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) END + '-01') AS SMALLDATETIME) AS SnapShotPeriod
,sum(o.Annualized_Contract_Value__c) AnnualizedContractValue
,sum(o.Forecasted_Annualized_Contract_Value__c) ForecastedAnnualizedContractValue
,Sum(o.Adjusted_Forecast__c) AdjustedForecastValue
,Sum(o.Adjusted_Forecast__c) NASForecastDollars
,CASE WHEN nasexcep.NASLookback is null then mra.AvgMktRevenue else nasexcep.NASLookback END NASLookback
FROM [ADW].[sf].[Opportunity] o
inner join ADS.stg.ADW_SF_User u on u.Id = o.OwnerId
inner join ADS.stg.ADW_SF_UserRole ur on ur.id=UserRoleId
inner join ADS.stg.ADW_SF_RecordType r on o.RecordTypeId=r.Id
inner join ADS.stg.ADW_SF_Account a on o.AccountID=a.ID
inner join sf.Opportunity opp on opp.SF_OpportunityID = o.SF_OpportunityID
inner join dim.Period p on p.[Date] = o.Forecasted_Commencement_Renewal_Date__c
left JOIN [ADW].[Dim].[CustomerServiceNumber] csn ON o.Customer_Number2__c = csn.CustomerServiceNumber
--left JOIN dim.Customer cus ON csn.CustomerID = cus.CustomerID
left join sf.SFModalites_Map sfm on sfm.SF_Modality=o.Modality__c
left join dim.Modality m on m.ModalityCode = sfm.ModalityCode
left JOIN dim.Company co on csn.CompanyID = co.CompanyID
left join ADS.stg.ADW_NASLookbackExceptions nasexcep on nasexcep.SF_Opportunity_ID=opp.SF_OpportunityID
left join MktRevAvg mra on csn.CustomerServiceNumberID = mra.CustomerID AND opp.OpportunityID = mra.OpportuniytID
where (1=1)
and r.Name like 'Imaging%'
and o.Exclude__c = 0
and o.Modality__c not in ('Professional Services','Radiation Therapy','SRS (Stereotactic Radiosurgery)')
and (year(o.Forecasted_Commencement_Renewal_Date__c) >= YEAR(GETDATE())-1)
group by
o.OpportunityID
,csn.CustomerServiceNumberID
,co.CompanyID
,p.DateID
,m.ModalityId
,csn.OrgUnitID
,o.HNI__c
,CASE WHEN nasexcep.NASLookback is null then mra.AvgMktRevenue else nasexcep.NASLookback END
)
select * From sales
July 22, 2014 at 3:42 pm
Quick thought, look for the value "Actual" in any column of the tables and track it from there.
😎
July 22, 2014 at 3:49 pm
Lots and lots of tables. It's hard for us because we don't know your schema and we can't test anything.
Try looking for columns that are defined as strings which are being compared to integers.
July 22, 2014 at 6:03 pm
Eirikur Eiriksson (7/22/2014)
Quick thought, look for the value "Actual" in any column of the tables and track it from there.😎
Thanks, that's where I'm focusing my search. So far no luck.
July 22, 2014 at 10:48 pm
This is normally the kind of thing I do in such situation, that is using a snip to create the search code, paste the results into a new query and selectively run each statement.
😎
DECLARE @TARGET_VALUE VARCHAR(50) = 'ABB';
SELECT
N'SELECT ' + NCHAR(39) + C.TABLE_SCHEMA + NCHAR(46) + C.TABLE_NAME + NCHAR(46) + C.COLUMN_NAME
+ NCHAR(39) + N' AS SEARCH_LOCATION '
+ N',(SELECT COUNT(*) FROM ' + C.TABLE_SCHEMA + NCHAR(46) + C.TABLE_NAME + N' WHERE ' + C.COLUMN_NAME
+ N' = ' + NCHAR(39) + @TARGET_VALUE + NCHAR(39) + N' ) AS INST_COUNT'
,C.TABLE_SCHEMA
,C.TABLE_NAME
,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.DATA_TYPE = N'VARCHAR'
AND C.TABLE_NAME IN ('TABLE_1','TABLE_2','TABLE_3')
July 22, 2014 at 11:06 pm
You also might want to try casting every column in the query as VARCHAR. Then insert into a temp table/physical table to see if any value in a column of int is not an int.
These conversion errors with a lot of tables/views can be hard to debug and pinpoint exactly what column the error is coming from.
July 23, 2014 at 12:55 am
Thanks all for the suggestions. I figured it out. One of the joined tables had been changed and field that was previously an integer was now a varchar. It was in the where clauses.
I knew it was in the 1st part of the CTE and I simply broke it down eliminating grouping and where clauses till I got it running then adding things back in one at a time.
July 23, 2014 at 5:57 am
-- Some alternative date arithmetic
SELECT
o.Forecasted_Commencement_Renewal_Date__c,
CAST(RTRIM(CAST(MONTH(DATEADD(mm, 1, o.Forecasted_Commencement_Renewal_Date__c)) AS CHAR(2))) + '/01/' + CAST(YEAR(o.Forecasted_Commencement_Renewal_Date__c) AS CHAR(4)) AS DATETIME),
DATEADD(MONTH,DATEDIFF(MONTH,0,Forecasted_Commencement_Renewal_Date__c)+1,0),
CAST((CAST(YEAR(GETDATE()) AS CHAR(4)) + CASE WHEN LEN(CAST(MONTH(GETDATE()) AS CHAR(2))) < 2 THEN '-0' + CAST(MONTH(GETDATE()) AS CHAR(1)) ELSE '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) END + '-01') AS SMALLDATETIME) AS SnapShotPeriod,
CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS SMALLDATETIME)
FROM (SELECT Forecasted_Commencement_Renewal_Date__c = GETDATE()) o
-- here's how it works
SELECT DATEDIFF(MONTH,0,GETDATE()) -- 1374
SELECT DATEADD(MONTH,1374+1,0) -- 2014-08-01 00:00:00.000
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0) -- 2014-08-01 00:00:00.000
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) -- 2014-07-01 00:00:00.000
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply