Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2012
    Posts
    13

    Unanswered: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL

    I have one query regarding how to retrieve the CLOB data.

    The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
    Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset.
    But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.

    Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error.

    I know that DISTINCT keyword cannot be used for CLOB datatypes.

    So can anyone help me out here with any work around/ alternate way to get the CLOB value in the SELECT QUERY.

    So please help me out in this ASAP.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If no duplicates in clob columns,
    try something like...
    Code:
    SELECT a.*
         , b.clob1
         , b.clob2
     FROM  (SELECT DISTINCT
                   ... /* columns list except clob columns */
             FROM  ...
                   ...
           ) a
     JOIN
           table_clobs b
      ON   ...

  3. #3
    Join Date
    Aug 2012
    Posts
    13
    Please don't mind.
    Can you please elaborate.
    The JOIN - ON is where i'm facing the problem.

    If it's just 2 tables then some how it could have been done.
    Since there are more than 5 tables joining on particular column is creating problem.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please use your brain.
    I didn't showed complete query. I just showed the way to avoid DISTINCT for clob columns.

    I assumed Ialic-Red as your original query.
    And, no DISTINCT for clob columns in my example.
    Code:
    SELECT a.*
         , b.clob1
         , b.clob2
     FROM  (SELECT DISTINCT
                   ... /* columns list except clob columns */
             FROM  ...
                   ...
            ) a
     JOIN
           table_clobs b
      ON   ...

  5. #5
    Join Date
    Aug 2012
    Posts
    13
    The Syntax which you have given is for ANSI SQL.
    I tried to implement that in normal SQL i.e, 10g but its failing.
    Its replicating everything.

    Thanks,
    Kumar

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think you have two choice.
    (1) Use my idea(sample) and develop/modify to complete query adjusting to meet 10g syntax.

    (2) Look for other ways.

  7. #7
    Join Date
    Aug 2012
    Posts
    13
    Ok. Thanks

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Something like:

    Code:
    SELECT *
    FROM  (
       SELECT ... -- all the columns you need
             row_number() over (partition by unique_col1, unique_col2 order by unique_col1, unique_col2) as rn -- this is the new part
       FROM  ... 
         JOIN ...
       WHERE ... 
    ) 
    where rn = 1
    You need to change the partition by and order by inside the window definition in order to list the combination of unique columns you need.

    For more details on the windowing (aka analytical) functions, see the manual: ROW_NUMBER
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  9. #9
    Join Date
    Aug 2012
    Posts
    13
    Hi Shammat,

    That will just partition into groups thats all.

    I want one alternative to fetch the clob columns in the query.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by kumar0828 View Post
    That will just partition into groups thats all.
    No it won't. It will fetch the first row of those groups that you make by providing a proper partition by clause. And that is exactly what DISTINCT is doing as well.

    You will need to give us much more details if you aren't satisfied with the answers.

    What about some sample data and the expected output?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  11. #11
    Join Date
    Aug 2012
    Posts
    13
    HI,

    I got the solution for the above query.
    I am posting it here for further reference.


    SQL> CREATE OR REPLACE
    2 TYPE clob_obj IS OBJECT(
    3 c CLOB,
    4 ORDER MEMBER FUNCTION equals(
    5 p_c clob_obj
    6 )
    7 RETURN NUMBER
    8 )
    9 /

    Type created.

    SQL> CREATE OR REPLACE
    2 TYPE BODY clob_obj
    3 IS
    4 ORDER MEMBER FUNCTION equals(
    5 p_c clob_obj
    6 )
    7 RETURN NUMBER
    8 IS
    9 BEGIN
    10 RETURN CASE
    11 WHEN SELF.c IS NULL AND p_c.c IS NULL THEN 0
    12 ELSE NVL(dbms_lob.compare(SELF.c,p_c.c),1)
    13 END;
    14 END;
    15 END;
    16 /

    Type body created.

    SQL> column c format a15
    SQL> with t as (
    2 select distinct v,
    3 clob_obj(c) c
    4 from tbl
    5 )
    6 select v,
    7 treat(c as clob_obj).c as c
    8 from t
    9 /

    V C
    ---------------
    ANALYST RESEARCH
    CLERK ACCOUNTING
    CLERK RESEARCH
    CLERK SALES
    MANAGER ACCOUNTING
    MANAGER RESEARCH
    MANAGER SALES
    PRESIDENT ACCOUNTING
    SALESMAN SALES

    9 rows selected.

    SQL>

  12. #12
    Join Date
    Sep 2013
    Posts
    1

    Wink solution to bypass the ORA-00932

    I make an example. If I have a table name CLOB_TABLE which contains a CLOB column, and i whant to do a select distinct * from CLOB_TABLE where ...
    I'll have ORA-00932 exception.
    To bypass the error, i can do :
    select * from CLOB_TABLE T1
    where T1.serial in (select distinct T2.serial from CLOB_TABLE T2 where ... ...)

    I hope that it will help you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •