Hi ALEMU Abiy,
Try out this :
select count(*)*2 FREQC
from sample1
where station='&ST'
and emission_freq is not null
and reception_freq is not null
having count(*) > 0
union
select count(*)*1 FREQC
from sample1
where station='&ST'
and ( emission_freq is not null
and reception_freq is null)
or ( emission_freq is null
and reception_freq is not null)
having count(*) > 0
/
Result:
When ST=STAT1 Result = 4
When ST=STAT2 Result = 3
As reqrd by u.
Hope this will help u.
Bye
- Harsh
-----Original Message-----
Sent: Monday, July 02, 2001 2:20 PM
To: Multiple recipients of list ORACLE-L
Well, then decode would be the only option, as suggested by other members.
Case is not supported.
rgds
amar
-----Original Message-----
Sent: Monday, July 02, 2001 2:00 PM
To: Multiple recipients of list ORACLE-L
I'm using Oracle 7.3.4, so is it possible to use CASE statements ?
-----Message d'origine-----
De : Amar Kumar Padhi [mailto:[EMAIL PROTECTED]]
Envoyé : lundi 2 juillet 2001 10:57
À : Multiple recipients of list ORACLE-L
Objet : RE: Simple SQL Query Question
select count((case when (nvl(emission_freq, 0) != 0 and nvl(reception_freq,
0) != 0)
then 1 else null end)) * 2 +
count((case when (nvl(emission_freq, 0) != 0 and nvl(reception_freq,
0) != 0)
then null else 1 end)) * 1 as "total"
from am39
where station = 'xxxx';
rgds
amar
-----Original Message-----
Sent: Monday, July 02, 2001 11:30 AM
To: Multiple recipients of list ORACLE-L
I've a table with the following structure and sample data :
Station Emission_Freq Reception_Freq
-------- ----------------- ------------------
STAT1 192.5 193.5
STAT2 193
STAT2 194
STAT2 193.5 194.5
STAT2 195
And I would like to select the number of frequencies, sort of count(*), for
a given station and multiply that value by 2 if the station has Emission
frequency and reception frequency and multiply by one if the station has
only the emission frequency or only the reception frequency. I don't know
how to use a condition in SQL. I'm sure that it's possible to do it but I
don't know how. Can anybody help me ?
----------------------------------------------------------------------------
--------
@biy @lemu
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Database Administration Engineer
Groupe CRIL TECHNOLOGY <http://www.criltechnology.com/images/cril.gif>
France
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harsh Agrawal
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).