Topic: SQL*Plus and PL/SQL >> Meta Data
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| shake |
Posted: Jun 17, 2008 03:58:53 AM |
Total Post: 84
Joined: Jun, 2008
|
I have the following table with 5 subjects. I tried to findout the max and min among 5 subjects of each student. I used the GREATEST and LEAST to do it. But I want to print the subject name next to the mark for each student (to understand the student's status at which subject he scored well and at which he got struggled.). I dont know the usage of DBMS_METADATA.
CREATE TABLE STUDENTSMARK
(
sname VARCHAR2(25),
sno NUMBER(3),
sub1 NUMBER(3),
sub2 NUMBER(3),
sub3 NUMBER(3),
sub4 NUMBER(3),
sub5 NUMBER(3)
);
INSERT INTO STUDENTSMARK VALUES('A',101,23,43,45,34,76);
INSERT INTO STUDENTSMARK VALUES('B',102,73,34,54,24,72);
INSERT INTO STUDENTSMARK VALUES('C',103,23,43,54,34,54);
INSERT INTO STUDENTSMARK VALUES('D',104,34,64,34,70,98);
INSERT INTO STUDENTSMARK VALUES('E',105,63,85,66,23,55);
INSERT INTO STUDENTSMARK VALUES('F',106,45,30,56,86,54);
INSERT INTO STUDENTSMARK VALUES('G',107,56,88,65,90,65);
INSERT INTO STUDENTSMARK VALUES('H',108,71,34,54,12,53);
SELECT sname, sno, GREATEST(sub1,sub2,sub3,sub4,sub5) High, LEAST(sub1,sub2,sub3,sub4,sub5) Low FROM STUDENTSMARK;
Result
sname sno High Low
A 101 76 23
B 102 73 24
.......................
.......................
I want it in this way
sname sno High Low
A 101 76 ->sub5 23 -> sub1
B 102 73 ->sub1 24 -> sub4
.......................
.......................
Plz give your valuable suggestions.
Thanx in advance. |
|
|
 shake |
| Posted: Jun 17, 2008 07:06:57 AM |  |
|
Total Post: 84
Joined: Jun, 2008
|
Hard-coded Solution Found.
SELECT TT.SNAME, TT.SNO,
GREATEST(tt.sub1, tt.sub2, tt.sub3, tt.sub4, tt.sub5) || ' -> ' ||
CASE GREATEST(tt.sub1, tt.sub2, tt.sub3, tt.sub4, tt.sub5)
WHEN tt.sub1 THEN 'Sub1'
WHEN tt.sub2 THEN 'Sub2'
WHEN tt.sub3 THEN 'Sub3'
WHEN tt.sub4 THEN 'Sub4'
WHEN tt.sub5 THEN 'Sub5'
END High,
LEAST(tt.sub1, tt.sub2, tt.sub3, tt.sub4, tt.sub5) || ' -> ' ||
CASE LEAST(tt.sub1, tt.sub2, tt.sub3, tt.sub4, tt.sub5)
WHEN tt.sub1 THEN 'Sub1'
WHEN tt.sub2 THEN 'Sub2'
WHEN tt.sub3 THEN 'Sub3'
WHEN tt.sub4 THEN 'Sub4'
WHEN tt.sub5 THEN 'Sub5'
END Low
FROM studentsmark TT
|
|
|
|
|
| shake |
| Posted: Jun 17, 2008 07:18:37 AM | |
|
Total Post: 84
Joined: Jun, 2008
|
Could someone explain how to get name of the column (fieldname)?.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
