Please help: Create View

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.comSorry, 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|||"T Tran via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c015ee60567f4767934b62908ec07144@.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|||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.