Tricky query.

[badrinathn]

Hi all,

I need to write a tricky query here:

We have two tables:
DOCDATA (xtopic_id varchar2(255))
and
TOPICS (id,topic).

DOCDATA contains rows with topic id's such as
row1-> 1, 2, 3, 4,5
row2-> 2, 4
row3-> 3, 4
row4-> 3, 5, 4
row5-> 1, 2

TOPICS table has
1, Book1
2, Book2
3, Book3 .....

Now I want to find all the records in DOCDATA, that has a book ('Book3')...or 'Book4' etc.

Any suggestions?
Thanks.

[tabreaz]

Try this query, hope it will be helpful

SELECT xtopic_id 
FROM docdata a, topics t
WHERE a.xtopic_id LIKE '%' || to_char(t.id) || '%'
AND t.topic = 'Book1'

[badrinathn]

Thanks Tabreaz,

My only concern is using the '%', would select all book1,book11, book111,book1111 etc if I just want for book1.

[tabreaz]

Dear badrinathn,

You can use SUBSTR function as

SELECT TO_NUMBER(SUBSTR('BOOK11', 5, Length('BOOK11')-4))
FROM DUAL;

[badrinathn]

thank you very much.
I ended up writing the following function and the query : This seems to be working out better.


CREATE OR REPLACE function test_func
(did in number,
xtopics_primary in varchar2,
id in number)
return number
is
plist varchar2(255) ;
--plist varchar2(255) := 'A,B,C,D';
plist1 varchar2(255);
ptablen BINARY_INTEGER;
ptab dbms_utility.uncl_array;
pdid number;
pid number;
idfound char(1);
begin
idfound := 'N';
pdid := did;
pid := id;
plist1 := replace(trim(xtopics_primary),' ','');
plist := replace(plist1,',',',x');
plist := 'x'||plist;
dbms_output.put_line('plist:'||plist1);
DBMS_utility.comma_to_table(
list=>plist,
tablen => ptablen,
tab=>ptab);
for i in 1..ptablen loop
--dbms_output.put_line(pdid ||', '|| substr(ptab(i),2));
if (substr(ptab(i),2)=pid) then
idfound := 'Y';
end if;
end loop;
if ( idfound = 'Y' ) then
return pdid;
else
return 0;
end if;
end;
/
and using the following query
select a.xtopics_primary, a.return_did,b.topic from ( select
test_func(did,xtopics_primary, &TopicID
) return_did, xtopics_primary , &TopicID topicid from
docmeta
where xtopics_primary is not null
) a, asq_topics b
where a.return_did <> 0
and a.topicid = b.id

[cjard]

There is always a better way to do the things

Yup.. structuring your DOCDATA table completely differntly would be one such a thing! That design of docdata is a very poor way of splitting an M:N relationship

[badrinathn]

Yup.. structuring your DOCDATA table completely differntly would be one such a thing! That design of docdata is a very poor way of splitting an M:N relationship

Yup - there is only so much you can do with a "canned" application