view for two tables (with blob)

From: Dirk Schwarzmann <Dirk.Schwarzmann_at_gmx.de>
Date: 19 Feb 2003 15:24:37 GMT
Message-ID: <b307jl$1hark8$1@ID-93128.news.dfncis.de>

Hi,

I am not a SQL pro, so forgive me if this is a stupid question or there is a much better approach that might circumvent my problem.

I have to create a view in 8.1.7.0 that combines the entries of some tables (to later define some triggers on it). The tables look similar to these examples:

create table my_blob (
  id_blob number primary key,
  blobfile blob not null,
  blobname varchar2 (200) not null,
  blobsuffix varchar2 (3) not null
);

create table general_info (
  id_gi number primary key,
  description varchar2 (200),
  web_url varchar2 (200),

  id_myblob,                 -- optionally link to a blob
	constraint FK_BLOB_INFO

    foreign key (id_myblob)
    references my_blob(id_blob),

        constraint check_NotNull
  check (
    description is not null or
    web_url is not null or
    id_myblob is not null
  )
);

My view should give all rows of table general_info plus the ones of my_blob. Obviously I cannot just do a:

create or replace view info_and_blob as
  select * from general_info a, my_blob b   where a.id_myblob = b.id_blob;

since this would omit all rows in general_info that have no blob attached.

So I tried a union:

create or replace view info_and_blob as
select * from
(

• Get only rows with a blob attached select a.id_gi, a.description, a.web_url, a.id_myblob, b.blobfile, b.blobname, b.blobsuffix from general_info a, my_blob b where a.id_myblob = b.id_blob union
• Get only rows with no blob attached select a.id_gi, a.description, a.web_url, a.id_myblob, emptyblob() as blobfile, -- doesn't work! '' as blobname, '' as blobsuffix from general_info a where a.id_myblob is null
  );
  

As you might see, my problem is how I can fake a blob in the second select stmt to satisfy the table structure integrity needed by the union statement.

Maybe there is a much better way to get around this - I've heard of joins but have not understood the principals behind that technique so I'm not sure if joins would be a proper solution.

Any help is greatly appreciated!

Dirk Received on Wed Feb 19 2003 - 09:24:37 CST