Re: Can I do this with a view?

From: Geoff Cliff <Geoff.Cliff_at_jif.u-net.com>
Date: 1996/08/15
Message-ID: <4v00vl$d6k_at_nuntius.u-net.net>#1/1


Donna Kray <kray.donna_at_mlink.motors.ge.com> wrote:

>Tom Alborough <toma_at_micrologic.com> wrote:
>>Hi,
>>
>>I have Table Unit_Day_Shift:
>>
>>Unit_ID Day_Date Shift_SN Run_Hours
>>=================================================
>>123 1-Jan 1 2
>>123 1-Jan 2 4
>>123 1-jan 3 0
>>345 1-jan 1 0
>>345 1-jan 2 2
>>..
>>123 2-jan ...
>>
>>
>>I need Table Unit_Day:
>>
>>Unit_ID Day_Date Shift_1_Hours Shift_2_Hours Shift_3_Hours
>>=====================================================================
>>123 1-Jan 2 4 0
>>345 1-Jan ...
>>
>>

 [...]
>>
>>Is this doable with a view?
>>
>>Thanks,
>>Tom
>============================================================
>Assuming Unit_ID, Day_Date, SHift_SN is the key :
 

>select Unit_ID, Day_Date,
> decode(shift_sn, 1, run_hours, 0) "Shift_1_Hours",
> decode(shift_sn, 2, run_hours, 0) "Shift_2_Hours",
> decode(shift_sn, 3, run_hours, 0) "Shift_3_Hours"
>from Unit_Day_Shift;
 

>Since you seeem to have accounted for all 3 shifts, you don't need the
>",0" argument in the decode function.
 

>I wasn't sure what you need for your other example, but maybe this will
>get the gears moving.
 

>Hope it helps,
 

>DL Kray

Yes it is doable with either a view or 7.2 onwards, an Inline View.

Of course the real answer is:

select	Unit_ID, 
	Day_Date, 
	SUM( decode( shift_sn, 1, run_hours, 0 ) ) "Shift_1_Hours",
	SUM( decode( shift_sn, 2, run_hours, 0 ) ) "Shift_2_Hours",
	SUM( decode( shift_sn, 3, run_hours, 0 ) ) "Shift_3_Hours"
   from 	Unit_Day_Shift
GROUP BY Unit_ID, 
	Day_Date;

Just thought I would throw my 2p in. This was needed because you only want one row returned per Unit_ID/Day_Date value. You could also use MIN or MAX. The reason being is:
1. you need to use a group function to return a value 2. the DECODE will only allow one value to be returned (other than 0)

   for a shift_sn for a Unit_Id/Day_Date SET.

cheers
jif/geoff/goff

#include <standard_disclaimer.h>
The thoughts are someone elses, just the words are mine Jif/Geoff/Goff (an aussie threw & thrwe) Received on Thu Aug 15 1996 - 00:00:00 CEST

Original text of this message