Topic: Oracle Certified Professional Exams (OCP & OCA) >> select query
|
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.
|
|
|
|
| meena |
Posted: May 08, 2007 02:02:52 AM |
Total Post: 7
Joined: Mar, 2007
|
Hi,
I have following query: this is not working:
what i need is to pull pos-grp(POSITION CODE), title(NAME OF POSITION) as of 21-mar-2005.
this is just a rough query .can someone help.
SELECT DISTINCT POS_GROUP,POS_TITLE,POS_STATUS_DATE
FROM TBLPOS_H WHERE PERSON_ID = x
AND POS_STATUS_DATE as of '21-MAR-2005'
i tried one more way b y giving this :
SELECT DISTINCT POS_GROUP,POS_TITLE,POS_STATUS_DATE
FROM TBLPOS_H WHERE PERSON_ID = 3288214
AND POS_STATUS_DATE < = '21-MAR-2005'
ORDER BY POS_STATUS_DATE DESC
In this i need only the first row bcos that will fetch me the postion as of 21-mar-2005 but i am not sure what to use. i mean if give FETCH ONE ROW ONLY,its giving error.
thanks,
Meenal.
|
|
|
 parth |
| Posted: May 08, 2007 09:52:25 AM |  |
|
Total Post: 17
Joined: Apr, 2007
|
yes, you can always select your record in Descending order and while doing so just make that column combine with ROWNUM or ROWID and find it max(rownum) or max(rowid) and you will always get the latest record for that DATE. and always first record for that matter of fact.
~Parth
|
|
|
|
|
| parth |
| Posted: May 08, 2007 10:00:12 AM | |
|
Total Post: 17
Joined: Apr, 2007
|
select rownum,ename from scott.emp
where rownum = (select max(rownum) from scott.emp where hiredate = '01/may/1981')
Probably this gives the answer, as far as i understand your problem
~Parth
|
|
|
|
|
| meena |
| Posted: May 10, 2007 12:58:43 AM | |
|
Total Post: 7
Joined: Mar, 2007
|
thanks for the answer.
But i did not do in that way.
i declared a cursor and took the first row.
Thnaks,
meenal
|
|
|
|
|
| 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 | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 239 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
