OraFAQ Forum: Client Tools » Searching by Row problem

Home » Other » Client Tools » Searching by Row problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Searching by Row problem [message #547704]

Thu, 15 March 2012 18:07

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?

Report message to a moderator

Re: Searching by Row problem [message #547705 is a reply to message #547704]

Thu, 15 March 2012 18:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: Searching by Row problem [message #547706 is a reply to message #547704]

Thu, 15 March 2012 20:30

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));

Report message to a moderator

Re: Searching by Row problem [message #547708 is a reply to message #547706]

Thu, 15 March 2012 20:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Does the desired/expected solution depend upon the order of the rows in table DATA?

Report message to a moderator

Re: Searching by Row problem [message #547726 is a reply to message #547708]

Fri, 16 March 2012 01:41

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

Report message to a moderator

Re: Searching by Row problem [message #547728 is a reply to message #547726]

Fri, 16 March 2012 01:44

Barbara Boehmer
Messages: 9103
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))
/

Report message to a moderator

Re: Searching by Row problem [message #547729 is a reply to message #547704]

Fri, 16 March 2012 01:53

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Searching by Row problem [message #547809 is a reply to message #547728]

Fri, 16 March 2012 16:46

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!

Report message to a moderator

Re: Searching by Row problem [message #547810 is a reply to message #547809]

Fri, 16 March 2012 17:00

Barbara Boehmer
Messages: 9103
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.

Report message to a moderator

Re: Searching by Row problem [message #547811 is a reply to message #547810]

Fri, 16 March 2012 19:07

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.

Report message to a moderator

Re: Searching by Row problem [message #547824 is a reply to message #547811]

Sat, 17 March 2012 01:37

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Searching by Row problem [message #547828 is a reply to message #547809]

Sat, 17 March 2012 01:52

flyboy
Messages: 1903
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, ...

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 11:32:35 CST 2025