DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Decode Function issue

  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Decode Function issue

    Hi all,

    i want compare two rows in a table. so i used decode function to compare the values, whther its matched or not.

    One of my table column has Long data type.

    Decode(a.col1,b.col1,'Match','NotMatch')
    col1 - is long data type.

    when i use long data type, i got below error,
    is there anyway to rectify this error.
    SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
    00932. 00000 - "inconsistent datatypes: expected %s got %s"

    Thanks in advance.
    Regards,
    Santhosh

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    LONG columns are pretty restictive, one of the restrictions is that they cannot be used on built-in functions.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Try using the to_clob() function, and then you can also use all of the methods of dbms_lob. As in:

    select dbms_lob.compare(to_clob(tab_a.mycolumn), to_clob(tab_b.mycolumn))
    from table_a
    inner join table_b
    on table_a.pkey = table_b.pkey
    where table_a.pkey.123456;

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