469,610 Members | 2,110 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

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.com
Jul 23 '05 #1
3 2164
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

"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
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.

Similar topics

28 posts views Thread by stu_gots | last post: by
reply views Thread by T Tran via SQLMonster.com | last post: by
23 posts views Thread by Jason | last post: by
16 posts views Thread by gnawz | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.