How to select a LONG column in Case Select Query

Question

I have a table which has a column of datatype LONG. If I write a basic query to fetch the value of that LONG colmun, I get the proper results. For eg:

select long_text from fnd_documents_long_text;

This returns proper value.

But If, I try to use a case statement with the LONG column, it gives ORA-00997: Illegal use of LONG datatype error. I am trying to write the below query :

 select case when datatype_id = 2 then 
 ( select long_text from fnd_documents_long_text where  media_id= fd.media_id)    end Text
 from fnd_documents fd where document_id = 715193;

Can you please suggest how can I retrieve the value of LONG column inside a case statement in Select query.

What i really want is that if the datatype_id =1 then, get the short_text from fnd_documents_short_text table else if datatype_id is 2 then get the long_text from fnd_documents_long_text table, where "long_text" is of datatype LONG. These are standard Oracle APPs tables. I am writing my query as below..but it is not working fine:

 select case when datatype_id = 1 then ( select short_text from fnd_documents_short_text where  media_id =fd.media_id)
             when datatype_id = 2 then ( select long_text from fnd_documents_long_text where  media_id =fd.media_id) 
        end Text
   from fnd_documents fd where document_id = 715193 ;

Thanks, abha

Answer 1

There are a lot of restrictions on the LONG data type, which rather limits your options.

As one possible workaround for your scenario, you could create a function that queries the LONG value into a local variable and then returns it as a CLOB, e.g.:

create or replace function get_long_text(
  p_media_id fnd_documents_long_text.media_id%type
)
return clob
as
  l_long long;
begin
  select long_text
  into l_long
  from fnd_documents_long_text
  where media_id = p_media_id;

  return to_clob(l_long);
end;
/

Then in your case expression you can call that function:

select case
         when datatype_id = 1 then (
           select to_clob(short_text)
           from fnd_documents_short_text
           where media_id = fd.media_id
         )
         when datatype_id = 2 then get_long_text(fd.media_id) 
       end as text
from fnd_documents fd
where document_id = 715193;

or with a left outer join to the 'short' table instead of using a subquery:

select case
         when datatype_id = 1 then to_clob(fdst.short_text)
         when datatype_id = 2 then get_long_text(fd.media_id)
        end as text
from fnd_documents fd
left join fnd_documents_short_text fdst
on fdst.media_id = fd.media_id
where fd.document_id = 715193;

db<>fiddle demo.

And from Oracle 12c you can even declare the function as part of a CTE, so you don't need a permanent database object (though you probably do if you'll be doing this often):

-- start of CTE
with function get_long_text(
    p_media_id fnd_documents_long_text.media_id%type
  )
  return clob
  as
    l_long long;
  begin
    select long_text
    into l_long
    from fnd_documents_long_text
    where media_id = p_media_id;

    return to_clob(l_long);
  end;
-- end of CTE
select case
         when datatype_id = 1 then to_clob(fdst.short_text)
         when datatype_id = 2 then get_long_text(fd.media_id)
        end as text
from fnd_documents fd
left join fnd_documents_short_text fdst
on fdst.media_id = fd.media_id
where fd.document_id = 715193;

Answer 2

As proposed here you may workaround using a global temporary table

First insert the relevant part (record) of your table in the temporary table. Than you can select from it as required, because both columns in teh temporary table have same type - CLOB.

You need not to delete from the temp. table, a COMMIT will do it.

Example

create table tab
(short_text varchar2(10),
 long_text LONG,
 datatype_id number);

insert into tab values ('xxx','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 1);     
insert into tab values ('yyy','yyyxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 2); 

This fails:

select datatype_id,
case when datatype_id=1 then long_text
else   short_text    end as txt from tab;

ORA-00932: inconsistent datatypes: expected LONG got CHAR

But copying data in temporary table works

CREATE GLOBAL TEMPORARY TABLE t2
(short_text CLOB,
 long_text CLOB,
 datatype_id number)
ON COMMIT DELETE ROWS;

insert into t2 (short_text,   LONG_TEXT, datatype_id )
select short_text,  to_lob(LONG_TEXT), datatype_id from tab; 

select datatype_id,
case when datatype_id=1 then long_text
else   short_text    end as txt from t2;

DATATYPE_ID TXT                                                                             
----------- --------------------------------------------------------------------------------
          1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx                                                
          2 yyy 

Answer 3

Why not doing just a Join ?

SELECT f_d_l_t.long_text as 'Text'
FROM fnd_documents fd
INNER JOIN 
  fnd_documents_long_text f_d_l_t 
         ON f_d_l_t.media_id= fd.media_id AND fd.datatype_id=2
WHERE 
  document_id = 715193;

If you want to use a case/join query, you can do this :

SELECT 
     OTher_columns,
     MAX(CASE
         WHEN datatype_id = 1 THEN 
               f_d_s_t.short_text
         WHEN datatype_id = 2 THEN 
               f_d_l_t.long_text
     END )as 'Text'
    FROM fnd_documents fd
    LEFT JOIN 
      fnd_documents_long_text f_d_l_t 
             ON f_d_l_t.media_id= fd.media_id
    LEFT JOIN
     fnd_documents_short_text f_d_s_t 
             ON f_d_s_t.media_id= fd.media_id
    WHERE 
      document_id = 715193
    GROUP BY OTher_columns ;