Home » SQL & PL/SQL » SQL & PL/SQL » Pattern recognition problem (12.1.0.2)
Pattern recognition problem [message #651857] Thu, 26 May 2016 08:30 Go to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Hello,

I have a project need to find a pattern (1010) on each row of 800 rows table. If the pattern is found, then write 1 to a new row on separate table as detail below.

===============
Table A (Data)
===============
C1 C2 C3 C4 C5 C6 C7 C8 C9
1 0 1 0 0 1 0 0 0

Condition: if found a pattern 1010 from the first set column (C1, C2, C3, C4), then write "1" to a new row on Table B. Looping to the next column for the next result.

C1, C2, C3, C4 = 1010 = 1
C2, C3, C4, C5 = 0100 = 0
C3, C4, C5, C6 = 1001 = 0

=================
Table B (Result)
=================
C1 C2 C3
1 0 0


As the Table A has about 800 rows, looping to each data row and writes the result to separate row on the Table B. So the Table B should also has 800 rows for the result of each data row.

Any help would be greatly appreciated!

[Updated on: Thu, 26 May 2016 08:40] by Moderator

Report message to a moderator

Re: Pattern recognition problem [message #651860 is a reply to message #651857] Thu, 26 May 2016 08:40 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I've removed your duplicate post and given your topic a sensible title.

What SQL have you tried so far?
Re: Pattern recognition problem [message #651863 is a reply to message #651860] Thu, 26 May 2016 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also, If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Re: Pattern recognition problem [message #651877 is a reply to message #651863] Thu, 26 May 2016 12:55 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Is your original table actually contain 9 columns? We need to know what is the first table and what is the structure of the new table that you want to go in. How can we guess at an insert command without knowing the structure of the table. Whqat is the key column that will link the first table to the second? Do they have to be linked? A simple pattern like that is easy

select *
from my_table
where c1||c2||c3||c4 like '%1010%';

[Updated on: Thu, 26 May 2016 12:56]

Report message to a moderator

Re: Pattern recognition problem [message #651882 is a reply to message #651857] Thu, 26 May 2016 13:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
This is the most that I can glean from your limited description and example with no explanation as to purpose. If this is not what you want or not enough that you can modify it to suit your needs, then please explain further.

-- test tables and test data:
SCOTT@orcl_12.1.0.2.0> DESC a
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 C1                                                             NUMBER
 C2                                                             NUMBER
 C3                                                             NUMBER
 C4                                                             NUMBER
 C5                                                             NUMBER
 C6                                                             NUMBER
 C7                                                             NUMBER
 C8                                                             NUMBER
 C9                                                             NUMBER

SCOTT@orcl_12.1.0.2.0> SELECT * FROM a
  2  /

        C1         C2         C3         C4         C5         C6         C7         C8         C9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          0          1          0          0          1          0          0          0

1 row selected.

SCOTT@orcl_12.1.0.2.0> DESC b
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 C1                                                             NUMBER
 C2                                                             NUMBER
 C3                                                             NUMBER

SCOTT@orcl_12.1.0.2.0> SELECT * FROM b
  2  /

no rows selected


-- insert and results:
SCOTT@orcl_12.1.0.2.0> INSERT INTO b (c1, c2, c3)
  2  SELECT n1, n2, n3
  3  FROM   (SELECT CASE WHEN c1 = 1 AND c2 = 0 AND c3 = 1 AND c4 = 0 THEN 1 ELSE 0 END n1,
  4  		    CASE WHEN c2 = 1 AND c3 = 0 AND c4 = 1 AND c5 = 0 THEN 1 ELSE 0 END n2,
  5  		    CASE WHEN c3 = 1 AND c4 = 0 AND c5 = 1 AND c6 = 0 THEN 1 ELSE 0 END n3
  6  	     FROM   a)
  7  WHERE  n1 = 1 OR n2 = 1 OR n3 = 1
  8  /

1 row created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM b
  2  /

        C1         C2         C3
---------- ---------- ----------
         1          0          0

1 row selected.

Re: Pattern recognition problem [message #652095 is a reply to message #651882] Wed, 01 June 2016 11:23 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara,

Thanks for your reply. I put your solution in my test env and it works as expected. Since I need to check and loop through 800 rows of binary data and insert the result in the result table, I have added the loop statement to your solution as below.

One problem that if I have 5 new data rows adding to the 800 rows table, is there a way to scan just those new 5 rows based on the SID sequence number instead of rescanning of 805 rows every time.

Thanks in advance for your help!


> DECLARE
   CURSOR c1 IS select SID from TBDATA_C12;
   i number;
BEGIN 
  FOR x IN c1 LOOP
   i := x.sid;
        INSERT INTO TBDATA_C12_P1010 (SID, C1_4, C2_5, C3_6, C4_7, C5_8, C6_9, C7_10, C8_11, C9_12)
    SELECT n1, n2, n3, n4, n5, n6, n7, n8, n9, n10
    FROM   
    (SELECT 
CASE WHEN SID = i THEN i ELSE 0 END n1,
CASE WHEN C1 = 1 AND C2 = 0 AND C3 = 1 AND C4 = 0 THEN 1 ELSE 0 END n2,
CASE WHEN C2 = 1 AND C3 = 0 AND C4 = 1 AND C5 = 0 THEN 1 ELSE 0 END n3,
CASE WHEN C3 = 1 AND C4 = 0 AND C5 = 1 AND C6 = 0 THEN 1 ELSE 0 END n4,
CASE WHEN C4 = 1 AND C5 = 0 AND C6 = 1 AND C7 = 0 THEN 1 ELSE 0 END n5,
CASE WHEN C5 = 1 AND C6 = 0 AND C7 = 1 AND C8 = 0 THEN 1 ELSE 0 END n6,
CASE WHEN C6 = 1 AND C7 = 0 AND C8 = 1 AND C9 = 0 THEN 1 ELSE 0 END n7,
CASE WHEN C7 = 1 AND C8 = 0 AND C9 = 1 AND C10 = 0 THEN 1 ELSE 0 END n8,
CASE WHEN C8 = 1 AND C9 = 0 AND C10 = 1 AND C11 = 0 THEN 1 ELSE 0 END n9,
CASE WHEN C9 = 1 AND C10 = 0 AND C11 = 1 AND C12 = 0 THEN 1 ELSE 0 END n10
         FROM TBDATA_C12 where SID = i)
    WHERE  n1 = i OR n2 = 1 OR n3 = 1 OR n4 = 1 OR n5 = 1 OR n6 = 1 OR n7 = 1 OR n8 = 1 OR n9 = 1 OR n10 = 1;
    END LOOP;
END;
/




> SELECT * FROM TBDATA_C12_P1010;

SID	C1_4	C2_5	C3_6	C4_7	C5_8	C6_9	C7_10	C8_11	C9_12
1	1	0	0	0	0	0	0	0	0
2	0	0	1	0	0	0	0	1	0
3	0	0	0	0	1	0	0	0	0


> SELECT * FROM TBDATA_C12;

SID	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12
1	1	0	1	0	0	0	0	1	0	1	1	1
2	0	0	1	0	1	0	0	1	0	1	0	1
3	0	0	0	0	1	0	1	0	0	0	0	0


> desc TBDATA_C12_P1010;

Name  Null     Type   
----- -------- ------ 
SID   NOT NULL NUMBER 
C1_4           NUMBER 
C2_5           NUMBER 
C3_6           NUMBER 
C4_7           NUMBER 
C5_8           NUMBER 
C6_9           NUMBER 
C7_10          NUMBER 
C8_11          NUMBER 
C9_12          NUMBER 


> desc TBDATA_C12;


Name Null     Type   
---- -------- ------ 
SID  NOT NULL NUMBER 
C1            NUMBER 
C2            NUMBER 
C3            NUMBER 
C4            NUMBER 
C5            NUMBER 
C6            NUMBER 
C7            NUMBER 
C8            NUMBER 
C9            NUMBER 
C10           NUMBER 
C11           NUMBER 
C12           NUMBER


[Updated on: Wed, 01 June 2016 11:25]

Report message to a moderator

Re: Pattern recognition problem [message #652096 is a reply to message #652095] Wed, 01 June 2016 11:26 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Try the following select

select x.SID from TBDATA_C12 x
where not exists
(select null
from TBDATA_C12_P1010 y
where x.id = y.id);


Use it in your procedure after you test it
Re: Pattern recognition problem [message #652097 is a reply to message #652095] Wed, 01 June 2016 12:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
There is no looping necessary. Looping only makes it slower. All you need is one SQL insert statement, as shown below. You can avoid inserting duplicate rows by adding a NOT EXISTS clause, as Bill B suggested, which I have included in the demonstration below.

-- test environment:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM tbdata_c12
  2  /

       SID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          1          0          0          0          0          1          0          1          1          1
         2          0          0          1          0          1          0          0          1          0          1          0          1
         3          0          0          0          0          1          0          1          0          0          0          0          0

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM tbdata_c12_p1010
  2  /

no rows selected


-- insert:
SCOTT@orcl_12.1.0.2.0> INSERT INTO TBDATA_C12_P1010 (SID, C1_4, C2_5, C3_6, C4_7, C5_8, C6_9, C7_10, C8_11, C9_12)
  2  SELECT sid, n1, n2, n3, n4, n5, n6, n7, n8, n9
  3  FROM   (SELECT sid,
  4  		    CASE WHEN c1 = 1 AND c2 = 0 AND c3 = 1 AND c4 = 0 THEN 1 ELSE 0 END n1,
  5  		    CASE WHEN c2 = 1 AND c3 = 0 AND c4 = 1 AND c5 = 0 THEN 1 ELSE 0 END n2,
  6  		    CASE WHEN c3 = 1 AND c4 = 0 AND c5 = 1 AND c6 = 0 THEN 1 ELSE 0 END n3,
  7  		    CASE WHEN c4 = 1 AND c5 = 0 AND c6 = 1 AND c7 = 0 THEN 1 ELSE 0 END n4,
  8  		    CASE WHEN c5 = 1 AND c6 = 0 AND c7 = 1 AND c8 = 0 THEN 1 ELSE 0 END n5,
  9  		    CASE WHEN c6 = 1 AND c7 = 0 AND c8 = 1 AND c9 = 0 THEN 1 ELSE 0 END n6,
 10  		    CASE WHEN c7 = 1 AND c8 = 0 AND c9 = 1 AND c10 = 0 THEN 1 ELSE 0 END n7,
 11  		    CASE WHEN c8 = 1 AND c9 = 0 AND c10 = 1 AND c11 = 0 THEN 1 ELSE 0 END n8,
 12  		    CASE WHEN c9 = 1 AND c10 = 0 AND c11 = 1 AND c12 = 0 THEN 1 ELSE 0 END n9
 13  	     FROM   tbdata_c12
 14  	     WHERE  NOT EXISTS
 15  		    (SELECT NULL
 16  		     FROM   tbdata_c12_p1010
 17  		     WHERE  tbdata_c12.sid = tbdata_c12_p1010.sid))
 18  WHERE  n1 = 1 OR n2 = 1 OR n3 = 1 OR n4 = 1 OR n5 = 1 OR n6 = 1 OR n7 = 1 OR n8 = 1 OR n9 = 1
 19  /

3 rows created.


-- results:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TBDATA_C12_P1010 ORDER BY sid
  2  /

       SID       C1_4       C2_5       C3_6       C4_7       C5_8       C6_9      C7_10      C8_11      C9_12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          0          0          0          0          0          0          0
         2          0          0          1          0          0          0          0          1          0
         3          0          0          0          0          1          0          0          0          0

3 rows selected.


Re: Pattern recognition problem [message #652098 is a reply to message #652097] Wed, 01 June 2016 12:47 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Nice Barbara,
I should have made the same suggestion, but I was trying to fit it within his existing code. Next time I will do it correctly.
Re: Pattern recognition problem [message #652156 is a reply to message #652097] Thu, 02 June 2016 19:15 Go to previous messageGo to next message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara and Bill,

Thanks so much for your helps and suggestions. I tested and it works as expected.
Another question? Since tbdata_c12 and tbdata_p1010 table both have different column name, is there a way to query (or select) the row data of sid = 1 from both tables displaying in the same grid.
Similar to using union for joining of two tables with the same structure as below.

Thanks again!


select * from tbdata_c12
where sid = 1
union 
select * from tbdata_c12_p1010 
where sid = 1
/

[Updated on: Thu, 02 June 2016 19:17]

Report message to a moderator

Re: Pattern recognition problem [message #652158 is a reply to message #652156] Thu, 02 June 2016 21:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
If they have the same number of columns in the same order and the data types match, then you can do as below, but it is generally considered a bad practice, as these things may change. I added some columns to one table to enable that.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM tbdata_c12
  2  WHERE sid = 1
  3  UNION
  4  SELECT * FROM tbdata_c12_p1010
  5  WHERE sid = 1
  6  /

       SID         C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          0          0          0          0          0          0          0
         1          1          0          1          0          0          0          0          1          0          1          1          1

2 rows selected.


Iff the number of columns differs or they are in different orders or data types differ, then you will need to list the names of the individual columns. This is general consider a better practice.

SCOTT@orcl_12.1.0.2.0> SELECT sid, c1, c2, c3, c4
  2  FROM   tbdata_c12
  3  WHERE  sid = 1
  4  UNION
  5  SELECT sid, c1_4, c2_5, c3_6, c4_7
  6  FROM  tbdata_c12_p1010
  7  WHERE sid = 1
  8  /

       SID         C1         C2         C3         C4
---------- ---------- ---------- ---------- ----------
         1          1          0          0          0
         1          1          0          1          0

2 rows selected.


Note that usage of UNION eliminates duplicates, where UNION ALL includes duplicates.
Re: Pattern recognition problem [message #652228 is a reply to message #652158] Sat, 04 June 2016 13:10 Go to previous message
bdtran
Messages: 17
Registered: May 2016
Junior Member
Barbara, Thanks so much for the helps!

Previous Topic: Transpose rows to columns without aggregation
Next Topic: Adding Cells from two different tables
Goto Forum:
  


Current Time: Fri Jul 30 00:23:59 CDT 2021