Home » SQL & PL/SQL » Client Tools » Searching by Row problem
Searching by Row problem [message #547704] Thu, 15 March 2012 18:07 Go to next message
Lansa
Messages: 5
Registered: March 2012
Location: California
Junior Member
Data
rec z1 z2 z3 z4 z5
6597 4 5 9 10 23
6596 2 5 7 19 22
6594 9 15 24 30 39
6593 9 13 20 27 39
6592 8 16 25 29 30
6591 2 8 13 29 35
6590 22 28 33 36 37
6589 4 5 11 26 36
6588 10 15 17 20 30
6587 4 9 12 32 39
6586 1 3 5 11 35
6585 3 8 26 30 31
6584 4 11 29 35 37
6583 12 15 33 34 38
6582 2 23 24 37 38
6581 9 14 16 25 33
6580 8 23 29 32 37
6579 14 18 20 24 34
6578 2 32 33 35 38
6577 1 16 18 19 30
6576 4 11 27 32 38

Expected Results
recz1 recz2 recz3 recz4 recz5
6589 6596 6594 6588 6582
6591 6589 6575 6595 6595
6593 6588 6582 6592 6593
6587 6591 6588 6576 6587
6591 6581 6581 6591 6588

I am trying to query the above table to take each column entry, row by row across all columns and search for the next previous occurrence and move the rec number to another column. See expected results. Row 6597 has column entries of 4, 5, 9, 10, 23. The next previous occurrence of 4 was found on row 6589 and moved to column recz1 etc. Can someone help me?
Re: Searching by Row problem [message #547705 is a reply to message #547704] Thu, 15 March 2012 18:25 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Searching by Row problem [message #547706 is a reply to message #547704] Thu, 15 March 2012 20:30 Go to previous messageGo to next message
Lansa
Messages: 5
Registered: March 2012
Location: California
Junior Member
I am sorry!

create table data
(rec number(6),
z1 number(2),
z2 number(2),
z3 number(2),
z4 number(2),
z5 number(2),
recz1 number(6),
recz2 number(6),
recz3 number(6),
recz4 number(6),
recz5 number(6));
Re: Searching by Row problem [message #547708 is a reply to message #547706] Thu, 15 March 2012 20:36 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
Does the desired/expected solution depend upon the order of the rows in table DATA?
Re: Searching by Row problem [message #547726 is a reply to message #547708] Fri, 16 March 2012 01:41 Go to previous messageGo to next message
Lansa
Messages: 5
Registered: March 2012
Location: California
Junior Member
Yes.
Thanks for the question. What I am trying to do,is to take each row's column (Z1 to Z5)entries and search for where they last occurred before the current row. If found, then the row number where they occurred is then copied to the columns(recz1...recz5). Matching z1 to recz1, z2 to recz2 etc. I want to be able to do that for each row until the last row. Please see my expected result table. The search for each column entry should cover all columns.

I am not sure whether I have made any sense.

Thanks
Re: Searching by Row problem [message #547728 is a reply to message #547726] Fri, 16 March 2012 01:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7986
Registered: November 2002
Location: California, USA
Senior Member
update data d1
set    d1.recz1 = 
       (select max (d2.rec)
        from   data d2
        where  d2.rec < d1.rec
        and    (d1.z1 = d2.z1 or
                d1.z1 = d2.z2 or
                d1.z1 = d2.z3 or
                d1.z1 = d2.z4 or
                d1.z1 = d2.z5)),
       d1.recz2 = 
       (select max (d2.rec)
        from   data d2
        where  d2.rec < d1.rec
        and    (d1.z2 = d2.z1 or
                d1.z2 = d2.z2 or
                d1.z2 = d2.z3 or
                d1.z2 = d2.z4 or
                d1.z2 = d2.z5)),
       d1.recz3 = 
       (select max (d2.rec)
        from   data d2
        where  d2.rec < d1.rec
        and    (d1.z3 = d2.z1 or
                d1.z3 = d2.z2 or
                d1.z3 = d2.z3 or
                d1.z3 = d2.z4 or
                d1.z3 = d2.z5)),
       d1.recz4 = 
       (select max (d2.rec)
        from   data d2
        where  d2.rec < d1.rec
        and    (d1.z4 = d2.z1 or
                d1.z4 = d2.z2 or
                d1.z4 = d2.z3 or
                d1.z4 = d2.z4 or
                d1.z4 = d2.z5)),
       d1.recz5 = 
       (select max (d2.rec)
        from   data d2
        where  d2.rec < d1.rec
        and    (d1.z5 = d2.z1 or
                d1.z5 = d2.z2 or
                d1.z5 = d2.z3 or
                d1.z5 = d2.z4 or
                d1.z5 = d2.z5))
/

Re: Searching by Row problem [message #547729 is a reply to message #547704] Fri, 16 March 2012 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is no order in the rows of a table unless you explicitly specify one.

2/ For any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

3/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Searching by Row problem [message #547809 is a reply to message #547728] Fri, 16 March 2012 16:46 Go to previous messageGo to next message
Lansa
Messages: 5
Registered: March 2012
Location: California
Junior Member
Works like a charm!!!!!!!!! Thanks so much! Can I ask another question?
I am wondering why I cannot view all my rows in data panel. I am using SQL Developer. When I click to view data, it starts from 2000 instead of the first record. I have 6500 rows of data. When I query for the first record, it does show up. What am I doing wrong?

Thanks again!
Re: Searching by Row problem [message #547810 is a reply to message #547809] Fri, 16 March 2012 17:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7986
Registered: November 2002
Location: California, USA
Senior Member
Does it return the rows as expected from SQL*Plus? I don't use SQL Developer, but I will move this thread to the client tools sub-forum, where SQL Developer issues belong.
Re: Searching by Row problem [message #547811 is a reply to message #547810] Fri, 16 March 2012 19:07 Go to previous messageGo to next message
Lansa
Messages: 5
Registered: March 2012
Location: California
Junior Member
Thanks again. Yes it does. It displayed the rows from the middle leaving out the beginning records. Something in options or preferences? I have looked thru but can't see any problems. All the rows are in the table. Can find them thru queries.
Re: Searching by Row problem [message #547824 is a reply to message #547811] Sat, 17 March 2012 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time follow the guide:

Michel Cadot wrote on Fri, 16 March 2012 07:53
1/ There is no order in the rows of a table unless you explicitly specify one.

2/ For any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

3/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


Re: Searching by Row problem [message #547828 is a reply to message #547809] Sat, 17 March 2012 01:52 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
Lansa wrote on Fri, 16 March 2012 22:46
What am I doing wrong?

Assuming that rows in table have an order. In reality (and as Michel already remarked), they have no implicit order.
If you want to get result set in specific order, you have to specify rule(s) for determining it - either in ORDER BY clause of the query or entering it in "Sort..." dialog box when watching table data in SQL developer. That is the only way for achieving it. Of course, there must be some column(s) for telling that. How did you know that the row is "first" only from data itself?

As far as I know, SQL Developer shows only first N rows from the result set (default is 50, it may be changed in some settings; I found it under Preferences -> Database -> Advanced Parameters -> Sql Array Fetch Size, but it may differ in your version). Maybe you should get acquainted with the tool you are using, go through its online its help, search online, ...
Previous Topic: SQL Developer error : IO Error : The Network Adapter could not establish the connection
Next Topic: How to configure SQL DEVELOPER Via Virtual Machine oracle database
Goto Forum:
  


Current Time: Sat Sep 20 19:59:00 CDT 2014

Total time taken to generate the page: 0.08071 seconds