Re: Sql request

From: Thomas Kellerer <OTPXDAJCSJVU_at_spammotel.com>
Date: Mon, 30 Nov 2009 22:37:04 +0100
Message-ID: <7nis7sF3lj89mU1_at_mid.individual.net>

ddf wrote on 30.11.2009 22:26:
> Yet it does have something Oracle-specfic: the ability, through
> SQL*Plus, to suppress repeating output, and analytic functions to
> compute the sum in-line so the stock_order table isn't necessary
> (columns are renamed, as necessary, to remove reserved words):
>

You don't need SQL*Plus for the formatting, analytical functions are enough

But you have a good point about the stock_order table not being necessary:

select case

          when lag(order_code) over (partition by order_code order by delivery_sequence) = order_code then ''
          else order_code
       end as display_code, 
       case
          when lag(order_code) over (partition by order_code order by delivery_sequence) = order_code then null
          else sum(amount) over (partition by order_code)
       end as order_amount, 
       delivery_sequence, 
       delivery_amount

from stock_delivery
order by order_code, delivery_sequence

Runs on PostgreSQL and DB2 just as well.

Thomas Received on Mon Nov 30 2009 - 15:37:04 CST