-
Tricky query.
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.
There is always a better way to do the things.
-
Try this query, hope it will be helpful
Code:
SELECT xtopic_id
FROM docdata a, topics t
WHERE a.xtopic_id LIKE '%' || to_char(t.id) || '%'
AND t.topic = 'Book1'
-
Thanks Tabreaz,
My only concern is using the '%', would select all book1,book11, book111,book1111 etc if I just want for book1.
There is always a better way to do the things.
-
Dear badrinathn,
You can use SUBSTR function as
Code:
SELECT TO_NUMBER(SUBSTR('BOOK11', 5, Length('BOOK11')-4))
FROM DUAL;
Last edited by tabreaz; 03-07-2007 at 06:17 PM.
-
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
There is always a better way to do the things.
-
Originally Posted by badrinathn
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
-
Originally Posted by cjard
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
There is always a better way to do the things.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|