Re: Help me design a table

From: Lothar Armbrüster <lothar.armbruester_at_t-online.de>
Date: Mon, 19 Mar 2007 22:36:16 +0100
Message-ID: <87648wgblb.fsf@prometeus.none.local>

"Anoop" <anoopkumarv_at_gmail.com> writes:

> I need to design a table with the following columns. It is a table
> that will be read by a 3rd party product which allows little
> customization. The columns would be something like these:
>
> id - primary key
> bank - non null (varchar(100))
> access - non null (varchar(100))
>
> The requirement is that every "id" can have multiple "bank"'s
> associated with it and each of those banks can have an (a single)
> "access" associated with it.
> In order to satisfy this requirement, I suggested that we have have
> the "bank" column contain a comma separated list - and similarly - the
> "access" column will have comma separated values. And example of a row
> is like this:
>

[...]

Hello Anoop,

do I understand it correctly that your strange application just reads your table? And does it read just one row?

Then you could do something like this:

create table base_tab
(id number(10) not null,
 bank varchar2(10) not null,
 acc varchar2(10) not null,
 constraint pk_base_tab primary key (id,bank));

Then write a table function that gives the csv data for a given id.

create type csv_type is object(bank varchar2(100), acc varchar2(100)); create type csv_tab is table of csv_type;

create function bank_csv(bid in number) return csv_tab pipelined as banks varchar2(100);
accs varchar2(100);
o_rec csv_type;
begin

   banks := null;
   accs := null;
   for b_rec in (select

                    bank,
                    acc
                 from
                    base_tab
                 where
                    id=bid
                 order by
                    bank) loop
      if banks is null then
         banks := b_rec.bank;
      else
         banks := banks||','||b_rec.bank;
      end if;
     
      if accs is null then
         accs := b_rec.acc;
      else
         accs := accs||','||b_rec.acc;
      end if;

   end loop;
   o_rec := csv_type(bid,banks,accs);
   pipe row(o_rec);
end bank_csv;

And finally build a view that wraps the table function.

create or replace view csv_view as
select t.*
from

   (select distinct id from base_tab) b,    table(bank_csv(b.id)) t
where

   t.id=b.id;

This way you can update and insert data into some simple table and your strange application has something to read csv from.

This is not very performant but seems to work.

Hope that helps,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |

Received on Mon Mar 19 2007 - 16:36:16 CDT