Distinct select with CLOB field
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Distinct select with CLOB field

Hybrid View

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    Distinct select with CLOB field

    Can anyone tell me why i got the following error:

    ORA-00932: inconsistent datatypes: expected - got CLOB

    when i try to do a

    Select Distinct(id), text
    From paragraphs
    where id IN
    (select paragraph_id
    from menuitems
    where section_id = 10);

    Is there some way to return the CLOB field with distinct IDs?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Is this what you want?
    Code:
    select p.id, p.text from paragraphs p
    where p.id in
    (select distinct m.paragraph_id
     from menuitems m
     and m.section_id = 10);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2003
    Posts
    90
    you are correct that your query would work and i realize that, but i want to know why you can't have select the distinct(id) and the clob field in the same select?

    Or how to do it without getting the inconsistent datatypes error.
    Last edited by luciffer; 03-17-2005 at 10:45 AM.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by luciffer
    you are correct that your query would work and i realize that, but i want to know why you can't have select the distinct(id) and the clob field in the same select?

    Or how to do it without getting the inconsistent datatypes error.
    It is bacuse DISTINCT is an operator that is applicable *to the whole row* in a resultset, not to a particular *column*!

    Your query was:
    Code:
    Select Distinct(id), text from ...
    but that doesn't mean that the distinct was ment only for the ID column, it rather said: give me the distinct combination of all id+text values from my table. And since clobs can not be (directly) compared to each other oracle returned ORA-00932: inconsistent datatypes error.

    DISTINCT is an operator, not a function, and is allways applied to all the columns in the query's columnb list. That's also the reason why it has to be specified before the column list. Try changing your query to
    Code:
    Select text, Distinct(id) from ...
    and you'll get error due to invalid syntax.

    BTW, it is rather unfortunate that the SQL parser didn't reject your query for invalid syntax - I would expect it to do so. In my opinion, it should be treatened as invalid syntax because there is no space between DISTINCT and the left patrenthesis in '(id)'. Remember, DISTINCT is not a function, so those parenthesis around 'id' actualy have no meaning at all, so there should be a space between the DISTINCT and the left parenthesis.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2009
    Posts
    2

    DISTINCT On Clob

    CAn some one explain how to get the distinct records,when a row as clob memeber.



    Quote Originally Posted by jmodic View Post
    It is bacuse DISTINCT is an operator that is applicable *to the whole row* in a resultset, not to a particular *column*!

    Your query was:
    Code:
    Select Distinct(id), text from ...
    but that doesn't mean that the distinct was ment only for the ID column, it rather said: give me the distinct combination of all id+text values from my table. And since clobs can not be (directly) compared to each other oracle returned ORA-00932: inconsistent datatypes error.

    DISTINCT is an operator, not a function, and is allways applied to all the columns in the query's columnb list. That's also the reason why it has to be specified before the column list. Try changing your query to
    Code:
    Select text, Distinct(id) from ...
    and you'll get error due to invalid syntax.

    BTW, it is rather unfortunate that the SQL parser didn't reject your query for invalid syntax - I would expect it to do so. In my opinion, it should be treatened as invalid syntax because there is no space between DISTINCT and the left patrenthesis in '(id)'. Remember, DISTINCT is not a function, so those parenthesis around 'id' actualy have no meaning at all, so there should be a space between the DISTINCT and the left parenthesis.

  6. #6
    Join Date
    Dec 2003
    Posts
    90
    Thanks for the explanation, that clears it all up.

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