Home » SQL & PL/SQL » SQL & PL/SQL » Need to match combination string at database (Oracle 11g, AIX server)
Need to match combination string at database [message #657484] |
Thu, 10 November 2016 01:19 |
|
Shan.kumar
Messages: 5 Registered: November 2016
|
Junior Member |
|
|
Hi,
I need to create a sql or pl/sql to match different combination of string separated by comma(,) to a database column and fetch the rows.
My example_string='TEST-001,TEST-002,TEST-003,TEST-004';
And my database columns contains like below,
COMBINATION DESCRIPTION
=========== =============
TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-007,TEST-005,TEST-004 Description 2
TEST-001,TEST-003,TEST-004,TEST-002 Description 3
TEST-008,TEST-009,TEST-010,TEST-011 Description 4
I need to create a query based on above example_string, which will give me output of below rows.
TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-003,TEST-004,TEST-002 Description 3
As I don't have big knowledge on pl/sql and comparatively new on that, iam stuck. Any help will be appreciated.
Thanks
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Thu, 10 November 2016 01:43] by Moderator Report message to a moderator
|
|
|
Re: Need to match combination string at database [message #657486 is a reply to message #657484] |
Thu, 10 November 2016 02:06 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I am not sure what you are trying to do. The definition of the table would help. But I think you may want to use a context text index, so you could write a query such as
select * from your_table where contains(your_column,'TEST-001') > 0 and contains ..... ;
|
|
|
|
Re: Need to match combination string at database [message #657489 is a reply to message #657486] |
Thu, 10 November 2016 02:22 |
|
Shan.kumar
Messages: 5 Registered: November 2016
|
Junior Member |
|
|
Hi John,
Thanks for your response. What I want is below,
According to requirement, we will have a table, as I explained. This table will contain information of product's material. This materials are the combination. Now, same combination of material can make different product. But the ordering of the materials in "Combination" column may differ. But I need to find out all the products with the given combination.
As my input material combination is 'TEST-001,TEST-002,TEST-003,TEST-004'.
And my query should return below values as they consist of same material(just ordering is different separated by comma) but products are different. As you can see the description is different.
TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-003,TEST-004,TEST-002 Description 3
Hope this input will help you to understand the issue. Please help. Iam not sure how to proceed.
Thanks
|
|
|
|
|
Re: Need to match combination string at database [message #657492 is a reply to message #657486] |
Thu, 10 November 2016 02:24 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following method uses an Oracle Text context index.
-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_tab
2 /
COMBINATION DESCRIPTION
----------------------------------- ---------------
TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-007,TEST-005,TEST-004 Description 2
TEST-001,TEST-003,TEST-004,TEST-002 Description 3
TEST-008,TEST-009,TEST-010,TEST-011 Description 4
4 rows selected.
-- lexer with hyphen as printjoin:
SCOTT@orcl_12.1.0.2.0> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
3 CTX_DDL.SET_ATTRIBUTE ('test_lex', 'PRINTJOINS', '-');
4 END;
5 /
PL/SQL procedure successfully completed.
-- Oracle Text context index that uses lexer:
SCOTT@orcl_12.1.0.2.0> CREATE INDEX combination_idx ON test_tab (combination)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('LEXER test_lex')
4 /
Index created.
-- variable:
SCOTT@orcl_12.1.0.2.0> VARIABLE example_string VARCHAR2(100)
SCOTT@orcl_12.1.0.2.0> EXEC :example_string := 'TEST-001,TEST-002,TEST-003,TEST-004'
PL/SQL procedure successfully completed.
-- SQL query:
SCOTT@orcl_12.1.0.2.0> SELECT combination, description
2 FROM test_tab
3 WHERE CONTAINS
4 (combination,
5 '{' || REPLACE (:example_string, ',', '} AND {') || '}') > 0
6 /
COMBINATION DESCRIPTION
----------------------------------- ---------------
TEST-001,TEST-002,TEST-003,TEST-004 Description 1
TEST-001,TEST-003,TEST-004,TEST-002 Description 3
2 rows selected.
|
|
|
Re: Need to match combination string at database [message #657497 is a reply to message #657492] |
Thu, 10 November 2016 04:31 |
|
Shan.kumar
Messages: 5 Registered: November 2016
|
Junior Member |
|
|
Hi Boehmer,
Many thanks everyone and specially you. The solution almost worked fine, but there is one issue. First let me explain few points,
My Table contains below combination,
COMBINATION DESCRIPTION
ART-001,ART-002,ART-003,ART-004,ART-005,ART-006 SPR1
ART-007,ART-008,ART-009,ART-010,ART-011,ART-012 SPR2
ART-003,ART-005,ART-001,ART-004,ART-002,ART-006 SPR3
ART-001,ART-002,ART-003,ART-004 SPR4
ART-001,ART-002,ART-008,ART-011,ART-005,ART-012 SPR5
ART-004,ART-002,ART-001,ART-003 SPR6
I have created the below index as of now,
CREATE INDEX myindex ON TEST_MATCH(COMBINATION)
INDEXTYPE IS CTXSYS.CONTEXT;
And fired your query,
SELECT * FROM TEST_MATCH
WHERE CONTAINS
(COMBINATION,
'{' || REPLACE ('ART-001,ART-002,ART-003,ART-004,ART-005,ART-006', ',', '} AND {') || '}') > 0;
That gave the expected result. But when I changed the input as below,
SELECT * FROM TEST_MATCH
WHERE CONTAINS
(COMBINATION,
'{' || REPLACE ('ART-001,ART-002,ART-003,ART-004', ',', '} AND {') || '}') > 0;
I got the below unexpected result,
ART-001,ART-002,ART-003,ART-004,ART-005,ART-006 SPR1
ART-003,ART-005,ART-001,ART-004,ART-002,ART-006 SPR3
ART-001,ART-002,ART-003,ART-004 SPR4
ART-004,ART-002,ART-001,ART-003 SPR6
The problem is, with the above query, I don't expect 1st and 2nd row in result. Is there any way I can stick to the result for 3rd and 4th row for this input? Because SPR1 and SPR2 contains some extra materials. So these products should not come with these material combination(ART-001,ART-002,ART-003,ART-004) from business point of view.
Thanks
|
|
|
|
Re: Need to match combination string at database [message #657506 is a reply to message #657497] |
Thu, 10 November 2016 22:05 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
In that case, I probably wouldn't use Oracle Text. Instead I would do something like below, separating the strings, then putting them back together in order, then comparing the results for equality.
-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_match
2 /
COMBINATION DESCRIPTION
-------------------------------------------------- ---------------
ART-001,ART-002,ART-003,ART-004,ART-005,ART-006 SPR1
ART-007,ART-008,ART-009,ART-010,ART-011,ART-012 SPR2
ART-003,ART-005,ART-001,ART-004,ART-002,ART-006 SPR3
ART-001,ART-002,ART-003,ART-004 SPR4
ART-001,ART-002,ART-008,ART-011,ART-005,ART-012 SPR5
ART-004,ART-002,ART-001,ART-003 SPR6
6 rows selected.
-- variable:
SCOTT@orcl_12.1.0.2.0> VARIABLE example_string VARCHAR2(35)
SCOTT@orcl_12.1.0.2.0> EXEC :example_string := 'ART-001,ART-002,ART-003,ART-004'
PL/SQL procedure successfully completed.
-- SQL query:
SCOTT@orcl_12.1.0.2.0> SELECT t1.combination, description
2 FROM (SELECT combination, description,
3 LISTAGG (vals, ',') WITHIN GROUP (ORDER BY vals) combo
4 FROM (SELECT combination, description,
5 REGEXP_SUBSTR (combination, '[^,]+', 1, COLUMN_VALUE) vals
6 FROM test_match,
7 TABLE
8 (CAST
9 (MULTISET
10 (SELECT LEVEL
11 FROM DUAL
12 CONNECT BY LEVEL <= REGEXP_COUNT (combination, ',') + 1)
13 AS SYS.ODCINUMBERLIST)))
14 GROUP BY combination, description) t1,
15 (SELECT LISTAGG (vals, ',') WITHIN GROUP (ORDER BY vals) combo
16 FROM (SELECT REGEXP_SUBSTR (:example_string, '[^,]+', 1, LEVEL) vals
17 FROM DUAL
18 CONNECT BY LEVEL <= REGEXP_COUNT (:example_string, ',') + 1)) t2
19 WHERE t1.combo = t2.combo
20 /
COMBINATION DESCRIPTION
------------------------------------------------------------ ---------------
ART-001,ART-002,ART-003,ART-004 SPR4
ART-004,ART-002,ART-001,ART-003 SPR6
2 rows selected.
|
|
|
Re: Need to match combination string at database [message #657508 is a reply to message #657506] |
Fri, 11 November 2016 01:17 |
|
Shan.kumar
Messages: 5 Registered: November 2016
|
Junior Member |
|
|
Hi Boehmer,
Thanks very much. This solution worked perfectly and that is exactly what I wanted.
Just one more help I need and after that I should be able to develop the entire system.
As a add on part of my system, I should be able to make different unique permutation of the materials. For that, I managed to get the below code and its working fine now. But problem is, I have to make the union list dynamic, as I can have different combination of materials,
with testdata as
(select 'ART-001' as col from dual
union
select 'ART-002' from dual
union
select 'ART-003' from dual
union
select 'ART-004' from dual
union
select 'ART-005' from dual
union
select 'ART-006' from dual),
-- create child column
testdata2 as
(select t.col as col1, t.col as col2 from testdata t)
select level, sys_connect_by_path(col1, ',') path
from testdata2 t
connect by prior col1 < col2
order by level, sys_connect_by_path(col1, ',');
Is there any way in this code, that I can put the material list in variable and send the variable to the code for permutation?
Thanks
[Updated on: Fri, 11 November 2016 06:02] Report message to a moderator
|
|
|
Re: Need to match combination string at database [message #657515 is a reply to message #657508] |
Fri, 11 November 2016 16:32 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> VARIABLE example_string VARCHAR2(50)
SCOTT@orcl_12.1.0.2.0> EXEC :example_string := 'ART-001,ART-002,ART-003,ART-004,ART-005,ART-006'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COLUMN path FORMAT A60
SCOTT@orcl_12.1.0.2.0> WITH
2 testdata AS
3 (SELECT REGEXP_SUBSTR (:example_string, '[^,]+', 1, LEVEL) AS col
4 FROM DUAL
5 CONNECT BY LEVEL <= REGEXP_COUNT (:example_string, ',') + 1),
6 testdata2 AS
7 (SELECT col as col1, col as col2
8 FROM testdata)
9 SELECT LEVEL, SYS_CONNECT_BY_PATH (col1, ',') path
10 FROM testdata2
11 CONNECT BY PRIOR col1 < col2
12 ORDER BY LEVEL, path
13 /
LEVEL PATH
---------- ------------------------------------------------------------
1 ,ART-001
1 ,ART-002
1 ,ART-003
1 ,ART-004
1 ,ART-005
1 ,ART-006
2 ,ART-001,ART-002
2 ,ART-001,ART-003
2 ,ART-001,ART-004
2 ,ART-001,ART-005
2 ,ART-001,ART-006
2 ,ART-002,ART-003
2 ,ART-002,ART-004
2 ,ART-002,ART-005
2 ,ART-002,ART-006
2 ,ART-003,ART-004
2 ,ART-003,ART-005
2 ,ART-003,ART-006
2 ,ART-004,ART-005
2 ,ART-004,ART-006
2 ,ART-005,ART-006
3 ,ART-001,ART-002,ART-003
3 ,ART-001,ART-002,ART-004
3 ,ART-001,ART-002,ART-005
3 ,ART-001,ART-002,ART-006
3 ,ART-001,ART-003,ART-004
3 ,ART-001,ART-003,ART-005
3 ,ART-001,ART-003,ART-006
3 ,ART-001,ART-004,ART-005
3 ,ART-001,ART-004,ART-006
3 ,ART-001,ART-005,ART-006
3 ,ART-002,ART-003,ART-004
3 ,ART-002,ART-003,ART-005
3 ,ART-002,ART-003,ART-006
3 ,ART-002,ART-004,ART-005
3 ,ART-002,ART-004,ART-006
3 ,ART-002,ART-005,ART-006
3 ,ART-003,ART-004,ART-005
3 ,ART-003,ART-004,ART-006
3 ,ART-003,ART-005,ART-006
3 ,ART-004,ART-005,ART-006
4 ,ART-001,ART-002,ART-003,ART-004
4 ,ART-001,ART-002,ART-003,ART-005
4 ,ART-001,ART-002,ART-003,ART-006
4 ,ART-001,ART-002,ART-004,ART-005
4 ,ART-001,ART-002,ART-004,ART-006
4 ,ART-001,ART-002,ART-005,ART-006
4 ,ART-001,ART-003,ART-004,ART-005
4 ,ART-001,ART-003,ART-004,ART-006
4 ,ART-001,ART-003,ART-005,ART-006
4 ,ART-001,ART-004,ART-005,ART-006
4 ,ART-002,ART-003,ART-004,ART-005
4 ,ART-002,ART-003,ART-004,ART-006
4 ,ART-002,ART-003,ART-005,ART-006
4 ,ART-002,ART-004,ART-005,ART-006
4 ,ART-003,ART-004,ART-005,ART-006
5 ,ART-001,ART-002,ART-003,ART-004,ART-005
5 ,ART-001,ART-002,ART-003,ART-004,ART-006
5 ,ART-001,ART-002,ART-003,ART-005,ART-006
5 ,ART-001,ART-002,ART-004,ART-005,ART-006
5 ,ART-001,ART-003,ART-004,ART-005,ART-006
5 ,ART-002,ART-003,ART-004,ART-005,ART-006
6 ,ART-001,ART-002,ART-003,ART-004,ART-005,ART-006
63 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:00:13 CDT 2024
|