Re: view question

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 27 Sep 1999 11:11:52 +0200
Message-ID: <7sncec$ms4$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 <01bf06c1$c79a5ba0$a40c4a9c_at_MD13457>...
>I'm new to creating views and what you can do.
>
>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 - 11:11:52 CEST