Aliasing a column name

Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:

SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear

But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.

Any help will be appreciated.

Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .

A couple of things to note:

& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_

"value" does not mean a literal, it means a column name. Use single quotes 'value'

Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.

|||

AFAIK, you can′t compose the Aliases on the fly.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your help. Its not the answer I was hoping for but what you are saying does make sense. And thanks for the t-sql tips too. I am really new at this so any help is appreciated.