Solved

Strode procedure order by using oracle 9i.

Posted on 2004-04-02
26
506 Views
Last Modified: 2008-02-07
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
Comment
Question by:onlyamir007
  • 12
  • 11
  • +2
26 Comments
 
LVL 3

Expert Comment

by:eicheled
ID: 10746708
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
 
LVL 8

Expert Comment

by:william_jwd
ID: 10746824
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10746855
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
 
LVL 8

Expert Comment

by:william_jwd
ID: 10746889
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
 
LVL 2

Expert Comment

by:aabbas
ID: 10746892
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
 

Expert Comment

by:gulhaugen
ID: 10746935
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10747342
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10747375
hi aabbas
your script returning  6 same records .
0
 
LVL 8

Expert Comment

by:william_jwd
ID: 10755457
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10821213
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
 
LVL 8

Expert Comment

by:william_jwd
ID: 10821356
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10821545
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
 
LVL 8

Expert Comment

by:william_jwd
ID: 10821824
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:onlyamir007
ID: 10821853
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
 
LVL 8

Expert Comment

by:william_jwd
ID: 10821869
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10822026
IS there any way to check S-Procedure in oracle ?? i want 2 check this S-Procedure
0
 
LVL 8

Accepted Solution

by:
william_jwd earned 100 total points
ID: 10822092
you can just give

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

or just

Exec procedureName(Arg1, Arg2...);
0
 
LVL 4

Author Comment

by:onlyamir007
ID: 10822119
its showing "pl/SQL procedure  successfully completed" but i didn't get any results can't i see output of this procedure ...?
0
 
LVL 8

Expert Comment

by:william_jwd
ID: 10822510
you should give

set serveroutput on

before executing the procedure...
0
 
LVL 4

Author Comment

by:onlyamir007
ID: 10852386
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
 
LVL 8

Expert Comment

by:william_jwd
ID: 10857328
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10868988
Ohhh Thank You very much.........  yea it is working fine..... :)
0
 
LVL 4

Author Comment

by:onlyamir007
ID: 10869038
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
 
LVL 8

Expert Comment

by:william_jwd
ID: 10870127
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
 
LVL 4

Author Comment

by:onlyamir007
ID: 10870488
YOUR GREAT ............ THANK YOU VERY MUCH........
0
 
LVL 8

Expert Comment

by:william_jwd
ID: 10876143
Thanks, you are welcome...

William.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now