RE: Simple SQL Question
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).
Simple SQL Question
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).
RE: simple SQL
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
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
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
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).
simple SQL
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).
Thanks: simple sql problem - 1 more problem
Thanks to all who responded, Appreciate it.
The grok approach was not working so had to go with the following one.
Now just 1 more problem is that how I do I set the environment, say if I want to do
set head off, in the following command can I do that?
The following command I had to do :-
echo "SELECT a.username,substr(sql_text,1,100) as \"SQL
TEXT\",substr(sql_text,101,250) as \"Second Text\",substr(sql_text,601,250) as \"Third
Text\",substr(sql_text,851,250) as \"Fourth Text \",substr(sql_text,1101,250) as
\"Fifth Text \" FROM sys.dba_users a, v\$session, v\$sqlarea where
parsing_user_id=user_id AND address=sql_address(+) and sid = #sid ;
" | $ORACLE_HOME/bin/sqlplus #username/#passwd@$ORACLE_SID
--
On Tue, 04 Dec 2001 16:06:01
Reardon, Bruce (CALBBAY) wrote:
>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).
>
--
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
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
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
>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
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
RE: simple sql problem
b'coz you have to type select * from v\$instance;"
you are passing arguments from shell. You have to escape $ like \$
HTH
-Original Message-
Sent: Tuesday, December 04, 2001 3:50 AM
To: Multiple recipients of list ORACLE-L
Even using like this:-
echo "select * from v$instance;" | sqlplus system/manager
gives me the error :-
SQL> select * from v
*
ERROR at line 1:
ORA-00942: table or view does not exist
Though I can do the same sql statement from inside sqlplus with ease, so
why does it give table or view does not exist?
I am confused ..
rgds,
--
On Mon, 03 Dec 2001 21:50:17
Ross Collado wrote:
>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).
>
--
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: Tatireddy, Shrinivas (MED, Keane)
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
If you are running it on Unix, you must "escape" the "$" in "v$instance".
thus, you would do
echo "select * from v\$instance;" | sqlplus system/manager
Note the "\" key before the "$".
Hemant
"Viraj Luthra" <[EMAIL PROTECTED]> 04/12/2001 02:50 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: RE: simple sql problem
Even using like this:-
echo "select * from v$instance;" | sqlplus system/manager
gives me the error :-
SQL> select * from v
*
ERROR at line 1:
ORA-00942: table or view does not exist
Though I can do the same sql statement from inside sqlplus with ease, so
why does it give table or view does not exist?
I am confused ..
rgds,
--
On Mon, 03 Dec 2001 21:50:17
Ross Collado wrote:
>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).
>
--
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 mes
RE: simple sql problem
Even using like this:-
echo "select * from v$instance;" | sqlplus system/manager
gives me the error :-
SQL> select * from v
*
ERROR at line 1:
ORA-00942: table or view does not exist
Though I can do the same sql statement from inside sqlplus with ease, so why does it
give table or view does not exist?
I am confused ..
rgds,
--
On Mon, 03 Dec 2001 21:50:17
Ross Collado wrote:
>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).
>
--
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
Title: RE: simple sql problem
Hi...
You cann't run sql statement in command line directly However you can in case of script file.
Make the query as a script file. Then run it on SQLPLUS.
-- c:\test.sql
-
select * from emp
/
exit
/
--
c:\> sqlplus -s scott/tiger@oranir @c:\test
This will work.
HTH.
Rgds,
Nirmal,
-Original Message-
From: Viraj Luthra [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, December 04, 2001 8:11 AM
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).
RE: simple sql problem
try this
$ echo "select * from emp;" | sqlplus scott/tiger
srinivas
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 04, 2001 2:31 AM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tatireddy, Shrinivas (MED, Keane)
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
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
Re: simple sql problem
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
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
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).
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).
SOLVED: simple sql problem
Hello all, Sorry I was sick, therefore not able to answer. I followed suggestions from Pulikkol, Swapna and Jon, and it works just fine now. Thanks to all for getting me out of this one. 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 problem
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
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
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
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
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
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
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
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
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
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).
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
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
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
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
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
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
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
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
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
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
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
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
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 cril.gif
