Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, gaia.

Asked: October 04, 2016 - 1:29 pm UTC

Last updated: October 06, 2016 - 12:50 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi Tom!

I've got the table:
create table example (id number, image varchar2(10));


with these two rows:
insert into example (id, image)
  values (25, 'pippo');

insert into example (id, image)
  values (32, 'pluto');


And this is my query:
select 
  max(case when id=25 then image end) as col1,
  max(case when id=32 then image end) as col2
from example;


Well, i need a query that would do the same even if the column "image" is of BLOB data type...

I've searched uselessly the net and you're my last option!

Many thanks for any advice you could give me.

Gaia

and Connor said...


SQL> drop table example purge;

Table dropped.

SQL>
SQL> create table example (id number, image varchar2(10));

Table created.

SQL>
SQL> insert into example (id, image)  values (25, 'pippo');

1 row created.

SQL> insert into example (id, image)  values (32, 'pluto');

1 row created.

SQL>
SQL> select a1, a2
  2  from
  3    ( select image a1 from example where id = 25 ),
  4    ( select image a2 from example where id = 32 );

A1                             A2
------------------------------ ------------------------------
pippo                          pluto

SQL>
SQL>
SQL> drop table example purge;

Table dropped.

SQL>
SQL> create table example (id number, image blob);

Table created.

SQL>
SQL> insert into example (id, image)  values (25, hextoraw('FFFF'));

1 row created.

SQL> insert into example (id, image)  values (32, hextoraw('AAAA'));

1 row created.

SQL>
SQL> select a1, a2
  2  from
  3    ( select image a1 from example where id = 25 ),
  4    ( select image a2 from example where id = 32 );

A1                             A2
------------------------------ ------------------------------
FFFF                           AAAA

SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not performant

gaia tosi, October 05, 2016 - 7:48 am UTC

Hi Connor, thanks for your answer! At the moment it's the only way i came up with that works...

The problem is that i need 28 images and i'm wondering if there's another way not to query the table 28 time separately..

Many thanks
Connor McDonald
October 06, 2016 - 12:50 am UTC

Perhaps do it two parts - grab the subset of data first, and then attack that

with subset as
  ( select /*+ materialize */ * from my_table where id in ( 1,2,3,4..... ) )
select a1,a2
from 
  ( select a1 from subset where id = 1 ),
  ( select a2 from subset where id = 1 ),
  ( select a3 from subset where id = 1 ),
  ( select a4 from subset where id = 1 )


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here