Re: Crosstab Query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Sep 1998 13:12:19 GMT
Message-ID: <36108a14.2820936@192.86.155.100>

A copy of this was sent to speng_at_ncs.com.sg (if that email address didn't require changing) On Mon, 28 Sep 1998 07:49:17 GMT, you wrote:

>I have a table with the columns as shown below:
>
><My_table>
>unemp varchar2(1)
>isdereg varchar2(1)
>exp date
>rereg_dte date
>renel_dte date
>
>How to i select a crosstab query that will calculate total no. in NewReg where
>unemp='0' and so on.
>
>unemp NewReg Re_Reg Renew_Reg
>0 total total total
>1
>2
>3
>4
>5
>
>where the conditon for:
>NewReg => isdereg ='N' and exp > sysdate
> rereg_dte and renl_dte is null
>
>Re_reg => isdereg='N' and exp > sysdate
> rereg_dte is not null
>
>Renew_reg => isdereg='N' and exp > sysdate
> renl_dte is not null

since all three have the criteria:

isdereg = 'N' and exp > sysdate

we can put that in the where clause. To get the other stuff transposed, we will use decode and sum:

select unemp,

   sum( decode( rereg_dte, NULL, decode( renl_dte, NULL, 1, 0 ), 0 ) ) 'NewReg',
   sum( decode( rereg_dte, NULL, 0, 1 ) 'Re-Reg',
   sum( decode( renl_dte,  NULL, 0, 1 ) 'Renew-Reg'

from T
where isdereg = 'N' and exp > sysdate
group by unemp
/

the decode( rereg_dte, NULL, decode( renl_dte, NULL, 1, 0 ), 0 ) says:

if rereg_dte is NULL then

   if renl_dte is NULL then

       return 1;
   else

       return 0;
   end if;
else

   return 0;
end if;

so, its a little function that returns 1 if rereg_dte AND renl_dte are both NULL, 0 otherwise.

Likewise for decode( rereg_dte, NULL, 0, 1 ). It says:

if ( rereg_dte is NULL ) then

   return 0;
else

   return 1;
end if;

we sum the zeroes and one's and get the answer....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 28 1998 - 08:12:19 CDT