Re: view for two tables (with blob)

Hi:

If I have understood your question, you want all the colums of the table general_info and all the columns of the table my_blob, and the problem you have is than, when a id_gi has not a id_blob asociated, when you join the two tables, these records don´t appear.

You can probe the next:

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

The option (+) will do these records appear.

I hope this is usefull to you.

Regards,

                                 Isa

"Dirk Schwarzmann" <Dirk.Schwarzmann_at_gmx.de> escribió en el mensaje news:b307jl$1hark8$1_at_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 - 11:28:20 CST