Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Oracle view creator guru, plz help...

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
heskyttberg
Asked:
heskyttberg
  • 2
1 Solution
 
DanielztCommented:
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
 
Computer101Commented:
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
 
heskyttbergAuthor Commented:
Hi!

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

Regards
/Hans - Erik Skyttberg
0
 
heskyttbergAuthor Commented:
Thanks for enlightening me, )

/Hans - Erik Skyttberg
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now