RE: Simple SQL Question

2003-12-18 Thread Kevin Toepke 
select a.emp,
 b.ValueA
 c.ValueB
>From base_table A
RIGHT OUTER JOIN child_Table1 B ON b.emp = a.emp
RIGHT OUTER JOIN child_Table2 C ON c.emp = a.emp

-Original Message-
Sent: Thursday, December 18, 2003 2:20 PM
To: Multiple recipients of list ORACLE-L


Hello:

I'm trying to figure out the new 9i outer joins.
I can get a single table outer join working without any issues. But seem to 
keep getting errors when trying to do a two table outer join.  I know it is 
just something with my syntax.  Could anyone provide a quick sample, thanks 
in advance.


A:=  Base Table
B:=  Child Table 1
B:=  Child Table 2

select a.emp,
 b.ValueA
 c.ValueB
>From base_table A outer join child_Table1 B
on A.emp=B.Emp...


I know the old way of
select a.emp,
 b.ValueA
 c.ValueB
>From base_table A, child_Table1 B, child_Table2 C
where A.emp=B.Emp(+)
and  A.emp=C.Emp(+)

_
Grab our best dial-up Internet access offer: 6 months @$9.95/month.  
http://join.msn.com/?page=dept/dialup

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Wade
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Toepke
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

RE: simple SQL

2001-12-07 Thread Ball, Terry 

create table test1 tablespace test1 as select from test2;

tablespace goes right after the new table_name.

Terrry

-Original Message-
Sent: Friday, December 07, 2001 2:40 PM
To: Multiple recipients of list ORACLE-L




Hi Gurus,

Iam creating table from one tablespace to another tablespace, Iam getting 
error!!!

create table test1 as select * from test2 tablespace TEST1;

What is the problem with statement??

Thanks
Raghu.

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Raghu Kota
  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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ball, Terry
  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).

Re: simple SQL

2001-12-07 Thread Jared . Still 


You have the TABLESPACE clause in the wrong place.

create table
  test1
  tablespace TEST1
as
select *
from test2;

Jared





   
 
"Raghu Kota"   
 

mail.com>cc:   
 
Sent by: Subject: simple SQL   
 
[EMAIL PROTECTED] 
 
om 
 
   
 
   
 
12/07/01 12:40 
 
PM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 






Hi Gurus,

Iam creating table from one tablespace to another tablespace, Iam getting
error!!!

create table test1 as select * from test2 tablespace TEST1;

What is the problem with statement??

Thanks
Raghu.

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raghu Kota
  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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  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).

RE: simple SQL

2001-12-07 Thread Kevin Lange 

create table test1 tablespace TEST1 as select * from test2;

Look at the syntax diagrams in the manuals.  This is explicitly shown.

-Original Message-
Sent: Friday, December 07, 2001 2:40 PM
To: Multiple recipients of list ORACLE-L




Hi Gurus,

Iam creating table from one tablespace to another tablespace, Iam getting 
error!!!

create table test1 as select * from test2 tablespace TEST1;
What is the problem with statement??

Thanks
Raghu.

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Raghu Kota
  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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  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).

Re: simple SQL

2001-12-07 Thread Kent Wayson 


The "tablespace TEST1" clause needs to come before the "as select ..." clause.


Kent


At 12:40 PM 12/7/01 -0800, you wrote:
>
>
>Hi Gurus,
>
>Iam creating table from one tablespace to another tablespace, Iam getting 
>error!!!
>
>create table test1 as select * from test2 tablespace TEST1;
>
>What is the problem with statement??
>
>Thanks
>Raghu.
>
>_
>Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Raghu Kota
>  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).
>
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kent Wayson
  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).

RE: simple sql problem

2001-12-04 Thread Reardon, Bruce (CALBBAY) 

After testing prompted by Jared's reply, you can use 1 of the 2 alternatives
from Windows.

That is, under windows the following works:
echo select * from dual; | sqlplus scott/tiger@abcd

I often use this type of syntax under NT to (say) log the time as below, but
had never thought to try it with sqlplus.
eg time example
C:\>echo.|time|find "current" /i
The current time is: 10:54:24.94

Hope this helps someone,
Bruce Reardon

-Original Message-
Sent: Wednesday, 5 December 2001 3:25

As Scott said, in Unix you can use Input Redirection.   If you are in
windows, you could always build the command as a .sql file and use the

sqlplus -s scott/tiger @myfile.sql

Command.

-Original Message-
Sent: Tuesday, December 04, 2001 10:01 AM

>From the unix command line you can run:

sqlplus -s scott/tiger << EOF
select * from emp;
EOF


Viraj Luthra wrote:
> 
> Hello all,
> 
> Why cannot I do :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> or sqlplus -s scott/tiger "select * from emp;"
> 
> Both the cases I get :-
> 
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }
> -s for silent mode and -? to obtain version number
> 
> What is wrong above? I need to run the query from command line. How do I
modify this :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> Rgds,
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  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).

RE: simple sql problem

2001-12-04 Thread Kevin Lange 

As Scott said, in Unix you can use Input Redirection.   If you are in
windows, you could always build the command as a .sql file and use the

sqlplus -s scott/tiger @myfile.sql

Command.

-Original Message-
Sent: Tuesday, December 04, 2001 10:01 AM
To: Multiple recipients of list ORACLE-L


>From the unix command line you can run:

sqlplus -s scott/tiger << EOF
select * from emp;
EOF



Viraj Luthra wrote:
> 
> Hello all,
> 
> Why cannot I do :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> or sqlplus -s scott/tiger "select * from emp;"
> 
> Both the cases I get :-
> 
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }
> -s for silent mode and -? to obtain version number
> 
> What is wrong above? I need to run the query from command line. How do I
modify this :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> Rgds,
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Viraj Luthra
>   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).

_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Shafer
  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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  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).

Re: simple sql problem

2001-12-04 Thread Scott Shafer 

>From the unix command line you can run:

sqlplus -s scott/tiger << EOF
select * from emp;
EOF



Viraj Luthra wrote:
> 
> Hello all,
> 
> Why cannot I do :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> or sqlplus -s scott/tiger "select * from emp;"
> 
> Both the cases I get :-
> 
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }
> -s for silent mode and -? to obtain version number
> 
> What is wrong above? I need to run the query from command line. How do I modify this 
>:-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> Rgds,
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Viraj Luthra
>   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).

_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Shafer
  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).

RE: simple sql problem

2001-12-04 Thread DENNIS WILLIAMS 

Viraj - Your SQL isn't terminated. You need to end your SQL with either a
semicolon or a "/" on the next line.

Jared - Thanks for passing along this technique.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, December 04, 2001 12:35 AM
To: Multiple recipients of list ORACLE-L


 Jared,

Using the 2nd option:-

sqlplus system/manager@orcl815 <   234567  Disconnected from Oracle8i Release
8.1.5.0.0 - Production
With the Java option

PL/SQL Release 8.1.5.0.0 - Production




But if I use the 1st option :-

eg

echo "SELECT a.username,  substr(sql_text,1,100),
substr(sql_text,101,250),substr(sql_text,601,250),substr(sql_text,851,250),
substr(sql_text,1101,250)
FROM dba_users a, v$session, v$sqlarea where parsing_user_id=user_id AND
address=sql_address(+)
and sid=15;"|sqlplus system/manager

I get this error:-

SQL>   234  FROM dba_users a, v, v where parsing_user_id=user_id AND
address=sql_address(+)  
 *

ERROR at line 3:

ORA-00942: table or view does not exist

 


But if I execute the query on its own, I get proper results.

So in both cases I am not able to get results.

What am I doing wrong?

Rgds,
--

On Mon, 3 Dec 2001 21:38:36   
 Jared Still wrote:
>
>The way you're doing it sqlplus is trying to parse the SQL 
>as a command line argument, which will not work.
>
>Try these:
>
>1.
>
>echo "select * from dual;" | sqlplus scott/tiger
>
>2.
>
>sqlplus scott/tiger EOF
>
>Jared
>
>
>On Monday 03 December 2001 21:10, Viraj Luthra wrote:
>> Hello all,
>>
>> Why cannot I do :-
>>
>> sqlplus -s scott/tiger select * from emp;
>>
>> or sqlplus -s scott/tiger "select * from emp;"
>>
>> Both the cases I get :-
>>
>> Usage: SQLPLUS [] [[/] [@]]
>>[@ [] [] ...]
>> where  ::= { -s | -? }
>> -s for silent mode and -? to obtain version number
>>
>>
>> What is wrong above? I need to run the query from command line. How do I
>> modify this :-
>>
>> sqlplus -s scott/tiger select * from emp;
>>
>>
>> Rgds,
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  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).

Re: simple sql problem

2001-12-03 Thread Jared Still 


The way you're doing it sqlplus is trying to parse the SQL 
as a command line argument, which will not work.

Try these:

1.

echo "select * from dual;" | sqlplus scott/tiger

2.

sqlplus scott/tiger < Hello all,
>
> Why cannot I do :-
>
> sqlplus -s scott/tiger select * from emp;
>
> or sqlplus -s scott/tiger "select * from emp;"
>
> Both the cases I get :-
>
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }
> -s for silent mode and -? to obtain version number
>
>
> What is wrong above? I need to run the query from command line. How do I
> modify this :-
>
> sqlplus -s scott/tiger select * from emp;
>
>
> Rgds,
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  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).

RE: simple sql problem

2001-12-03 Thread Ross Collado 

I don't believe SQLPlus will let you run a SQL statement in the command line
itself.  As you can see in Usage:, it doesn't have an option for this.
However, if you insist on running SQL statements in the command line itself,
you can probably do something like (only in Unix):

sqlplus -s scott/tiger << !
select * from emp;
!

or 

save your select * from emp; to a sql file and do :
sqlplus -s scott/tiger @myfile.sql

hth
Ross

> -Original Message-
> From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 4 December 2001 16:11
> To: Multiple recipients of list ORACLE-L
> Subject: simple sql problem
> 
> 
> Hello all,
> 
> Why cannot I do :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> or sqlplus -s scott/tiger "select * from emp;"
> 
> Both the cases I get :-
> 
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }   
> -s for silent mode and -? to obtain version number   
> 
> 
> What is wrong above? I need to run the query from command 
> line. How do I modify this :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> 
> Rgds,
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Viraj Luthra
>   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).
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ross Collado
  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).

Re: simple sql problem

2001-12-03 Thread hemantchitale 

You can't have the SQL statements on the command line.  You must put them
in
a script file (eg myscript.sql) and execute it with the @ sign.
Thus,
sqlplus -s scott/tiger @myscript[note the SPACE between the "tiger" and
the "@"].
Hemant



"Viraj Luthra" <[EMAIL PROTECTED]>  04/12/2001 01:10 PM
Sent by: [EMAIL PROTECTED]

Please respond to ORACLE-L
   

 To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>   

 cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group)

 Subject: simple sql problem   

   

   

   






Hello all,

Why cannot I do :-

sqlplus -s scott/tiger select * from emp;

or sqlplus -s scott/tiger "select * from emp;"

Both the cases I get :-

Usage: SQLPLUS [] [[/] [@]]
   [@ [] [] ...]
where  ::= { -s | -? }
-s for silent mode and -? to obtain version number


What is wrong above? I need to run the query from command line. How do I
modify this :-

sqlplus -s scott/tiger select * from emp;


Rgds,



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
  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).




[This e-mail is confidential and may also be privileged. If you are not the
intended recipient, please delete it and notify us immediately; you should
not copy or use it for any purpose, nor disclose its contents to any other
person. Thank you.]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  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).

RE: simple sql problem

2001-07-31 Thread Pulikkol Kumar 


Hi
 This will work out 

SELECT  DECODE(SIGN(x+y-1) , 1,LPAD(TO_CHAR(x+y),LENGTH(x+y),'0'),
   LPAD(TO_CHAR(x+y),LENGTH(x+y)+1,'0'))  FROM
blah
   
   
   
   
   
   
Pulikkol Nitheesh Kumar
   
   
   
Software Engineer  
   
   
   
Mannai Corporation 
   
   
   
C O S - PB # 76
   
   
   
Doha - Qatar   
   
   
   
Phone : 4412-555 extn  
   
-363   
   
   
   
http//www.mannai.com.qa
   
   
   
   
   





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pulikkol Kumar
  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).

RE: simple sql problem

2001-07-31 Thread Swapna_Chinnagangannagari 
Title: RE: simple sql problem





but this will append 0 to numbers >1 (sum (a+b) >1)
which is not the requirement


-Original Message-
From:   Pulikkol Kumar [SMTP:[EMAIL PROTECTED]]
Sent:   Tuesday, July 31, 2001 4:10 PM
To: Multiple recipients of list ORACLE-L
Subject:        RE: simple sql problem



OR


SELECT   LPAD(TO_CHAR(x+y),LENGTH(x+y)+1,'0')  FROM   BLAH


    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pulikkol Kumar
  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).

RE: simple sql problem

2001-07-31 Thread Jacques Kilchoer 
Title: RE: simple sql problem





> -Original Message-
> From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
> 
>  No, I mean, if (x+y) < 1, that is a value of .92 which it 
> prints out, but I want it to print out like, 0.92, that is a 
> "0." is concatenateed to the result.
> 
> therefore, when i have, 
> 
> select x+y from blah
> 
> I should get 90 when the value is really 90
> and I should get 0.92 when the value is really .92


Here is a starting point to help you.


a) Read the manual on the following functions:
sign
decode
to_char
abs


b) Look at the example below
SQL> select
  2    x, y, x + y as x_plus_y,
  3    decode (sign (abs (x + y) - 1),
  4    -1, to_char (x + y, '0.99'),
  5    to_char (x + y, '99')
  6   ) as formatted_x_plus_y
  7   from t ;


 X  Y   X_PLUS_Y FORMA
-- -- -- -
    .7 .3  1   1
   -.4    1.8    1.4   1
    .2    .16    .36  0.36
    13 65 78  78
   -20  -16.2  -36.2 -36
 1   -1.4    -.4 -0.40
    34    -34  0  0.00


7 rows selected.


--
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com

RE: simple sql problem

2001-07-31 Thread Ramon Estevez 

Use Decode function

Ramon Estevez 



*809-565-3121 x 225

 

* [EMAIL PROTECTED]

 



-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Viraj Luthra
Enviado el: Tuesday, 31 July, 2001 12:10 AM
Para: Multiple recipients of list ORACLE-L
Asunto: simple sql problem


Hello all,

If I have a situation, where I have the following:-

select x+y from blah

and if x+y > 1, eg 5 then the output can be 5,
but if x+y < 1, eg, .92, then I need the output as 0.92.

How do I do this?

rgds,

raja


Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ramon Estevez
  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).

Re: simple sql problem

2001-07-31 Thread Jon Walthour 

Raja:

Let me put my two cents into the idea bin. How about:

select decode(sign(num-1),-1,'0' || to_char(num), to_char(num)) as num
  from t1;

Jon Walthour
- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, July 31, 2001 1:10 AM


> Hello all,
> 
> If I have a situation, where I have the following:-
> 
> select x+y from blah
> 
> and if x+y > 1, eg 5 then the output can be 5,
> but if x+y < 1, eg, .92, then I need the output as 0.92.
> 
> How do I do this?
> 
> rgds,
> 
> raja
> 
> 
> Get 250 color business cards for FREE!
> http://businesscards.lycos.com/vp/fastpath/
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Viraj Luthra
>   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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jon Walthour
  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).

RE: simple sql problem

2001-07-31 Thread Pulikkol Kumar 


OR

SELECT   LPAD(TO_CHAR(x+y),LENGTH(x+y)+1,'0')  FROM   BLAH























-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pulikkol Kumar
  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).

RE: simple sql problem

2001-07-31 Thread Amar Kumar Padhi 
Title: RE: simple sql problem



or,
set 
numformat '0.99' (in sql*plus)
 

  -Original Message-From: Swapna_Chinnagangannagari 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, July 
  31, 2001 12:26 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: simple sql problem
  Hello Raja, 
  You can try this way 
  
  1)select x+y sum1 from 
  blah 
  2)col sum1 format 09.99 
  
  3)select x+y sum1 from 
  blah 
  rgds swapna 
  
-Original Message- From:   Viraj 
Luthra [SMTP:[EMAIL PROTECTED]] Sent:   Tuesday, July 
31, 2001 12:31 PM To: Multiple recipients of list ORACLE-L Subject:    RE: simple sql problem 
 No, I mean, if (x+y) < 1, that is a 
value of .92 which it prints out, but I want it to print out like, 0.92, 
that is a "0." is concatenateed to the result.
therefore, when i have, 
select x+y from blah 
I should get 90 when the value is really 
90 and I should get 0.92 when the value 
is really .92 
Any comments 
rgds, 
raja -- 

On Mon, 30 Jul 2001 22:00:23  
 Amar Kumar Padhi wrote: 
>I believe what you meant is if it is less 
than one then the output should be >1. >if so: > >select (case 
when x + y > 1 then x + Y > 
else  1 >    end) from blah; 
> > >-Original 
Message- >Sent: Tuesday, July 31, 
2001 9:10 AM >To: Multiple recipients 
of list ORACLE-L > > >Hello 
all, > >If I have a situation, where I have the following:- 
> >select x+y from blah > >and if x+y > 1, eg 5 
then the output can be 5, >but if x+y 
< 1, eg, .92, then I need the output as 0.92. > >How do I do this? 
> >rgds, > >raja > 
> >Get 
250 color business cards for FREE! >http://businesscards.lycos.com/vp/fastpath/ 
>-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: 
Viraj Luthra >  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). > 
Get 250 color business cards for FREE! 
http://businesscards.lycos.com/vp/fastpath/ 
-- Please see 
the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj 
Luthra   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).

RE: simple sql problem

2001-07-31 Thread Swapna_Chinnagangannagari 
Title: RE: simple sql problem





Hello Raja,


You can try this way


1)select x+y sum1 from blah


2)col sum1 format 09.99


3)select x+y sum1 from blah


rgds
swapna


-Original Message-
From:   Viraj Luthra [SMTP:[EMAIL PROTECTED]]
Sent:   Tuesday, July 31, 2001 12:31 PM
To: Multiple recipients of list ORACLE-L
Subject:    RE: simple sql problem


 No, I mean, if (x+y) < 1, that is a value of .92 which it prints out, but I want it to print out like, 0.92, that is a "0." is concatenateed to the result.

therefore, when i have, 


select x+y from blah


I should get 90 when the value is really 90
and I should get 0.92 when the value is really .92


Any comments


rgds,


raja
--


On Mon, 30 Jul 2001 22:00:23  
 Amar Kumar Padhi wrote:
>I believe what you meant is if it is less than one then the output should be
>1.
>if so:
>
>select (case when x + y > 1 then x + Y
> else  1
>    end) from blah;
>
>
>-Original Message-
>Sent: Tuesday, July 31, 2001 9:10 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello all,
>
>If I have a situation, where I have the following:-
>
>select x+y from blah
>
>and if x+y > 1, eg 5 then the output can be 5,
>but if x+y < 1, eg, .92, then I need the output as 0.92.
>
>How do I do this?
>
>rgds,
>
>raja
>
>
>Get 250 color business cards for FREE!
>http://businesscards.lycos.com/vp/fastpath/
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Viraj Luthra
>  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).
>



Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  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).

RE: simple sql problem

2001-07-30 Thread Viraj Luthra 

 No, I mean, if (x+y) < 1, that is a value of .92 which it prints out, but I want it 
to print out like, 0.92, that is a "0." is concatenateed to the result.

therefore, when i have, 

select x+y from blah

I should get 90 when the value is really 90
and I should get 0.92 when the value is really .92

Any comments

rgds,

raja
--

On Mon, 30 Jul 2001 22:00:23  
 Amar Kumar Padhi wrote:
>I believe what you meant is if it is less than one then the output should be
>1.
>if so:
>
>select (case when x + y > 1 then x + Y
> else  1
>end) from blah;
>
>
>-Original Message-
>Sent: Tuesday, July 31, 2001 9:10 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello all,
>
>If I have a situation, where I have the following:-
>
>select x+y from blah
>
>and if x+y > 1, eg 5 then the output can be 5,
>but if x+y < 1, eg, .92, then I need the output as 0.92.
>
>How do I do this?
>
>rgds,
>
>raja
>
>
>Get 250 color business cards for FREE!
>http://businesscards.lycos.com/vp/fastpath/
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Viraj Luthra
>  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).
>


Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  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).

RE: simple sql problem

2001-07-30 Thread Amar Kumar Padhi 
Title: RE: simple sql problem





I believe what you meant is if it is less than one then the output should be 1.
if so:


select (case when x + y > 1 then x + Y
 else  1
    end) from blah;



-Original Message-
From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 31, 2001 9:10 AM
To: Multiple recipients of list ORACLE-L
Subject: simple sql problem



Hello all,


If I have a situation, where I have the following:-


select x+y from blah


and if x+y > 1, eg 5 then the output can be 5,
but if x+y < 1, eg, .92, then I need the output as 0.92.


How do I do this?


rgds,


raja



Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  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).

RE: Simple SQL Query Question

2001-07-02 Thread ALEMU Abiy 



Your 
query also seems working, not a novice's solution :--)
Thank 
you

  -Message d'origine-De : novicedba 
  [mailto:[EMAIL PROTECTED]]Envoyé : lundi 2 juillet 2001 
  14:05À : Multiple recipients of list 
  ORACLE-LObjet : 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
  
- Original Message - 
From: 
ALEMU Abiy 
To: Multiple 
recipients of list ORACLE-L 
Sent: Monday, July 02, 2001 3:30 
PM
Subject: RE: Simple SQL Query 
Question

For example for a table like this 
one,
 
STATION    
EMISSION_FREQ   
RECEPTION_FREQ-- 
-    
--   STAT1  
192.5  
193.5 STAT2  
192.5   STAT2  194   
STAT1  
193.5  
194.5  
STAT2  195   

 
 
I 
would like to have a value for STAT1 which is equal to 2*2=4  and for 
STAT2 equal to 3*1=3

  -Message d'origine-De : novicedba 
  [mailto:[EMAIL PROTECTED]]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
   
  I did not understand what you meant by 'And I would like to select the 
  number of frequencies, sort of count(*), for a given station and multiply 
  that value by 2 '
  so what I 
  have done is 
  if 
  
      the reception_freq is null 
  (that would mean there is only emission_freq )
  then 
  
      value=emission_freq*1
  else
      
  value=emission_freq*1
  end 
  if
   
  Hope this is 
  what you wanted
   
  cozI am anoviceOracle Certifiable DBBS
  
- Original Message - 
From: 
ALEMU Abiy 
To: Multiple 
recipients of list ORACLE-L 
Sent: Monday, July 02, 2001 1:00 
PM
Subject: Simple SQL Query 
Question

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]
Database Administration 
Engineer

France
 

RE: Simple SQL Query Question

2001-07-02 Thread ALEMU Abiy 

Thank you Harsh, your query seems working

-Message d'origine-
De : Harsh Agrawal [mailto:[EMAIL PROTECTED]]
Envoyé : lundi 2 juillet 2001 14:05
À : Multiple recipients of list ORACLE-L
Objet : RE: Simple SQL Query Question


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 = '';
 
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 :
 
StationEmission_FreqReception_Freq
---
STAT1192.5193.5
STAT2193
STAT2194
STAT2193.5194.5
STAT2195
 
 
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: ALEMU Abiy
  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).

Re: Simple SQL Query Question

2001-07-02 Thread novicedba 



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

  - Original Message - 
  From: 
  ALEMU Abiy 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Monday, July 02, 2001 3:30 PM
  Subject: RE: Simple SQL Query 
  Question
  
  For 
  example for a table like this one,
   
  STATION    
  EMISSION_FREQ   
  RECEPTION_FREQ-- 
  -    
  --   STAT1  
  192.5  
  193.5 STAT2  
  192.5   STAT2  194   
  STAT1  
  193.5  
  194.5  
  STAT2  195   
  
   
   
  I 
  would like to have a value for STAT1 which is equal to 2*2=4  and for 
  STAT2 equal to 3*1=3
  
-Message d'origine-De : novicedba 
[mailto:[EMAIL PROTECTED]]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
 
I did not understand what you meant by 'And I would like to select the 
number of frequencies, sort of count(*), for a given station and multiply 
that value by 2 '
so what I have 
done is 
if 

    the reception_freq is null (that 
would mean there is only emission_freq )
then 

    value=emission_freq*1
else
    
value=emission_freq*1
end 
if
 
Hope this is 
what you wanted
 
cozI am anoviceOracle Certifiable DBBS

  - Original Message - 
  From: 
  ALEMU Abiy 
  To: Multiple recipients of list 
  ORACLE-L 
  Sent: Monday, July 02, 2001 1:00 
  PM
  Subject: Simple SQL Query 
  Question
  
  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]
  Database Administration 
  Engineer
  
  France
   

RE: Simple SQL Query Question

2001-07-02 Thread ALEMU Abiy 



Okay 
but how do you use DECODE with the count(*) ?   For example the query 
below doesn't work
 
SELECT 
decode(reception_freq, null, 1, 2)*count(*) from 
my_table
where 
station = 'STAT1' ;

  -Message d'origine-De : Amar Kumar Padhi 
  [mailto:[EMAIL PROTECTED]]Envoyé : lundi 2 juillet 2001 
  13:20À : Multiple recipients of list 
  ORACLE-LObjet : RE: Simple SQL Query 
  Question
  Well, then decode would be the only option, as suggested by other 
  members. Case is not supported.
   
  rgds
  amar
  
-Original Message-From: ALEMU Abiy 
[mailto:[EMAIL PROTECTED]]Sent: Monday, July 02, 2001 
2:00 PMTo: Multiple recipients of list 
    ORACLE-LSubject: RE: Simple SQL Query 
Question
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-LObjet : 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 = '';
   
  rgds
  amar
  
-Original Message-From: ALEMU Abiy 
[mailto:[EMAIL PROTECTED]]Sent: Monday, July 02, 
2001 11:30 AMTo: Multiple recipients of list 
ORACLE-LSubject: Simple SQL Query 
Question
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]
Database Administration 
Engineer

France
 

RE: Simple SQL Query Question

2001-07-02 Thread Harsh Agrawal 

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 = '';
 
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 :
 
StationEmission_FreqReception_Freq
---
STAT1192.5193.5
STAT2193
STAT2194
STAT2193.5194.5
STAT2195
 
 
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).

RE: Simple SQL Query Question

2001-07-02 Thread Amar Kumar Padhi 



Well, then decode would be the only option, as suggested by other 
members. Case is not supported.
 
rgds
amar

  -Original Message-From: ALEMU Abiy 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, July 02, 2001 
  2:00 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Simple SQL Query Question
  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-LObjet : 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 = '';
 
rgds
amar

  -Original Message-From: ALEMU Abiy 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, July 02, 
  2001 11:30 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Simple SQL Query Question
  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]
  Database Administration 
  Engineer
  
  France
   

RE: Simple SQL Query Question

2001-07-02 Thread ALEMU Abiy 



For 
example for a table like this one,
 
STATION    EMISSION_FREQ   
RECEPTION_FREQ-- 
-    
--   STAT1  
192.5  
193.5 STAT2  
192.5   STAT2  194   
STAT1  
193.5  
194.5  
STAT2  195   

 
 
I 
would like to have a value for STAT1 which is equal to 2*2=4  and for STAT2 
equal to 3*1=3

  -Message d'origine-De : novicedba 
  [mailto:[EMAIL PROTECTED]]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
   
  I did not understand what you meant by 'And I would like to select the 
  number of frequencies, sort of count(*), for a given station and multiply that 
  value by 2 '
  so what I have 
  done is 
  if 
  
      the reception_freq is null (that 
  would mean there is only emission_freq )
  then 
  
      value=emission_freq*1
  else
      
  value=emission_freq*1
  end 
  if
   
  Hope this is what 
  you wanted
   
  cozI am anoviceOracle Certifiable DBBS
  
- Original Message - 
From: 
ALEMU Abiy 
To: Multiple 
recipients of list ORACLE-L 
Sent: Monday, July 02, 2001 1:00 
PM
Subject: Simple SQL Query 
Question

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]
Database Administration 
Engineer

France
 

RE: Simple SQL Query Question

2001-07-02 Thread ALEMU Abiy 



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-LObjet : 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 = '';
   
  rgds
  amar
  
-Original Message-From: ALEMU Abiy 
[mailto:[EMAIL PROTECTED]]Sent: Monday, July 02, 2001 
11:30 AMTo: Multiple recipients of list 
ORACLE-LSubject: Simple SQL Query Question
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]
Database Administration 
Engineer

France
 

Re: Simple SQL Query Question

2001-07-02 Thread novicedba 



you could achieve this using decode
 
 
cozI am anoviceOracle Certifiable DBBS

  - Original Message - 
  From: 
  ALEMU Abiy 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Monday, July 02, 2001 1:00 PM
  Subject: Simple SQL Query Question
  
  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]
  Database Administration 
  Engineer
  
  France
   

Re: Simple SQL Query Question

2001-07-02 Thread novicedba 



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
 
I did not understand what you meant by 'And I would like to select the number 
of frequencies, sort of count(*), for a given station and multiply that value by 
2 '
so what I have done 
is 
if 

    
the reception_freq is null (that would mean there is only emission_freq 
)
then 

    
value=emission_freq*1
else
    
value=emission_freq*1
end 
if
 
Hope this is what 
you wanted
 
cozI am anoviceOracle Certifiable DBBS

  - Original Message - 
  From: 
  ALEMU Abiy 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Monday, July 02, 2001 1:00 PM
  Subject: Simple SQL Query Question
  
  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]
  Database Administration 
  Engineer
  
  France
   

RE: Simple SQL Query Question

2001-07-02 Thread Amar Kumar Padhi 



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 = '';
 
rgds
amar

  -Original Message-From: ALEMU Abiy 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, July 02, 2001 
  11:30 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Simple SQL Query Question
  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]
  Database Administration 
  Engineer
  
  France