Re: SQL

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/16
Message-ID: <8id9vo$l6f$1@nnrp1.deja.com>#1/1

In article <8icv07$hau$1_at_readme.uio.no>,   n.a.ekern_at_usit.uio.no wrote:
> Hi!
>
> For example:
>
> Create a table month with column month# Number(2) and populate it with
 all =
>
> the months 1 .. 12
>
> Select '2000' || m.month# "Period", p.customer=5Fbudget/12 "Budget"
> From Project p, Month m
> Where m.month# between 3 and 8;
>
> This will list the result of customer=5Fbudget/12 six times, one for
 each m=
> onth march .. august.
>
> Nj=E5l
>
>

This can also be done without creating a new table, using PL/SQL and a numeric 'for' loop. Presuming you also have a customer_number in the project table:

declare

   cursor get_budget is
   select customer_number, customer_budget/12 budget    from project
   order by customer_number;
begin

   for budget_rec in get_budget
   loop

          dbms_output.put_line('Customer: '||budget_rec.customer_number);

          for month in 1 .. 12 loop
              if month < 10 then
              dbms_output.put_line('20000'||month||'
'||to_char(budget_rec.budget, '$999,999'));
              else
              dbms_output.put_line('2000'||month||'
'||to_char(budget_rec.budget, '$999,999'));
              end if;
          end loop;

    end loop;
end;
/

To change the number of periods displayed change the numeric parameters in the second 'for' loop from 1 .. 12 to <starting month number> .. <ending month number>. For example:

for month in 3 .. 6 loop

   ...

will output periods 3 through 6.

David Fitzjarrell
Oracle Certified DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 16 2000 - 00:00:00 CDT