Home » SQL & PL/SQL » SQL & PL/SQL » queston on view (merged)
queston on view (merged) [message #392929] Thu, 19 March 2009 12:21 Go to next message
rajesh_tns
Messages: 6
Registered: February 2009
Junior Member
i want to construct a view .condition: I should not modify the view each time a new column is added to table.

eg: table has

empno ename address grade
1 aaa US A
2 bbb US c
1 aaa US B
2 ddd AUS B

I want to create a view on top of the table selecting all columns and grade = A,B

hence i get 3 rows...... 1, 1, 2

here lets concentrate on only two records

1 aaa US A
1 aaa US B

I donot want to select both rows...coz the empno,ename,address are equal hence I will select only A i.e A has always more preference than B.


my result should be containing only two rows

1 aaa US A
2 ddd AUS B

how to write a query for this ? please let me know

Re: question on a view [message #392931 is a reply to message #392929] Thu, 19 March 2009 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

do NOT cross/multi-post
Re: oracle views scenario [message #392932 is a reply to message #392929] Thu, 19 March 2009 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

do NOT cross/multi-post
Re: queston on view (merged) [message #392933 is a reply to message #392929] Thu, 19 March 2009 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic less than one hour ago:
Michel Cadot wrote on Thu, 19 March 2009 17:30
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel


[Updated on: Thu, 19 March 2009 13:00]

Report message to a moderator

Re: queston on view (merged) [message #393146 is a reply to message #392929] Fri, 20 March 2009 09:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want to use an analytic like ROW_NUMBER to give you an order to the rows within each Empno,Ename,Address combination which have the correct grade, and then pick the first of each of these sets of rows.

Have a try yourself, and if you get stuck, we can point you in the right direction.

To keep everyone else quiet, I've knocked up a set of test data for you. Next time, please try to do this yourself.:
create table test_152 (empno number,ename varchar2(10), address varchar2(10), grade varchar2(1));

insert into test_152 values (1, 'aaa', 'US' ,'A');
insert into test_152 values (2, 'bbb', 'US' ,'c');
insert into test_152 values (1, 'aaa', 'US' ,'B');
insert into test_152 values (2, 'ddd', 'AUS','B');
 
commit;
Previous Topic: EXECUTE IMMEDIATE trouble in stored procedures
Next Topic: Declaring Variable in Basic PL/SQL...
Goto Forum:
  


Current Time: Sat Dec 03 18:25:56 CST 2016

Total time taken to generate the page: 0.05308 seconds