?
Solved

Oracle view creator guru, plz help...

Posted on 2003-02-27
4
Medium Priority
?
354 Views
Last Modified: 2010-08-05
Hi!

I have been presented with a really strange task on a 30 questions questionare for a course I'm attending after work.

I din't think you could do like this, or I might just be wrong here!

This is the question:
Createa view kalled PS from table persons.
They view should contain two columns:
PA (PERSON_ATTR) and SUM
The values in first column should be
Food, Address and E-Mail
In second column the number of persons that has somthing filled in into thoose columns should be listed.

persons table:
p_id, address, food, email

Now I could get the values they want like this:

select count(1) from PS where food IS NOT NULL;
RETURNS 3
select count(1) from PS where address IS NOT NULL;
RETURNS 2
select count(1) from PS where email IS NOT NULL;
RETURNS 4
I could even put all of theese three into one row.

But how do I get theese values as follows in view:

PA      SUM
Food    3
Address 2
E-Mail  4

I have done quite som development, but never tinkered with such complex vies as this seems to be.

Any pointer or example on how to do this would be helpful.

I have 30 questions, I only need to pass 24, so this question isn't that important, I just want to know if it at all is possible to do like this.

There wasn't any pre-requisites for this course so I'm abit surprised such a question has come after a week.

Regards
/Hans - Erik Skyttberg
0
Comment
Question by:heskyttberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
Danielzt earned 200 total points
ID: 8038107
it's easy, just try this:

select 'food' as PA, count(*) as SUM from PS where food IS NOT NULL
union
select 'address' AS PA,count(*) as SUM from PS where address IS NOT NULL
union
select 'email' as PA,count(*) as SUM from PS where email IS NOT NULL


0
 
LVL 1

Expert Comment

by:Computer101
ID: 8039154
A request for deletion has been made.  If no response or you feel this is in error, comment.  If no objection, I will delete in three days.

Computer101
E-E Admin
0
 
LVL 8

Author Comment

by:heskyttberg
ID: 8043233
Hi!

Many thanks it was correct, I haven't learned to use union yet, but I see the possibilitied.

Regards
/Hans - Erik Skyttberg
0
 
LVL 8

Author Comment

by:heskyttberg
ID: 8043240
Thanks for enlightening me, )

/Hans - Erik Skyttberg
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question