asked on

Strode procedure order by using oracle 9i.

Hi,
i need to make SP where I want to get 5 last modified messages.
Scenario:
This is forum tables where I have 3 tables 1 for messages , thread and forums . Thread table contains all forums information like threadID,forumID,rootmsgID . all messages are in messages tables . I want 2 display one msg from one thread and total 5 last modified msg from all forums.

Ef = forums
Em=messages
Et = thread

CREATE OR REPLACE PROCEDURE ABC IS

      cursor c1 is
      select em.SUBJECT,em.thID,EM.MODIFIEDDATE from ef,em,et
where
ef.fID      =      et.fID and
et.thID      =      em.thID
      ORDER BY EM.MODIFIEDDATE DESC;
      begin
      for i in c1 loop
      --process records

      if (c1%rowCOUNT < 5) THEN
      EXIT;
      END IF;
      end loop;

END ABC;
I don’t know this procedure is working or now coz, I tried in my jsp script It was not working.

eicheled

if (c1%rowCOUNT <5) THEN
EXIT;
END IF;

will stop after the first iteration, no?

How about > instead?

if (c1%rowCOUNT > 5) THEN
EXIT;
END IF;

william_jwd

Try this,

CREATE OR REPLACE PROCEDURE ABC IS
Ctr Number := 0;
cursor c1 is
select em.SUBJECT,em.thID,EM.MODIFIEDDATE from ef,em,et
where
ef.fID = et.fID and
et.thID = em.thID
ORDER BY EM.MODIFIEDDATE DESC;
begin
for i in c1 loop
--process records
Ctr := Ctr + 1;
if (Ctr > 5) THEN
EXIT;
END IF;
end loop;
End;

onlyamir007

ASKER

first of all thanks for your quick reply
See,
this is my EM table :-

EMID - Subject - THID - MODIFIEDDATE
1 - TEST1 - 2 - 1079781214937
2 - TEST2 - 2 - 1079962332437
3 - TEST3 - 3 - 1051693722296
4 - TEST4 - 5 - 1079947494125
5 - TEST5 - 3 - 1054705479046
6 - TEST6 - 7 - 1054705479046
7 - TEST7 - 7 - 1054705479046

here is 7 records of EM tables i want distinct (thid) I wrote in my question as well
output should be like this

EMID - Subject - THID - MODIFIEDDATE
1 - TEST1 - 2 - 1079781214937
3 - TEST3 - 3 - 1051693722296
4 - TEST4 - 5 - 1079947494125
6 - TEST6 - 7 - 1054705479046

i hope u guys understand

william_jwd

Try this,

CREATE OR REPLACE PROCEDURE ABC IS
Ctr Number := 0;
cursor c1 is
select em.SUBJECT,em.thID,EM.MODIFIEDDATE from em
where exists (Select 1 from ef, et where
ef.fID = et.fID and
et.thID = em.thID)
and rowid in (Select min(rowid) from em group by thid)
ORDER BY EM.MODIFIEDDATE DESC;
begin
for i in c1 loop
--process records
Ctr := Ctr + 1;
if (Ctr > 5) THEN
EXIT;
END IF;
end loop;
End;

aabbas

As far as restricting the output to 5 ROWS is not an issue. You can do this by using ROWNUM < 6 in your WHERE clause.

For example, the following query will display first 5 joined employees.
select * from (select empno, ename, hiredate, job from emp order by hiredate) where ROWNUM < 5;

Similarly, you can get last 5 mofied rows using the following query.

select * from (select em.SUBJECT,em.thID,EM.MODIFIEDDATE from ef,em,et
where
ef.fID = et.fID and
et.thID = em.thID
ORDER BY EM.MODIFIEDDATE DESC) where ROWNUM < 6;

Just give it a try !!!

Best of luck.

gulhaugen

select distinct
--We do not want every message in every thread.
em.thid,
--the thread
first_value(em.subject) over(partition by em.thid order by em.modifieddate desc) subject,
--Just the first subject we find, when we look at the newsest modified date first.
first_value( em.modifieddate) over( partition by em.thid order by em.modifieddate desc) thid --The modified date you want.
from em;

I'm not at work right know so I don't have a DB in front of me, but I'm pretty sure it's correct. This will also lead you into the wonderful world of analytical functions. It really is fascinating how fast these are.

onlyamir007

ASKER

HI william_jwd,
you script seem's 2 be working but i need these col 2 be display and these chks should be there I was trying 2 alter you script but I couldn’t .

SELECT EM.THREADID,ET.ARCHIVE,EM.MODIFIEDDATE,EUR.USERNAME AS Author,ET.VIEWER,EM.SUBJECT,EM.USERID,EM.THREADID
            FROM EFORUM EF, ETHREAD ET, EMESSAGE EM , EMESSAGETREE EMT,EUREG EUR
            WHERE
            EF.FORUMID = ET.FORUMID             AND
            ET.THREADID = EM.THREADID        AND
            EM.USERID = EUR.USERID             AND
           EM.MESSAGEID = EMT.CHILDID        AND
            ET.APPROVED =        1       AND
            EMT.APPROVED =      1                  AND
            ET.ARCHIVE =       0
            ORDER BY EM.MODIFIEDDATE DESC

onlyamir007

ASKER

hi aabbas
your script returning 6 same records .

william_jwd

CREATE OR REPLACE PROCEDURE ABC IS
Ctr Number := 0;
cursor c1 is
select em.SUBJECT,em.thID,EM.MODIFIEDDATE,EM.THREADID,ET.ARCHIVE,EM.MODIFIEDDATE,EUR.USERNAME AS Author,ET.VIEWER,EM.SUBJECT,EM.USERID,EM.THREADID from EFORUM EF, ETHREAD ET, EMESSAGE EM , EMESSAGETREE EMT,EUREG EUR
where
EF.FORUMID = ET.FORUMID AND
ET.THREADID = EM.THREADID AND
EM.USERID = EUR.USERID AND
EM.MESSAGEID = EMT.CHILDID AND
ET.APPROVED = 1 AND
EMT.APPROVED = 1 AND
ET.ARCHIVE = 0 AND
and rowid in (Select min(rowid) from em group by thid)
ORDER BY EM.MODIFIEDDATE DESC;
begin
for i in c1 loop
--process records
Ctr := Ctr + 1;
if (Ctr > 5) THEN
EXIT;
END IF;
end loop;
End;

onlyamir007

ASKER

SOrry i was on vocation well i tried this ...... :$
i'm getting error "(13:9) ---- PL/SQL: ORA-00918: column ambiguously defined"

CREATE OR REPLACE PROCEDURE ABC IS
Ctr Number := 0;
cursor c1 is
select em.SUBJECT,em.THREADID,EM.MODIFIEDDATE,EM.THREADID,ET.ARCHIVE,EM.MODIFIEDDATE,EUR.USERNAME AS Author,ET.VIEWER,EM.SUBJECT,EM.USERID,EM.THREADID from EFORUM EF, ETHREAD ET, EMESSAGE EM , EMESSAGETREE EMT,EUREG EUR
where
EF.FORUMID = ET.FORUMID AND
ET.THREADID = EM.THREADID AND
EM.USERID = EUR.USERID AND
EM.MESSAGEID = EMT.CHILDID AND
ET.APPROVED = 1 AND
EMT.APPROVED = 1 AND
ET.ARCHIVE = 0 AND
and rowid in (Select min(rowid) from em group by THREADID)
ORDER BY EM.MODIFIEDDATE DESC;
begin
for i in c1 loop
--process records
Ctr := Ctr + 1;
if (Ctr > 5) THEN
EXIT;
END IF;
end loop;
End;

william_jwd

check whether you have missed the respective alias name for any column. i.e em.Archive, check whether you have missed em. for any column name. One more point is, in the query, change the following part

and rowid in (Select min(rowid) from em group by THREADID)
to
and rowid in (Select min(rowid) from EMESSAGE group by THREADID)

onlyamir007

ASKER

STILL i'm getting same error on this

---- >>AND <<---

on this line its showing error i duno ur using 2 time "and" may be thats y its giving . i tried after removing buts still giving error if u geme ur email then i'll send u original script i can't write here my email is onlyamir007@homail.com .

---- >> and rowid in (Select min(rowid) from em group by THREADID)<<---
ORDER BY EM.MODIFIEDDATE DESC;
begin
for i in c1 loop
--process records
Ctr := Ctr + 1;
if (Ctr > 5) THEN
EXIT;
END IF;
end loop;
End;

william_jwd

I thing, communicating through email is wrong according to EE policy. Why dont you submit the script here itself so that if someone else finds out the problem, they can also give you the solution. Post the table structure for the following tables :

EFORUM, ETHREAD, EMESSAGE, EMESSAGETREE,EUREG

Also post the complete procedure...

Regards,
William.

onlyamir007

ASKER

Ohh okie no prob... well when im trying to run this SQL script in SQL Editor i'm getting same error
i thk some thg wrog with SQL Query ... plzz chk this :$

select em.SUBJECT,EM.MODIFIEDDATE from EFORUM EF, ETHREAD ET, EMESSAGE EM , EMESSAGETREE EMT,EUREG EUR
where
EF.FORUMID = ET.FORUMID AND
ET.THREADID = EM.THREADID AND
EM.USERID = EUR.USERID AND
EM.MESSAGEID = EMT.CHILDID AND
ET.APPROVED = 1 AND
EMT.APPROVED = 1 AND
ET.ARCHIVE = 0 AND
AND rowid in (Select min(rowid) from EMESSAGE group by THREADID) ORDER BY EM.MODIFIEDDATE DESC

william_jwd

sorry, the problem is with the rowid, try to change this...

select em.SUBJECT,EM.MODIFIEDDATE from EFORUM EF, ETHREAD ET, EMESSAGE EM , EMESSAGETREE EMT,EUREG EUR
where
EF.FORUMID = ET.FORUMID AND
ET.THREADID = EM.THREADID AND
EM.USERID = EUR.USERID AND
EM.MESSAGEID = EMT.CHILDID AND
ET.APPROVED = 1 AND
EMT.APPROVED = 1 AND
ET.ARCHIVE = 0 AND
AND EM.rowid in (Select min(rowid) from EMESSAGE group by THREADID) ORDER BY EM.MODIFIEDDATE DESC

onlyamir007

ASKER

IS there any way to check S-Procedure in oracle ?? i want 2 check this S-Procedure

ASKER CERTIFIED SOLUTION

william_jwd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

onlyamir007

ASKER

its showing "pl/SQL procedure successfully completed" but i didn't get any results can't i see output of this procedure ...?

william_jwd

you should give

set serveroutput on

before executing the procedure...

onlyamir007

ASKER

This query returning more then 2 or 3 records it can be more then 3. i need from query only first record it should be after order by ??

select EM.USERID || ';' || EUR.USERNAME from Emessage EM,ETHREAD ET,EMESSAGETREE EMT,EUREG EUR
where em.threadid = 198
AND EM.USERID=EUR.USERID
AND EM.THREADID = ET.THREADID
AND EM.MESSAGEID = EMT.CHILDID
AND ET.APPROVED = 1
AND EMT.APPROVED = 1
order by em.modifieddate DESC

william_jwd

Try this,

Select * from
(select EM.USERID || ';' || EUR.USERNAME from Emessage EM,ETHREAD ET,EMESSAGETREE EMT,EUREG EUR
where em.threadid = 198
AND EM.USERID=EUR.USERID
AND EM.THREADID = ET.THREADID
AND EM.MESSAGEID = EMT.CHILDID
AND ET.APPROVED = 1
AND EMT.APPROVED = 1
order by em.modifieddate DESC)
Where Rownum < 2

onlyamir007

ASKER

Ohhh Thank You very much......... yea it is working fine..... :)

onlyamir007

ASKER

hi william_jwd,
can u tell me 1 more thg?? i have 1 filed as long .. i want output as varchar2 can i do this ?

william_jwd

You cannot select long fields directly as you select other fields. If you can hold the data in a varchar2 field, why do you want the field to be long. It is not good programming practice to hold a long data in a varchar2 variable.

Check the following link,

http://www.classicity.com/oracle/htdocs/forums/ClsyForumID125/8.html

onlyamir007

ASKER

YOUR GREAT ............ THANK YOU VERY MUCH........

william_jwd

Thanks, you are welcome...

William.