is it possible to add columns with data hard coded in each column to a view?

katbrennan
katbrennan used Ask the Experts™
on
Hi,

I have created a view to run off of several joined tables. I would like my view to return 3 additional columns (with hard-coded data). These three columns are not in any of the tables, but it's additional data that I need in the view. Is this possible?

Thanks,
Kate
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
you can do it

create view vw_test as
select Col1, col2, '1' as newcolumn, '23' as anotherColumn
FROM urTable
You can have aliases in a different way and data of any type hardcoded i.e.
create view vw_test(C1, C2, newcol1, newcol2,newcol3,newcol4) as
select Col1, col2, '1', 23,10.5,getDate()
FROM urTable;

Author

Commented:
When I do the above, it names the column 'No Column Name'. How do I name the column?
For example I would like to add a column called 'Description' and for all of the records in the view the value of that field would be 'Account Description Needed'. How do I accomplish that?
Commented:
All you have to do is give the column an alias name.

CREATE VIEW dbo.MyDBView AS
 
SELECT {your colums}
       ,'Account Description Needed' As Description
  FROM dbo.YourTable
 WHERE {where clause}
GO

Open in new window

CREATE VIEW dbo.MyDBView({your colums},'Account Description Needed')  AS
SELECT {your colums}
       ,Description
  FROM dbo.YourTable
 WHERE {where clause}
GO

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial