Re: Decode and ranges?

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Tue, 02 Mar 1999 17:12:09 +0800
Message-ID: <36DBAB69.3DC6@bhp.com.au>

JCEdleman wrote:
>
> Hi,
>
> Here is what I am trying to do:
>
> In one SQL statement using Decode ( or another function you might suggest),
> validate a numeric range so . . .
>
> If the row value is between 6 and 10 display a count of records.
>
> Psydo -code:
> Select gender_cd,
> count(decode(round(months_between(birth_dt,sysdate)/12),between 6 and 10, total
> count))) "Age 6-10"
> from members
> where . . . . .
>
> Result would look like this:
>
> Age 6-10 Age 11-15
> Males 5 10
> Females 6 22

Example: To test if x is greater than 6

decode(sign(x-6), 1 , "BIGGER THAN 6", "EQUAL TO OR LESS THAN")

or

decode(sign(x-6), 1 , "BIGGER THAN 6",

		  0, "EQUAL TO 6",
		  -1, "LESS THAN")

etc

HTH --

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"Never wrestle a pig - you both get dirty and the pig likes it..." Received on Tue Mar 02 1999 - 03:12:09 CST