Home » SQL & PL/SQL » SQL & PL/SQL » extract and display data in a pattern (oracle 9i)
extract and display data in a pattern [message #399355] Tue, 21 April 2009 15:16 Go to next message
anoopam9
Messages: 6
Registered: December 2008
Junior Member
Hello,

I have data stored in the database like this in two columns FROM_URL and TO_URL

FROM_URL ---- TO_URL

start ---- a.html
a.html ---- b.html
b.html ---- c.html
start ---- k.html
k.html ---- h.html
h.html ---- f.html
f.html ---- t.html
start ---- k.html
k.html ---- l.html

So I want to display it like

1) start -> a.html -> b.html -> c.html
2) start -> k.html -> h.html -> h.html -> f.html -> t.html
3) start -> k.html -> l.html

I am bit confused here.

Thanks in advance.


[/ALIGN]
[/ALIGN]
Re: extract and display data in a pattern [message #399363 is a reply to message #399355] Tue, 21 April 2009 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You have a bigger problem that you do not even realize exists.

Rows in a table have NO inherent order.

Say your rows are returned in the following order

start ---- k.html
start ---- a.html
start ---- k.html
h.html ---- f.html
k.html ---- h.html
b.html ---- c.html
a.html ---- b.html
b.html ---- c.html
f.html ---- t.html
k.html ---- l.html

What is the expected/desired results?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Re: extract and display data in a pattern [message #399365 is a reply to message #399363] Tue, 21 April 2009 17:21 Go to previous messageGo to next message
anoopam9
Messages: 6
Registered: December 2008
Junior Member
Actually I have another column called entry time. So the data will be displayed in the order of pages visited.

From_url ---- To_url ---- Entry time

start ---- a.html ---- 10
a.html ---- b.html ---- 14
b.html ---- c.html ---- 16
start ---- k.html ---- 22
k.html ---- h.html ---- 26
h.html ---- f.html ---- 30
f.html ---- t.html ---- 34
start ---- k.html ---- 37
k.html ---- l.html ---- 41


I want to display data in sessions.

Re: extract and display data in a pattern [message #399366 is a reply to message #399355] Tue, 21 April 2009 17:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
search this forum for "pivot" to see how others have solve this FAQ!
Re: extract and display data in a pattern [message #399367 is a reply to message #399366] Tue, 21 April 2009 17:28 Go to previous messageGo to next message
anoopam9
Messages: 6
Registered: December 2008
Junior Member
Thanks for the guidance dude. I will look for it.
Re: extract and display data in a pattern [message #399372 is a reply to message #399367] Tue, 21 April 2009 19:28 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Sounds like you need graphviz...
http://www.graphviz.org/

c:\> type sample.DOT.txt | dot -Tpng -o sample.png

sample.DOT.txt
----------------
digraph g {
  node [fontname="Sans", fontsize=10];
  rankdir=LR;
  "1-start" -> "2-a.html" -> "1-b.html" -> "1-c.html"
  "2-start" -> "2-k.html" -> "2-h.html" -> "2-h.html" -> "2-f.html" -> "2-t.html"
  "3-start" -> "3-k.html" -> "3-l.html"
}

  • Attachment: sample.png
    (Size: 31.37KB, Downloaded 420 times)
Re: extract and display data in a pattern [message #399383 is a reply to message #399372] Tue, 21 April 2009 22:36 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could also try the SYS_CONNECT_BY_PATH() function

Ross Leishman
Re: extract and display data in a pattern [message #399394 is a reply to message #399355] Wed, 22 April 2009 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Always post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: extract and display data in a pattern [message #399796 is a reply to message #399394] Fri, 24 April 2009 02:30 Go to previous message
anoopam9
Messages: 6
Registered: December 2008
Junior Member
I have got an idea here. But this code executes the query only once. If I can write a condition which can loop the output in such a way that if

FROM_URL="START"

it starts a new count.
here is my code.

try {
      Statement stmt = conn.createStatement( );

     
        String query = "select FROM_URl, TO_URL from LOG ORDER BY TIME ASC";
	ResultSet rset = stmt.executeQuery( query );
	rset.next( );
	System.out.print ( "<ol><li>"  );
	System.out.print ( "Start"  );
	while(rset.getString(1) == "START")
	{
       	do{
       	//System.out.print ( "START");
	System.out.print (  "&nbsp;&rarr;&nbsp;" + rset.getString( 2 ) + "<br>");
	}while(rset.getString(1) != "START");
	System.out.print ( "</li></ol>" );
	}
	
        System.out.println( );
      stmt.close( );
      rset.close( );
    }
    catch( SQLException ex ) {
      System.out.println( ex );
    }


[Mod-edit: Frank added code-tags to improve readability]

[Updated on: Fri, 24 April 2009 04:36] by Moderator

Report message to a moderator

Previous Topic: handling collection type (merged 3)
Next Topic: Oracle 11g Pivot for Subquery
Goto Forum:
  


Current Time: Thu Feb 13 22:21:35 CST 2025