By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Please help: Create View

P: n/a
Hi Gurus,
I'm a beginner. Would you please tell me if it's possible to create a view
having a calcuated column based on the condition of the column on the sql
table.

create view vwImaging AS
select
EmpID, LastName, FirstName, EmpTag = 'Act' if

FROM tblPerPay
I have a table EMP:
SSN (char 9)

A view giving a formatted SSN (XXX-XX-XXXX,) a

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Sorry, I press the "Post Message" by accident while editing the message.

Here is question again: Is it possible to create a view having a column
based on the value of a given column on the sql table.

create view LookUp AS
select
EmpID,
LastName,
FirstName,
EmpTag = 'Act' (if tblEmp.TermDate is Null)
EmpTag = 'Inact' (if tblEmp.TermDate not Null)
from tblEmp

I don't know the syntax of the last 2 columns. TermDate is the termination
date in tblEmp.

Any help will be greatly appreciated.
Thanks in advance
TTran

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #2

P: n/a

"T Tran via SQLMonster.com" <fo***@SQLMonster.com> wrote in message
news:c0******************************@SQLMonster.c om...
Sorry, I press the "Post Message" by accident while editing the message.

Here is question again: Is it possible to create a view having a column
based on the value of a given column on the sql table.

create view LookUp AS
select
EmpID,
LastName,
FirstName,
EmpTag = 'Act' (if tblEmp.TermDate is Null)
EmpTag = 'Inact' (if tblEmp.TermDate not Null)
from tblEmp

I don't know the syntax of the last 2 columns. TermDate is the termination
date in tblEmp.

Any help will be greatly appreciated.
Thanks in advance
TTran

--
Message posted via http://www.sqlmonster.com


Check out CASE in Books Online. Do you need two EmpTag columns? If Act/Inact
is a flag, it may make more sense to use only one column:

create view LookUp AS
select
EmpID,
LastName,
FirstName,
EmpTag = case when TermDate is Null then 'Act' else 'Inact' end
from
tblEmp

But if you do need separate columns, then try this (it's usually not a good
idea to return multiple columns with the same name):

create view LookUp AS
select
EmpID,
LastName,
FirstName,
EmpTagAct = case when TermDate is null then 'Act' else '-' end,
EmpTagInact = case when TermDate is not null then 'Inact' else '-' end
from
tblEmp

Simon
Jul 23 '05 #3

P: n/a
Since you are learning SQL, you might want to start by learning
ISO-11179 rules for names and the Standard SQL syntax for aliases. A
CASE expression will handle this:

CREATE VIEW PersonnelStatus (ssn, last_name, first_name,
employment_status)
AS
SELECT ssn, last_name, first_name,
CASE (WHEN term_date IS NULL
THEN 'inactive'
ELSE 'active ' END
FROM Personnel;

The equal sign is local dialect; the AS operator is Standard. Use
collective or plural nouns for table names; never put a prefix on a
data element to tell us how it is *physically* stored. This is really
silly in SQL, since there is only one data structure.

I have a book on SQL PROGRAMMING STYLE due out the middle of this year
that might be of some help to you.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.