Re: view question - is this possible?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 27 Sep 1999 10:30:46 +0200
Message-ID: <7sna18$n9v$1@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.
>
>
>
>
>
Received on Mon Sep 27 1999 - 03:30:46 CDT