| Query CLOB column with values in a Set List [message #689972] |
Wed, 04 September 2024 10:28  |
Duane
Messages: 581
Registered: December 2002
|
Senior Member |
|
|
I'm trying to figure out how to join the column of TABLE_MASTER.ID to TABLE_SET.ID_SET. Do you use some sort of dbms_lob.substr on column ID_SET?
I've only seen one value within ID_SET but I'm assuming if multiple values are present then the values would look something like '[6,7,8,9]'.
Solution: Join TABLE_MASTER.ID to TABLE_SET.ID_SET
CREATE TABLE TABLE_SET
(
ID_SET CLOB
)
insert into table_set
(id_set)
values
('[6]');
commit;
CREATE TABLE TABLE_MASTER
(
ID NUMBER(19) NOT NULL
)
insert into table_master
(id)
values
(6);
commit;
|
|
|
|
| Re: Query CLOB column with values in a Set List [message #689973 is a reply to message #689972] |
Wed, 04 September 2024 11:48  |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Very bad design. You could use something like:
SELECT *
FROM TABLE_SET S,
TABLE_MASTER M
WHERE ',' || SUBSTR(S.ID_SET,2,LENGTH(S.ID_SET) - 2) || ',' LIKE '%,' || M.ID || ',%'
/
ID_SET ID
---------- ----------
[6] 6
SQL>
DROP TABLE TABLE_SET PURGE
/
CREATE TABLE TABLE_SET
(
ID_SET SYS.ODCINUMBERLIST
)
/
insert into table_set
(id_set)
values
(SYS.ODCINUMBERLIST(6));
commit;
SELECT *
FROM TABLE_SET S,
TABLE_MASTER M
WHERE M.ID IN (SELECT * FROM TABLE(S.ID_SET))
/
ID_SET ID
-------------------- ----------
ODCINUMBERLIST(6) 6
SQL>
SY.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
