| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Simple SQL Query Question
select decode(emm_freq,0,1,emm_freq) *
decode(rec_freq,0,1,rec_freq) valuefrom (select count(emission_freq)
emm_freq,count(reception_freq) rec_freq from test where
station='STAT2')
VALUE-------- 3
I am selecting from a view which as you see has the
counts of both the columns
I am using decode to replace '0' with '1' ,
without this step the answer would be improper
for example
for stat2 the inner view will fetch values 3 and 0
when multiplied will give 0
to overcome this I replace 0 with 1
Hope this serves the purpose
cozI am anoviceOracle Certifiable DBBS
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Sent: Monday, July 02, 2001 3:30 PM
Subject: RE: Simple SQL Query
Question
For
example for a table like this one,
<SPAN
class=250325508-02072001>
<SPAN
class=250325508-02072001>STATION
EMISSION_FREQ RECEPTION_FREQ ---------- ------------- -------------- STAT1 192.5 193.5 STAT2 192.5 STAT2 194 STAT1 193.5 194.5 STAT2 195
<SPAN
class=250325508-02072001>
<SPAN
class=250325508-02072001>
I
would like to have a value for STAT1 which is equal to 2*2=4 and for
STAT2 equal to 3*1=3
<FONT face=Tahoma
size=2>-----Message d'origine-----De : novicedba
[mailto:novicedba_at_hotmail.com]Envoyé : lundi 2 juillet 2001
11:15À : Multiple recipients of list
ORACLE-LObjet : Re: Simple SQL Query
Question
SELECT
STATION,EMISSION_FREQ,RECEPTION_FREQ,DECODE(SUBSTR(NVL(TO_CHAR(RECEPTION_FREQ),'NONE'),1,1),'N',EMISSION_FREQ*1,EMISSION_FREQ*2)
VALUEFROM TEST;
STATION
EMISSION_FREQ RECEPTION_FREQ
VALUE----------
-------------
--------------
---------STAT1
192.5
193.5
385STAT2
192.5
192.5STAT2 194
194STAT2
193.5
194.5
387STAT2 195
195
<SPAN
class=270522206-02072001> the reception_freq is null (that
would mean there is only emission_freq )
then
<SPAN
class=270522206-02072001> <FONT
face=Arial>value=emission_freq*1
<SPAN
class=270522206-02072001>else
<SPAN
class=270522206-02072001>
value=emission_freq*1
end
if
<SPAN
class=270522206-02072001>
Hope this is
what you wanted
<SPAN
class=270522206-02072001>
cozI am anoviceOracle Certifiable DBBS
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
I've a table
with the following structure and sample data :
<SPAN
class=270522206-02072001>
<SPAN
class=270522206-02072001>Station
Emission_Freq
Reception_Freq
<SPAN
class=270522206-02072001>--------
-----------------
------------------
<SPAN
class=270522206-02072001>STAT1
192.5
193.5
<SPAN
class=270522206-02072001>STAT2
193
<SPAN
class=270522206-02072001>STAT2
194
<SPAN
class=270522206-02072001>STAT2
193.5
194.5
<SPAN
class=270522206-02072001>STAT2
195
<SPAN
class=270522206-02072001>
<SPAN
class=270522206-02072001>
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 ?
<FONT face=Arial color=#ff0000
size=2>------------------------------------------------------------------------------------
@biy @lemu
<A
href="mailto:abiy.alemu_at_criltelecom.com">abiy.alemu_at_criltelecom.com
<SPAN
class=630595213-05042001>Database Administration
Engineer
<IMG height=17
alt="Groupe CRIL TECHNOLOGY" hspace=0
src="http://www.criltechnology.com/images/cril.gif" width=177 align=top
NOSEND="1">
France
Received on Mon Jul 02 2001 - 06:28:07 CDT
 |
 |
