Re: Help me design a table

On Mar 19, 5:36 pm, lothar.armbrues..._at_t-online.de (Lothar Armbrüster) wrote:
> "Anoop" <anoopkum..._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.armbrues..._at_t-online.de
> Hauptstr. 26 |
> 65346 Eltville |

Thank you so much for the replies and help... But I think this solution (STRAGG) really fits my requirement:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336 Received on Sun Mar 25 2007 - 01:13:04 CDT