Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: view question - is this possible?

Re: view question - is this possible?

From: Didier LENQUETTE <didier.lenquette_at_steria.fr>
Date: Thu, 30 Sep 1999 05:23:43 GMT
Message-ID: <ztCI3.1951$S2.18018418@France.EU.net>

Michel Cadot <micadot_at_netcourrier.com> a écrit dans le message : 7sna18$n9v$1_at_oceanite.cybercable.fr...
> Is that what you want?
>
> v734>create table my_table (custom_id number, the_date date, consump
number);
> v734>insert into my_table values (123, to_date('01/01', 'MM/DD'), 10);
> v734>insert into my_table values (123, to_date('02/01', 'MM/DD'), 12);
> v734>insert into my_table values (123, to_date('03/01', 'MM/DD'), 11);
> v734>insert into my_table values (123, to_date('03/30', 'MM/DD'), 13);
> v734>create or replace package my_pkg is
> 2 function f (cid number) return varchar;
> 3 pragma restrict_references (f, WNDS, RNPS, WNPS);
> 4 end my_pkg;
> 5 /
>
> Package created.
> v734>create or replace package body my_pkg is
> 2 function f (cid number) return varchar is
> 3 result varchar2(32000) := '';
> 4 cursor curs is
> 5 select to_char(the_date, 'MonDD') the_date, consump
> 6 from my_table where custom_id = cid;
> 7 first boolean := true;
> 8 begin
> 9 for rec in curs loop
> 10 if first then
> 11 result := rec.the_date||' '||rec.consump;
> 12 first := false;
> 13 else
> 14 result := result||' '||rec.the_date||' '||rec.consump;
> 15 end if;
> 16 end loop;
> 17 return result;
> 18 end;
> 19 end my_pkg;
> 20 /
> v734>create or replace view my_view as
> 2 select distinct custom_id, my_pkg.f(custom_id) result from my_table;
> v734>select custom_id, substr(result,1,70) from my_view;
>
> CUSTOM_ID SUBSTR(RESULT,1,70)
> ---------- ---------------------------------------------------------------



> -
> 123 Jan1 10 Fev1 12 Mar1 11 Mar30 13
>
> 1 row selected.
>
>
> L Lee a écrit dans le message <01bf06c8$2b22f3c0$a40c4a9c_at_MD13457>...
> >
> >The problem I'm having is how to create a view using the following table:
> >These are the records for a single service ID having multiple consumption
> >records like the following. The dates are not consistently one month
> >apart.
> > cust id date consumption
> > 123 Jan1 10
> > 123 Feb1 12
> > 123 Mar1 11
> > 123 Mar30 13
> >
> >The users want to see all the discrete reads. So we want to create a
view
> >for the users to show the records like this:
> >cust id 1stdate consump 2nddate consump 3rddate consump 4thdate
consump
> >123 Jan1 10 Feb1 12 Mar1 11 Mar30 13
> >
> >Can anyone help me with how this could be done?
> >Thank you in advance.
> >
> >
> >

Ta solution avec la fonction est pas mal. Quel est le problème ?

- voir les données comme le renvoi la fonction
- faire des requetes sur le jeu de resultats (comme le donne la fonction)
- créer une vue avec ce format

A+ Received on Thu Sep 30 1999 - 00:23:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US