• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

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.



0
onlyamir007
Asked:
onlyamir007
  • 12
  • 11
  • +2
1 Solution
 
eicheledCommented:
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;
0
 
william_jwdCommented:
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;
0
 
onlyamir007Author Commented:
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
william_jwdCommented:
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;
0
 
aabbasCommented:
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.
0
 
gulhaugenCommented:
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.
0
 
onlyamir007Author Commented:
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
            
0
 
onlyamir007Author Commented:
hi aabbas
your script returning  6 same records .
0
 
william_jwdCommented:
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;
0
 
onlyamir007Author Commented:
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;
0
 
william_jwdCommented:
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)
0
 
onlyamir007Author Commented:
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;
0
 
william_jwdCommented:
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.
0
 
onlyamir007Author Commented:
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
0
 
william_jwdCommented:
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
0
 
onlyamir007Author Commented:
IS there any way to check S-Procedure in oracle ?? i want 2 check this S-Procedure
0
 
william_jwdCommented:
you can just give

begin
  procedureName(Arg1, Arg2...);
end;

or just

Exec procedureName(Arg1, Arg2...);
0
 
onlyamir007Author Commented:
its showing "pl/SQL procedure  successfully completed" but i didn't get any results can't i see output of this procedure ...?
0
 
william_jwdCommented:
you should give

set serveroutput on

before executing the procedure...
0
 
onlyamir007Author Commented:
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

0
 
william_jwdCommented:
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
0
 
onlyamir007Author Commented:
Ohhh Thank You very much.........  yea it is working fine..... :)
0
 
onlyamir007Author Commented:
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 ?
0
 
william_jwdCommented:
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
0
 
onlyamir007Author Commented:
YOUR GREAT ............ THANK YOU VERY MUCH........
0
 
william_jwdCommented:
Thanks, you are welcome...

William.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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