Tricky query.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Tricky query.

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    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.

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    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'

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    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.

  4. #4
    Join Date
    Sep 2005
    Posts
    278
    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.

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    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.

  6. #6
    Join Date
    Mar 2006
    Posts
    74
    Quote 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

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    Quote 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
  •  


Click Here to Expand Forum to Full Width