error message when trying to save code as a view, but runs fine in SSMS

Question

error message when trying to save code as a view, but runs fine in SSMS

randyetheridge

SSCommitted

Points: 1703
More actions
May 17, 2016 at 6:46 am

#318891

I have the following code and when I ran it I got this message
Msg 468, Level 16, State 9, Line 31
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
so on the last line I added this for accountnum and dimension COLLATE Latin1_General_CI_AS
I then ran the code and it worked fine, gave me the correct answer set.
I then right clicked on view "New View" and copied this code into the view statement. when I tried to execute the view it dropped the added Latin1_General_CI_AS information and gave the error noted above.
I am just wondering if view's require a different handling of this error?
SELECT t001.ScenarioID, t001.accountnum, t001.ACCOUNTNAME, t001.dimension, t001.GroupID, ROUND(CAST(t001.SumOfActual_amt AS NUMERIC(15, 4)), 2)
AS SumOfActual_amt, ROUND(CAST(t001.SumOfActual_amt / t04.SalesActualYTD * 100 AS NUMERIC(15, 4)), 1) AS PercentageYTDToSales,
ROUND(CAST(t04.SalesActualYTD AS NUMERIC(15, 4)), 0) AS SalesActualYTD, ROUND(CAST(t03.Forecast - t04.SalesActualYTD AS NUMERIC(15, 4)), 0)
AS PartOfForecast, ROUND(CAST(t03.Forecast AS NUMERIC(15, 4)), 0) AS Forecast, ROUND(CAST(t03.Budget AS NUMERIC(15, 4)), 0) AS Budget,
ROUND(CAST(CASE WHEN t05.Forecast IS NULL THEN t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)
ELSE t05.Forecast END AS NUMERIC(15, 4)), 2) AS ForecastOnAccount, ROUND(CAST((CASE WHEN t05.Forecast IS NULL
THEN t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD) ELSE t05.Forecast END) / t03.Forecast * 100 AS NUMERIC(15, 4)), 1)
AS PercentageForecastToSales, ROUND(CAST(CASE WHEN t05.Budget IS NULL THEN ((t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD))
/ t03.Forecast) * t03.Budget ELSE t05.Budget END AS NUMERIC(15, 4)), 2) AS BudgetOnAccount, ROUND(CAST((CASE WHEN t05.Budget IS NULL
THEN ((t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)) / t03.Forecast) * t03.Budget ELSE t05.Budget END)
/ t03.Budget * 100 AS NUMERIC(15, 4)), 1) AS PercentageBudgetOfSales
FROM (SELECT t01.ScenarioID, t00.GroupID, t00.dimension, t00.accountnum, t00.ACCOUNTNAME, SUM(t00.ActualAmtUSD) AS SumOfActual_amt
FROM (SELECT *
FROM finance.MonthEndReviewUS81
WHERE (accounttype = 'Profit_Loss') AND (GroupID IN ('G0071', 'G0073', 'G0074')) AND (accountnum NOT IN ('60000000', '60800000', '60000300')))
AS t00 INNER JOIN
dbo.BudgetMasterScenario AS t01 ON t00.YearID + 1 = RIGHT(t01.ScenarioID, 4) LEFT OUTER JOIN
US_Planning_HR.dbo.BudgetHR_InputMasterData AS t02 ON t01.ScenarioID = t02.ScenarioID
WHERE (t00.monthid <= t02.MonthCalculating)
GROUP BY t01.ScenarioID, t00.GroupID, t00.dimension, t00.accountnum, t00.ACCOUNTNAME) AS t001 LEFT OUTER JOIN
(SELECT ScenarioID, SUM(SalesEstimated) AS Forecast, SUM(SalesBudget) AS Budget
FROM dbo.Budget_SalesSummary
GROUP BY ScenarioID) AS t03 ON t001.ScenarioID = t03.ScenarioID LEFT OUTER JOIN
(SELECT t11.ScenarioID, SUM(t10.ActualAmtUSD) * - 1 AS SalesActualYTD
FROM finance.MonthEndReviewUS81 AS t10 LEFT OUTER JOIN
US_Planning_HR.dbo.BudgetHR_InputMasterData AS t11 ON t10.yearid + 1 = CAST(RIGHT(t11.ScenarioID, 4) AS NUMERIC(4, 0))
WHERE (t10.MonthID <= t11.MonthCalculating) AND (t10.GroupID IN ('G0011', 'G0012'))
GROUP BY t11.ScenarioID) AS t04 ON t001.ScenarioID = t04.ScenarioID LEFT OUTER JOIN
dbo.BudgetCogs_InputOtherCogs AS t05 ON t001.ScenarioID = t05.ScenarioID AND t001.accountnum = t05.accountnum COLLATE Latin1_General_CI_AS AND t001.dimension = t05.dimension COLLATE Latin1_General_CI_AS

Viewing 6 posts - 1 through 5 (of 5 total)

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

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

Have you tried creating your view outside of the designer? if you have created a view using the designer then you can use the alter statement along with your view name. If you haven't created it yet then change alter to create.

ALTER VIEW dbo.ViewName

AS

SELECT t001.ScenarioID, t001.accountnum, t001.ACCOUNTNAME, t001.dimension, t001.GroupID, ROUND(CAST(t001.SumOfActual_amt AS NUMERIC(15, 4)), 2)

AS SumOfActual_amt, ROUND(CAST(t001.SumOfActual_amt / t04.SalesActualYTD * 100 AS NUMERIC(15, 4)), 1) AS PercentageYTDToSales,

ROUND(CAST(t04.SalesActualYTD AS NUMERIC(15, 4)), 0) AS SalesActualYTD, ROUND(CAST(t03.Forecast - t04.SalesActualYTD AS NUMERIC(15, 4)), 0)

AS PartOfForecast, ROUND(CAST(t03.Forecast AS NUMERIC(15, 4)), 0) AS Forecast, ROUND(CAST(t03.Budget AS NUMERIC(15, 4)), 0) AS Budget,

ROUND(CAST(CASE WHEN t05.Forecast IS NULL THEN t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)

ELSE t05.Forecast END AS NUMERIC(15, 4)), 2) AS ForecastOnAccount, ROUND(CAST((CASE WHEN t05.Forecast IS NULL

THEN t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD) ELSE t05.Forecast END) / t03.Forecast * 100 AS NUMERIC(15, 4)), 1)

AS PercentageForecastToSales, ROUND(CAST(CASE WHEN t05.Budget IS NULL THEN ((t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD))

/ t03.Forecast) * t03.Budget ELSE t05.Budget END AS NUMERIC(15, 4)), 2) AS BudgetOnAccount, ROUND(CAST((CASE WHEN t05.Budget IS NULL

THEN ((t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)) / t03.Forecast) * t03.Budget ELSE t05.Budget END)

/ t03.Budget * 100 AS NUMERIC(15, 4)), 1) AS PercentageBudgetOfSales

FROM (SELECT t01.ScenarioID, t00.GroupID, t00.dimension, t00.accountnum, t00.ACCOUNTNAME, SUM(t00.ActualAmtUSD) AS SumOfActual_amt

FROM (SELECT *

FROM finance.MonthEndReviewUS81

WHERE (accounttype = 'Profit_Loss') AND (GroupID IN ('G0071', 'G0073', 'G0074')) AND (accountnum NOT IN ('60000000', '60800000', '60000300')))

AS t00 INNER JOIN

dbo.BudgetMasterScenario AS t01 ON t00.YearID + 1 = RIGHT(t01.ScenarioID, 4) LEFT OUTER JOIN

US_Planning_HR.dbo.BudgetHR_InputMasterData AS t02 ON t01.ScenarioID = t02.ScenarioID

WHERE (t00.monthid <= t02.MonthCalculating)

GROUP BY t01.ScenarioID, t00.GroupID, t00.dimension, t00.accountnum, t00.ACCOUNTNAME) AS t001 LEFT OUTER JOIN

(SELECT ScenarioID, SUM(SalesEstimated) AS Forecast, SUM(SalesBudget) AS Budget

FROM dbo.Budget_SalesSummary

GROUP BY ScenarioID) AS t03 ON t001.ScenarioID = t03.ScenarioID LEFT OUTER JOIN

(SELECT t11.ScenarioID, SUM(t10.ActualAmtUSD) * - 1 AS SalesActualYTD

FROM finance.MonthEndReviewUS81 AS t10 LEFT OUTER JOIN

US_Planning_HR.dbo.BudgetHR_InputMasterData AS t11 ON t10.yearid + 1 = CAST(RIGHT(t11.ScenarioID, 4) AS NUMERIC(4, 0))

WHERE (t10.MonthID <= t11.MonthCalculating) AND (t10.GroupID IN ('G0011', 'G0012'))

GROUP BY t11.ScenarioID) AS t04 ON t001.ScenarioID = t04.ScenarioID LEFT OUTER JOIN

dbo.BudgetCogs_InputOtherCogs AS t05 ON t001.ScenarioID = t05.ScenarioID AND t001.accountnum = t05.accountnum COLLATE Latin1_General_CI_AS AND t001.dimension = t05.dimension COLLATE Latin1_General_CI_AS

GO

randyetheridge SSCommitted Points: 1703 More actions · Answer 2

Thank you so much. I am very new to this level of SQL usage. prior experience was all simple stuff. Never knew the Alter command. I used it, it worked perfectly and the view now runs. Thank you so much.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Try to format your code. You'll thank yourself later.

ALTER VIEW dbo.ViewName

AS

SELECT t001.ScenarioID

,t001.accountnum

,t001.ACCOUNTNAME

,t001.dimension

,t001.GroupID

,ROUND(CAST(t001.SumOfActual_amt AS NUMERIC(15, 4)), 2) AS SumOfActual_amt

,ROUND(CAST(t001.SumOfActual_amt / t04.SalesActualYTD * 100 AS NUMERIC(15, 4)), 1) AS PercentageYTDToSales

,ROUND(CAST(t04.SalesActualYTD AS NUMERIC(15, 4)), 0) AS SalesActualYTD

,ROUND(CAST(t03.Forecast - t04.SalesActualYTD AS NUMERIC(15, 4)), 0) AS PartOfForecast

,ROUND(CAST(t03.Forecast AS NUMERIC(15, 4)), 0) AS Forecast

,ROUND(CAST(t03.Budget AS NUMERIC(15, 4)), 0) AS Budget

,ROUND(CAST(CASE

WHEN t05.Forecast IS NULL

THEN t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)

ELSE t05.Forecast

END AS NUMERIC(15, 4)), 2) AS ForecastOnAccount

,ROUND(CAST((

CASE

WHEN t05.Forecast IS NULL

THEN t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)

ELSE t05.Forecast

END

) / t03.Forecast * 100 AS NUMERIC(15, 4)), 1) AS PercentageForecastToSales

,ROUND(CAST(CASE

WHEN t05.Budget IS NULL

THEN ((t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)) / t03.Forecast) * t03.Budget

ELSE t05.Budget

END AS NUMERIC(15, 4)), 2) AS BudgetOnAccount

,ROUND(CAST((

CASE

WHEN t05.Budget IS NULL

THEN ((t001.SumOfActual_amt / t03.Forecast * (t03.Forecast - t04.SalesActualYTD)) / t03.Forecast) * t03.Budget

ELSE t05.Budget

END

) / t03.Budget * 100 AS NUMERIC(15, 4)), 1) AS PercentageBudgetOfSales

FROM (

SELECT t01.ScenarioID

,t00.GroupID

,t00.dimension

,t00.accountnum

,t00.ACCOUNTNAME

,SUM(t00.ActualAmtUSD) AS SumOfActual_amt

FROM (

SELECT *

FROM finance.MonthEndReviewUS81

WHERE (accounttype = 'Profit_Loss')

AND (

GroupID IN (

'G0071'

,'G0073'

,'G0074'

)

AND (

accountnum NOT IN (

'60000000'

,'60800000'

,'60000300'

)

) AS t00

INNER JOIN dbo.BudgetMasterScenario AS t01 ON t00.YearID + 1 = RIGHT(t01.ScenarioID, 4)

LEFT JOIN US_Planning_HR.dbo.BudgetHR_InputMasterData AS t02 ON t01.ScenarioID = t02.ScenarioID

WHERE (t00.monthid <= t02.MonthCalculating)

GROUP BY t01.ScenarioID

,t00.GroupID

,t00.dimension

,t00.accountnum

,t00.ACCOUNTNAME

) AS t001

LEFT JOIN (

SELECT ScenarioID

,SUM(SalesEstimated) AS Forecast

,SUM(SalesBudget) AS Budget

FROM dbo.Budget_SalesSummary

GROUP BY ScenarioID

) AS t03 ON t001.ScenarioID = t03.ScenarioID

LEFT JOIN (

SELECT t11.ScenarioID

,SUM(t10.ActualAmtUSD) * - 1 AS SalesActualYTD

FROM finance.MonthEndReviewUS81 AS t10

LEFT JOIN US_Planning_HR.dbo.BudgetHR_InputMasterData AS t11 ON t10.yearid + 1 = CAST(RIGHT(t11.ScenarioID, 4) AS NUMERIC(4, 0))

WHERE (t10.MonthID <= t11.MonthCalculating)

AND (

t10.GroupID IN (

'G0011'

,'G0012'

)

GROUP BY t11.ScenarioID

) AS t04 ON t001.ScenarioID = t04.ScenarioID

LEFT JOIN dbo.BudgetCogs_InputOtherCogs AS t05 ON t001.ScenarioID = t05.ScenarioID

AND t001.accountnum = t05.accountnum COLLATE Latin1_General_CI_AS

AND t001.dimension = t05.dimension COLLATE Latin1_General_CI_AS

GO

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

randyetheridge SSCommitted Points: 1703 More actions · Answer 4

Yes I have been told that a couple times now. most of what I am currently doing is updating prior code. So most of my questions revolve around updating prior code. I am still learning how to format code. I think there are tools to help with this but I just have to learn to use them.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Some tools available are Redgate's SQL Prompt, ApexSQL Refactor and Poor Man's T-SQL Formatter.

I use the 2 latest because they're free, but I know that SQL Prompt has more capabilities. In my opinion, ApexSQL Refactor is a better formatter and has more functionality that Poor Man's formatter, but it depends on the objects to exist.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2