Re: [Oracle] Sql request

Ben Finney wrote on 28.11.2009 00:12:
> pif_orders=> SELECT DISTINCT
> order_code,
> order_amount,
> delivery_sequence,
> delivery_amount
> FROM
> (
> SELECT
> code AS order_code,
> amount AS order_amount
> FROM stock_order
> ) AS o
> NATURAL JOIN
> (
> SELECT
> order_code,
> sequence AS delivery_sequence,
> amount AS delivery_amount
> FROM stock_delivery
> ) AS d
> ORDER BY order_code, delivery_sequence;

This can be done a lot easier including the surpressing of repeating values (works in Postgres and Oracle):

select case

          when lag(o.code) over (partition by o.code order by d.sequence) = o.code then ''
          else o.code
       end, 
       case
          when lag(o.code) over (partition by o.code order by d.sequence) = o.code then null
          else o.amount
       end as order_amount, 
       d.sequence as delivery_sequence, 
       d.amount as delivery_amount

   join stock_delivery d on o.code = d.order_code order by o.code, d.sequence;

And even if the "blank" stuff should be in there I don't understand the complicated sub-query you were using :)

Thomas Received on Fri Nov 27 2009 - 17:25:54 CST