How to get rid of { fn } in my View code

[Cpreston]

HI

I have a query that runs ok when I run it in query window and gives the results required. If I add the query into a view I am getting the attached error message when I run it. I believe it is because when pasting it into the view it is changing this row from

DATEADD(year, 0, CAST(CONCAT(CalendarMonth, '/1/', CalendarYear) as date))as Date1 TO DATEADD(year, 0, CAST({ fn CONCAT(dbo.ProductAnalysis.CalendarMonth, '/1/', dbo.ProductAnalysis.CalendarYear) } AS date)) AS Date1

And

CONCAT(CHOOSE(CalendarMonth,'Jan', 'Feb', 'Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'), ' ', CalendarYear) As FullDate TO { fn CONCAT(CHOOSE(dbo.ProductAnalysis.CalendarMonth, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), ' ', dbo.ProductAnalysis.CalendarYear) } AS FullDate

It is putting in {fn at the beginning with } at the end which is a function, I don't want the function there just the normal code, any ideas please how to get round this

 

[Andrzejek]

Could you provide the WHOLE Select statement, not just bits and pieces?
Just copy-n-paste what you have.
Formatting your statement would be nice to have. It is a lot easier to read....

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Here we go this is the code before I paste it into the View and it runs ok, when I run the view it adds in the FN in the lines in my previous post

SELECT DISTINCT 
                         dbo.Product.ProductCode, dbo.Product.Description, dbo.Stock.StockActual, dbo.Stock.StockAvailable, dbo.Stock.StockOnOrder, dbo.Stock.StockDue, dbo.Stock.StockAllocated, dbo.Stock.StockReserved, 
                         dbo.Product.Thickness, dbo.Product.Width, dbo.Product.AverageCostPriceWithAdditional, dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel1, 
                         dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel2, dbo.Per.Name, dbo.ProductAnalysis.CalendarYear, dbo.ProductAnalysis.CalendarMonth, SUM(dbo.ProductAnalysis.QuantitySold) 
                         AS QTYSold,
						 DATEADD(year, 0, CAST(CONCAT(CalendarMonth, '/1/', CalendarYear) as date))as Date1,
						 ---CONVERT(varchar(3),CalendarMonth,0) AS OrderMonth,
						 ---DATENAME(MONTH, CalendarMonth) As LongMonth,
						 ---CONVERT(varchar(3), DATENAME(MONTH, CalendarMonth)) AS OrderMonth,
						CONCAT(CHOOSE(CalendarMonth,'Jan', 'Feb', 'Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'), ' ', CalendarYear) As FullDate,
						 CHOOSE(CalendarMonth,'Jan', 'Feb', 'Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as NameMonth
FROM            dbo.ProductAnalysis INNER JOIN
                         dbo.Product ON dbo.ProductAnalysis.ProductID = dbo.Product.ProductID INNER JOIN
                         dbo.Stock ON dbo.Product.ProductID = dbo.Stock.ProductID AND dbo.ProductAnalysis.BranchID = dbo.Stock.BranchID INNER JOIN
                         dbo.[148-vwProductGroupandSubGroupLevelsCP] ON dbo.Product.ProductGroupID = dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupID INNER JOIN
                         dbo.Per ON dbo.Product.BuyPerID = dbo.Per.PerID AND dbo.Product.SellPerID = dbo.Per.PerID
WHERE        (dbo.ProductAnalysis.ProductID = 27168) AND (dbo.ProductAnalysis.BranchID = 1) AND (dbo.ProductAnalysis.CalendarYear * 100 + dbo.ProductAnalysis.CalendarMonth >= (YEAR(GETDATE()) - 1) 
                         * 100 + MONTH(GETDATE()))
GROUP BY dbo.ProductAnalysis.CalendarMonth, dbo.ProductAnalysis.CalendarYear, dbo.Product.ProductCode, dbo.Product.Description, dbo.Product.Thickness, dbo.Product.Width, dbo.Stock.StockActual, 
                         dbo.Stock.StockAvailable, dbo.Stock.StockOnOrder, dbo.Stock.StockDue, dbo.Stock.StockAllocated, dbo.Stock.StockReserved, dbo.Product.AverageCostPriceWithAdditional, 
                         dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel1, dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel2, dbo.Per.Name

 

[Andrzejek]

I don't know if that's your issue, but in your Select part you have 19 fields + SUM(), (not counting 3 commented out fields) but your ORDER BY has only 16 fields.
Last 3 fields (Date1, FullDate, and NameMonth) are NOT in your Order By part...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

These are Alias names Date1, FullDate, NameMonth and when added to the Group By its says they are invalid columns. I then added in the full rows of the code without the As part. it ran ok in the query window. Pasted it into a new view and I get the same error message.

How the code is that works, in the View it adds the [FN} again

SELECT DISTINCT 
                         dbo.Product.ProductCode, dbo.Product.Description, dbo.Stock.StockActual, dbo.Stock.StockAvailable, dbo.Stock.StockOnOrder, dbo.Stock.StockDue, dbo.Stock.StockAllocated, dbo.Stock.StockReserved, 
                         dbo.Product.Thickness, dbo.Product.Width, dbo.Product.AverageCostPriceWithAdditional, dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel1, 
                         dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel2, dbo.Per.Name, dbo.ProductAnalysis.CalendarYear, dbo.ProductAnalysis.CalendarMonth, SUM(dbo.ProductAnalysis.QuantitySold) 
                         AS QTYSold, DATEADD(year, 0, CAST({ fn CONCAT(dbo.ProductAnalysis.CalendarMonth, '/1/', dbo.ProductAnalysis.CalendarYear) } AS date)) AS Date1, { fn CONCAT(CHOOSE(dbo.ProductAnalysis.CalendarMonth, 
                         'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), ' ', dbo.ProductAnalysis.CalendarYear) } AS FullDate, CHOOSE(dbo.ProductAnalysis.CalendarMonth, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 
                         'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS NameMonth
FROM            dbo.ProductAnalysis INNER JOIN
                         dbo.Product ON dbo.ProductAnalysis.ProductID = dbo.Product.ProductID INNER JOIN
                         dbo.Stock ON dbo.Product.ProductID = dbo.Stock.ProductID AND dbo.ProductAnalysis.BranchID = dbo.Stock.BranchID INNER JOIN
                         dbo.[148-vwProductGroupandSubGroupLevelsCP] ON dbo.Product.ProductGroupID = dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupID INNER JOIN
                         dbo.Per ON dbo.Product.BuyPerID = dbo.Per.PerID AND dbo.Product.SellPerID = dbo.Per.PerID
WHERE        (dbo.ProductAnalysis.ProductID = 27168) AND (dbo.ProductAnalysis.BranchID = 1) AND (dbo.ProductAnalysis.CalendarYear * 100 + dbo.ProductAnalysis.CalendarMonth >= (YEAR(GETDATE()) - 1) 
                         * 100 + MONTH(GETDATE()))
GROUP BY dbo.ProductAnalysis.CalendarMonth, dbo.ProductAnalysis.CalendarYear, dbo.Product.ProductCode, dbo.Product.Description, dbo.Product.Thickness, dbo.Product.Width, dbo.Stock.StockActual, 
                         dbo.Stock.StockAvailable, dbo.Stock.StockOnOrder, dbo.Stock.StockDue, dbo.Stock.StockAllocated, dbo.Stock.StockReserved, dbo.Product.AverageCostPriceWithAdditional, 
                         dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel1, dbo.[148-vwProductGroupandSubGroupLevelsCP].ProductGroupLevel2, dbo.Per.Name, DATEADD(year, 0, 
                         CAST({ fn CONCAT(dbo.ProductAnalysis.CalendarMonth, '/1/', dbo.ProductAnalysis.CalendarYear) } AS date)), { fn CONCAT(CHOOSE(dbo.ProductAnalysis.CalendarMonth, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 
                         'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), ' ', dbo.ProductAnalysis.CalendarYear) }, CHOOSE(dbo.ProductAnalysis.CalendarMonth, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')

 

[Andrzejek]

Looks like - for some reason - your environment does not like [tt]CONCAT()[/tt] function and is trying to replace it with some other, internal function. You can read more about it here

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson