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  |
 |
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 #651877 is a reply to message #651863] |
Thu, 26 May 2016 12:55  |
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 #652095 is a reply to message #651882] |
Wed, 01 June 2016 11:23 |
|
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 |
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 |
 |
Barbara Boehmer
Messages: 9085
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 #652156 is a reply to message #652097] |
Thu, 02 June 2016 19:15 |
|
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 |
|
Barbara Boehmer
Messages: 9085
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 10:46:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
