RE: dynamic sql problem
Thanks for all those who answered Using authid current_user in package has solved my problem. With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charu Joshi Sent: Tuesday, October 28, 2003 5:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: dynamic sql problem Siddharth, All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer rights. The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer rights queries SESSION_ROLES, the query does not return any rows. Named PL/SQL blocks that execute with invoker rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. So the problem might be that you have been granted 'CREATE TABLE' through a role and not directly. Regards, Charu. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Siddharth Haldankar Sent: 28 October 2003 17:09 To: Multiple recipients of list ORACLE-L Subject: dynamic sql problem Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at COMMADM.CT_REFRESH_PK, line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com
dynamic sql problem
Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at COMMADM.CT_REFRESH_PK, line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED]
RE: dynamic sql problem
Title: Mensagem to use it inside packages u must have some priviledges given directly to u not to a role. Regards PG -Mensagem original-De: Siddharth Haldankar [mailto:[EMAIL PROTECTED] Enviada: terça-feira, 28 de Outubro de 2003 11:39Para: Multiple recipients of list ORACLE-LAssunto: dynamic sql problem Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "COMMADM.CT_REFRESH_PK", line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED]
RE: dynamic sql problem
Your account probably has the create table privilege granted through the resource role, grant create table to your account and try again, privileges granted through a role are not active when running a procedure. -Original Message-From: Siddharth Haldankar [mailto:[EMAIL PROTECTED]Sent: 28 October 2003 11:39To: Multiple recipients of list ORACLE-LSubject: dynamic sql problem Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "COMMADM.CT_REFRESH_PK", line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED]
RE: dynamic sql problem
Siddharth, All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer rights. The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer rights queries SESSION_ROLES, the query does not return any rows. Named PL/SQL blocks that execute with invoker rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. So the problem might be that you have been granted 'CREATE TABLE' through a role and not directly. Regards, Charu. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Siddharth HaldankarSent: 28 October 2003 17:09To: Multiple recipients of list ORACLE-LSubject: dynamic sql problem Hi Gurus, I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block. This is the sample code DECLARE lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; / This runs fine. But as soon as I put this inside a package I get an error PROCEDURE test is lv_sql_stmt VARCHAR2(2000); begin lv_sql_stmt := 'create table a_temp (a number)'; EXECUTE IMMEDIATE lv_sql_stmt; end; ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "COMMADM.CT_REFRESH_PK", line 415 ORA-06512: at line 1 This line 415 is the execute immediate line. Any clues why this is acting strangely. Thanks in advance for your time in answering to my query With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) # : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com
Embedded SQL problem
Title: Embedded SQL problem We have a COBOL program using embedded SQL that is INSERTing thru the DRDA Gateway. -- Any INSERT stmt succeeds via embedded SQL if the fields are all CHAR. -- Any INSERT stmt succeeds via embedded SQL if any fields are VARCHAR2, so LONG AS THE LENGTH IS 74 CHARACTERS. The INSERT fails (ORA-02055) if the VARCHAR2(1069) field contains 75 characters or more. Note that any INSERT stmt succeeds via SQL*Plus if the fields are CHAR or VARCHAR2 (any length). The problem appears to be with an embedded SQL buffer reaching the gateway when VARCHAR2s are involved. Is anyone aware of any OCI issues with 9.2.0.3? The gateway is 9.2.0.4. If anyone wishes to email me privately, I can send you our compiler/sql directive files, the source code (small test program) and table descriptions. Thanx, Alan Martin DBA at Defense Logistics Info Service [EMAIL PROTECTED]
SQL Problem - Merging Result sets
Hi all I have a question which is probably easy for some of you but it is 21:00 and my mind is not working. Would appreciate it if someone can point me in the right direction. version is 8i so no ANSI OUTER JOIN. I have 4 tables: master_tab, ID is PK detail_tab_A, ID is FK detail_tab_B , ID is FK detail_tab_C , ID is FK For each row in the master_tab I would like to know the number of rows in the corresponding child tables. Can this be done in a single DML statement? My base queries are: Q1= select id, from master_tab; Q2= Select id,count(*) from detail_tab_A group by id; Q3= Select id,count(*) from detail_tab_B group by id; Q4= Select id,count(*) from detail_tab_C group by id; Result set (R1 = Result of Q1) Master_tab -- ID1,... ID2,... ID3,... ID4,... ID5,... (R2 = Result of Q2) detail_tab_A ID1, 1 ID2, 3 (R3 = Result of Q3) detail_tab_B ID2, 7 ID4, 5 ID5, 3 (R4 = Result of Q4) detail_tab_C ID3, 7 ID5, 1 The result to look something like (R1+ R2+ R3+ R4) ID1,..., 1, -, - ID2,..., 3, 7, - ID3,..., -, -, 7 ID4,..., -, 5, - ID5,..., -, 3, 1 where - mean blank or 0. Any pointers would be a great help. ta tony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: SQL Problem - Merging Result sets
Hi, Try something like select id, numberrowsA, numberrowsB .. from master_tab m, (select id, count(*) numberrowsa from detail_tabA a where m.id = a.id group by a.id) da, (select id, count(*) numberrowsb from detail_tabb b where m.id = b.id group by b.id) db, ... where da.id = m.id and db.id = m.id Hth, Jeroen -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Verzonden: woensdag 26 maart 2003 11:04 Aan: Multiple recipients of list ORACLE-L Onderwerp: SQL Problem - Merging Result sets Hi all I have a question which is probably easy for some of you but it is 21:00 and my mind is not working. Would appreciate it if someone can point me in the right direction. version is 8i so no ANSI OUTER JOIN. I have 4 tables: master_tab, ID is PK detail_tab_A, ID is FK detail_tab_B , ID is FK detail_tab_C , ID is FK For each row in the master_tab I would like to know the number of rows in the corresponding child tables. Can this be done in a single DML statement? My base queries are: Q1= select id, from master_tab; Q2= Select id,count(*) from detail_tab_A group by id; Q3= Select id,count(*) from detail_tab_B group by id; Q4= Select id,count(*) from detail_tab_C group by id; Result set (R1 = Result of Q1) Master_tab -- ID1,... ID2,... ID3,... ID4,... ID5,... (R2 = Result of Q2) detail_tab_A ID1, 1 ID2, 3 (R3 = Result of Q3) detail_tab_B ID2, 7 ID4, 5 ID5, 3 (R4 = Result of Q4) detail_tab_C ID3, 7 ID5, 1 The result to look something like (R1+ R2+ R3+ R4) ID1,..., 1, -, - ID2,..., 3, 7, - ID3,..., -, -, 7 ID4,..., -, 5, - ID5,..., -, 3, 1 where - mean blank or 0. Any pointers would be a great help. ta tony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Jeroen van Sluisdam 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: SQL Problem - Merging Result sets
Tony - 8i does have outer joins, the syntax is (+) - intuitive right? I haven't run this query, but it might give some ideas. Since nobody has responded yet, maybe it'll provoke the experts ;-) select r1.id, r2.count(*), r3.count(*), r4.count(*) from (select id from master_tab) r1, (select id, count(*) from detail_tab_a group by id) r2, (select id, count(*) from detail_tab_b group by id) r3, (select id, count(*) from detail_tab_c group by id) r4 where r1.id = r2.id (+) r1.id = r3.id (+) r1.id = r4.id (+) -Original Message- Sent: Wednesday, March 26, 2003 4:04 AM To: Multiple recipients of list ORACLE-L Hi all I have a question which is probably easy for some of you but it is 21:00 and my mind is not working. Would appreciate it if someone can point me in the right direction. version is 8i so no ANSI OUTER JOIN. I have 4 tables: master_tab, ID is PK detail_tab_A, ID is FK detail_tab_B , ID is FK detail_tab_C , ID is FK For each row in the master_tab I would like to know the number of rows in the corresponding child tables. Can this be done in a single DML statement? My base queries are: Q1= select id, from master_tab; Q2= Select id,count(*) from detail_tab_A group by id; Q3= Select id,count(*) from detail_tab_B group by id; Q4= Select id,count(*) from detail_tab_C group by id; Result set (R1 = Result of Q1) Master_tab -- ID1,... ID2,... ID3,... ID4,... ID5,... (R2 = Result of Q2) detail_tab_A ID1, 1 ID2, 3 (R3 = Result of Q3) detail_tab_B ID2, 7 ID4, 5 ID5, 3 (R4 = Result of Q4) detail_tab_C ID3, 7 ID5, 1 The result to look something like (R1+ R2+ R3+ R4) ID1,..., 1, -, - ID2,..., 3, 7, - ID3,..., -, -, 7 ID4,..., -, 5, - ID5,..., -, 3, 1 where - mean blank or 0. Any pointers would be a great help. ta tony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: DENNIS WILLIAMS 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: SQL Problem - Merging Result sets
Tony, this works also: select distinct r1.id, count(r2.id),count(r3.id),count(r4.id) from r1, r2, r3, r4 where r2.id (+) = r1.id and r3.id (+) = r1.id and r4.id (+) = r1.id group by r1.id Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, March 26, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Tony - 8i does have outer joins, the syntax is (+) - intuitive right? I haven't run this query, but it might give some ideas. Since nobody has responded yet, maybe it'll provoke the experts ;-) select r1.id, r2.count(*), r3.count(*), r4.count(*) from (select id from master_tab) r1, (select id, count(*) from detail_tab_a group by id) r2, (select id, count(*) from detail_tab_b group by id) r3, (select id, count(*) from detail_tab_c group by id) r4 where r1.id = r2.id (+) r1.id = r3.id (+) r1.id = r4.id (+) -Original Message- Sent: Wednesday, March 26, 2003 4:04 AM To: Multiple recipients of list ORACLE-L Hi all I have a question which is probably easy for some of you but it is 21:00 and my mind is not working. Would appreciate it if someone can point me in the right direction. version is 8i so no ANSI OUTER JOIN. I have 4 tables: master_tab, ID is PK detail_tab_A, ID is FK detail_tab_B , ID is FK detail_tab_C , ID is FK For each row in the master_tab I would like to know the number of rows in the corresponding child tables. Can this be done in a single DML statement? My base queries are: Q1= select id, from master_tab; Q2= Select id,count(*) from detail_tab_A group by id; Q3= Select id,count(*) from detail_tab_B group by id; Q4= Select id,count(*) from detail_tab_C group by id; Result set (R1 = Result of Q1) Master_tab -- ID1,... ID2,... ID3,... ID4,... ID5,... (R2 = Result of Q2) detail_tab_A ID1, 1 ID2, 3 (R3 = Result of Q3) detail_tab_B ID2, 7 ID4, 5 ID5, 3 (R4 = Result of Q4) detail_tab_C ID3, 7 ID5, 1 The result to look something like (R1+ R2+ R3+ R4) ID1,..., 1, -, - ID2,..., 3, 7, - ID3,..., -, -, 7 ID4,..., -, 5, - ID5,..., -, 3, 1 where - mean blank or 0. Any pointers would be a great help. ta tony -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: DENNIS WILLIAMS 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: Mercadante, Thomas F 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: Sql problem
Tim, Thank you very much for your help. I think it'll work. Zsolt At 07:04 2002.12.19. -0800, you wrote: Zsolt, In Oracle9i, you might be able to make use of the new WITH syntax: SQL with xxx as 2 (select 1+2 calc from dual) 3 select calc*2 from xxx; CALC*2 -- 6 It might cut down on typographic errors (if not syntax complexity) from repeatedly retyping the same complex formula... Hope this helps... -Tim -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag 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).
Sql problem
Hi, The following select does not work, I get ORA-00904 error message that the AgentShare column does not exists. It would be painful to write down every calculation many times because I can't make a reference to a previous calculated colunm. Select t.*,t.RowId , CONTR_ROWS_MG - CONTR_ROWS_GROSS_RECEIVED OutStanding , CONTRACT_SALESAGENT_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED / 100 AgentShare ,CONTRACT_SHARE_FIXED OtherShareFixed ,CONTRACT_SHARE_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED /100 OtherSharePercentage ,AgentShare *2 From Contract_Rows T,Contract Where Contract.Contract_Unique = t.contr_rows_contract_unique Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag 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: Sql problem
Two possible answers : 42 and inline views. - Original Message - From: Zsolt Csillag [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 19 Dec 2002 05:43:55 Hi, The following select does not work, I get ORA-00904 error message that the AgentShare column does not exists. It would be painful to write down every calculation many times because I can't make a reference to a previous calculated colunm. Select t.*,t.RowId , CONTR_ROWS_MG - CONTR_ROWS_GROSS_RECEIVED OutStanding , CONTRACT_SALESAGENT_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED / 100 AgentShare ,CONTRACT_SHARE_FIXED OtherShareFixed ,CONTRACT_SHARE_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED /100 OtherSharePercentage ,AgentShare *2 From Contract_Rows T,Contract Where Contract.Contract_Unique = t.contr_rows_contract_unique Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag 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). --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul 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: Sql problem
Use inline views, create a view or use a cursor in a PL/sql block Regards, Waleed -Original Message- Sent: Thursday, December 19, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Hi, The following select does not work, I get ORA-00904 error message that the AgentShare column does not exists. It would be painful to write down every calculation many times because I can't make a reference to a previous calculated colunm. Select t.*,t.RowId , CONTR_ROWS_MG - CONTR_ROWS_GROSS_RECEIVED OutStanding , CONTRACT_SALESAGENT_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED / 100 AgentShare ,CONTRACT_SHARE_FIXED OtherShareFixed ,CONTRACT_SHARE_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED /100 OtherSharePercentage ,AgentShare *2 From Contract_Rows T,Contract Where Contract.Contract_Unique = t.contr_rows_contract_unique Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag 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: Khedr, Waleed 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).
AW: Sql problem
Try following: Select t.*,t.RowId , CONTR_ROWS_MG - CONTR_ROWS_GROSS_RECEIVED OutStanding , CONTRACT_SALESAGENT_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED / 100 AgentShare ,CONTRACT_SHARE_FIXED OtherShareFixed ,CONTRACT_SHARE_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED /100 OtherSharePercentage ,(CONTRACT_SALESAGENT_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED / 100)*2 AgentShare2 From Contract_Rows T,Contract Where Contract.Contract_Unique = t.contr_rows_contract_unique Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Zsolt Csillag [mailto:[EMAIL PROTECTED]] Gesendet: Donnerstag, 19. Dezember 2002 14:44 An: Multiple recipients of list ORACLE-L Betreff: Sql problem Hi, The following select does not work, I get ORA-00904 error message that the AgentShare column does not exists. It would be painful to write down every calculation many times because I can't make a reference to a previous calculated colunm. Select t.*,t.RowId , CONTR_ROWS_MG - CONTR_ROWS_GROSS_RECEIVED OutStanding , CONTRACT_SALESAGENT_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED / 100 AgentShare ,CONTRACT_SHARE_FIXED OtherShareFixed ,CONTRACT_SHARE_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED /100 OtherSharePercentage ,AgentShare *2 From Contract_Rows T,Contract Where Contract.Contract_Unique = t.contr_rows_contract_unique Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag 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: 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: Sql problem
Zsolt, In Oracle9i, you might be able to make use of the new WITH syntax: SQL with xxx as 2 (select 1+2 calc from dual) 3 select calc*2 from xxx; CALC*2 -- 6 It might cut down on typographic errors (if not syntax complexity) from repeatedly retyping the same complex formula... Hope this helps... -Tim Hi, The following select does not work, I get ORA-00904 error message that the AgentShare column does not exists. It would be painful to write down every calculation many times because I can't make a reference to a previous calculated colunm. Select t.*,t.RowId , CONTR_ROWS_MG - CONTR_ROWS_GROSS_RECEIVED OutStanding , CONTRACT_SALESAGENT_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED / 100 AgentShare ,CONTRACT_SHARE_FIXED OtherShareFixed ,CONTRACT_SHARE_PERCENTAGE * CONTR_ROWS_GROSS_RECEIVED /100 OtherSharePercentage ,AgentShare *2 From Contract_Rows T,Contract Where Contract.Contract_Unique = t.contr_rows_contract_unique Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zsolt Csillag 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: Tim Gorman 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).
Dynamic SQL problem (Oracle 9-8)
Title: Dynamic SQL problem (Oracle 9-8)
Hello,
I have software compiled in Oracle 9 environment. And when I try to fetch data using
different versions of Oracle client/server I receive ORA-errors:
1. client 9 -- server 8: ORA-00932: inconsistent datatypes
2. client 8 -- server 9: ORA-01455: converting column overflows integer datatype
These errors do not occur when connecting client 9 to server 9.
I use the following example code (similar to example in Oracle 9 documentation):
EXEC SQL BEGIN DECLARE SECTION;
int intn_b;
VARCHAR buffer[6];
VARCHAR cursor_def[500];
EXEC SQL END DECLARE SECTION;
strcpy((char*)cursor_def.arr, SELECT tstintn,tstcode FROM test WHERE tstcode LIKE 'AU%');
cursor_def.len = strlen((char*)cursor_def.arr);
EXEC SQL PREPARE S FROM :cursor_def;
EXEC SQL DECLARE My_cursor CURSOR FOR S;
EXEC SQL OPEN My_cursor ;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
int i=0;
EXEC SQL FETCH My_cursor INTO :intn_b, :buffer;
buffer.arr[buffer.len] = '\0';
printf(%6d %7s\n, intn_b, buffer.arr);
}
EXEC SQL CLOSE My_cursor;
When I declare cursor without using dynamic SQL:
EXEC SQL DECLARE My_cursor CURSOR FOR
SELECT tstintn, tstcode FROM test WHERE tstcode LIKE 'AU%';
everything works fine.
Does anyone know how to solve this problem?
Regards,
Rafal Wojnar
e-mail: [EMAIL PROTECTED]
RE: RE: Long running SQL Problem?
Catherine, I am sure that Larry Elkins will forgive my taking the opportunity of being 7 hours ahead of him (and 7 hours behind yourself) for singing the praise of the 'hash anti-join' in his name. And anyway you could remind your senior DBA the existence of NOT EXISTS, far better than COUNT(*) in this case. Basically, in case A you have a non-correlated sub-query, and in case B a correlated one. A correlated sub-query means that for each row from Table_1 you must search Table_2. For one thing, if the corresponding columns are not indexed, you're dead. Even if they are, it may really be bad if Table_1 is huge AND THERE IS NO OTHER CRITERION, because you will have a full scan of Table_1. A non-correlated subquery is executed only once. If it returns few rows, you will have a full TS of Table_1 in both cases, but the NOT IN is likely to be slightly more efficient. If it returns many rows, if Table_1 is big, if there is no other criterion and if col3 and col4 are not null, then the NOT IN with a hint asking for a hash anti-join will outperform a NOT EXISTS, not to mention a 0 = (select COUNT(*) ...). The nested loops of a correlated subquery will be excellent when you have fairly selective criteria besides, and when the correlated subquery is, so to speak, the icing on the cake. Let me add that an external join with a test for nullity usually gives fairly good results too (in fact, it often goes the hash antijoin way) and that I have also had excellent results under some circumstances with an inline view (typically when you have additional criteria bearing on Table_2) ... To summarize, abruptly saying 'this sucks' exposes you to be proved wrong once in a while. - Original Message - From: CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 02 Apr 2002 21:13:19 Hi Gurus, My senior DBA always tell us that the not in command sucks and we are all encourage to use the select count(*). SQL A is greatly frowned upon and SQL B will be the best. SQL A : SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); SQL B : SELECT col1,col2 FROM Table_1 A WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE b.col3=a.col1 AND b.col4=a.col2)); Qn : Is it true ? Could someone shed some light ? Please advise. Thanks. Regds, Catherine Stephane Faroult Oriole Corporation Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Long running SQL Problem?
I would rewrite it with a where not exists But that is just me. I would also not bother selecting anything in the subquery. Just a 1 or a 'x' would do. Don't return stuff from the database that you don't want. All you care is whether or not there is a record in the subquery not what it is or how many there are. When you have that criteria think EXISTS. -Original Message- Sent: Tuesday, April 02, 2002 10:48 PM To: Multiple recipients of list ORACLE-L Why not code up a couple of SQL's and try it out? I just tried a couple of examples that match your code, and the NOT IN version was slightly faster, so I'm not frowning upon it. ;-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 02, 2002 9:13 PM Hi Gurus, My senior DBA always tell us that the not in command sucks and we are all encourage to use the select count(*). SQL A is greatly frowned upon and SQL B will be the best. SQL A : SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); SQL B : SELECT col1,col2 FROM Table_1 A WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE b.col3=a.col1 AND b.col4=a.col2)); Qn : Is it true ? Could someone shed some light ? Please advise. Thanks. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 8:19 PM To: CHAN Chor Ling Catherine (CSC) Hello Catherine Thanks first of all for your suggestions. The indexes were already in exitance before your email, so I did not even try that. But your query and that of Marco van Rooy ran exactly the same number of seconds. They are both basicly the same. Marco's looked like this... SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); Because both yours and Marcos brought the data back in so short a time *16sec*, I have not yet experimented with any of the others. Thanks again Rgds Denham -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, March 27, 2002 2:00 PM To: '[EMAIL PROTECTED]' Hi Denham, I would like to know which solution is the fastest. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 7:44 PM To: Multiple recipients of list ORACLE-L Hi List Thank you to everyone who took the time to answer, I never realised that there could be so many solutions :) Rgds Denham -Original Message- Sent: Wednesday, March 27, 2002 10:53 AM To: Multiple recipients of list ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com http://www.marshalsoftware.com www.marshalsoftware.com _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com http://www.marshalsoftware.com www.marshalsoftware.com _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) 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: Greg Moore 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
RE: Long running SQL Problem?
Use 'exists' or 'not exists' only if you have index on col3 and col4 on table_2 that can be used in the sub-query, else the query will be running like a dog. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Follow-up :Long running SQL Problem?
CHAN Chor Ling Catherine (CSC) wrote: Hi Gurus, My senior DBA always tell us that the not in command sucks and we are all encourage to use the select count(*). SQL A is greatly frowned upon and SQL B will be the best. SQL A : SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); SQL B : SELECT col1,col2 FROM Table_1 A WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE b.col3=a.col1 AND b.col4=a.col2)); Qn : Is it true ? Could someone shed some light ? Please advise. Thanks. Regds, Catherine Larry has pointed to me off-list that your 'SQL A' query is indeed correlated - totally unusual for a 'NOT IN' and, in your case, such a case for disaster (couldn't return anything) that I presume that you typed it as fast as I read it initially? Being as lazy as he is :-) here is from Larry's message : Also, point her towards Metalink note 28934.1. It contains a good discussion. But I don't agree with the final conclusion to always use NOT EXISTS even though a NOT IN using a HASH AJ is sometimes much better. The only reason for that recommendation was their fear that many folks don't understand how a NOT IN handles nulls in the results set (returns no rows) differently than a NOT EXISTS. A good developer should know the difference. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Follow-up :Long running SQL Problem?
Hi, Thanks to those who take the trouble to reply. It's indeed enlightening. I've learnt a lot from you guys. Regds, New Bee -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 3:54 AM To: Multiple recipients of list ORACLE-L Subject:Re: Follow-up :Long running SQL Problem? CHAN Chor Ling Catherine (CSC) wrote: Hi Gurus, My senior DBA always tell us that the not in command sucks and we are all encourage to use the select count(*). SQL A is greatly frowned upon and SQL B will be the best. SQL A : SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); SQL B : SELECT col1,col2 FROM Table_1 A WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE b.col3=a.col1 AND b.col4=a.col2)); Qn : Is it true ? Could someone shed some light ? Please advise. Thanks. Regds, Catherine Larry has pointed to me off-list that your 'SQL A' query is indeed correlated - totally unusual for a 'NOT IN' and, in your case, such a case for disaster (couldn't return anything) that I presume that you typed it as fast as I read it initially? Being as lazy as he is :-) here is from Larry's message : Also, point her towards Metalink note 28934.1. It contains a good discussion. But I don't agree with the final conclusion to always use NOT EXISTS even though a NOT IN using a HASH AJ is sometimes much better. The only reason for that recommendation was their fear that many folks don't understand how a NOT IN handles nulls in the results set (returns no rows) differently than a NOT EXISTS. A good developer should know the difference. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: CHAN Chor Ling Catherine (CSC) 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: Long running SQL Problem?
Hi Gurus, My senior DBA always tell us that the not in command sucks and we are all encourage to use the select count(*). SQL A is greatly frowned upon and SQL B will be the best. SQL A : SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); SQL B : SELECT col1,col2 FROM Table_1 A WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE b.col3=a.col1 AND b.col4=a.col2)); Qn : Is it true ? Could someone shed some light ? Please advise. Thanks. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 8:19 PM To: CHAN Chor Ling Catherine (CSC) Hello Catherine Thanks first of all for your suggestions. The indexes were already in exitance before your email, so I did not even try that. But your query and that of Marco van Rooy ran exactly the same number of seconds. They are both basicly the same. Marco's looked like this... SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); Because both yours and Marcos brought the data back in so short a time *16sec*, I have not yet experimented with any of the others. Thanks again Rgds Denham -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, March 27, 2002 2:00 PM To: '[EMAIL PROTECTED]' Hi Denham, I would like to know which solution is the fastest. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 7:44 PM To: Multiple recipients of list ORACLE-L Hi List Thank you to everyone who took the time to answer, I never realised that there could be so many solutions :) Rgds Denham -Original Message- Sent: Wednesday, March 27, 2002 10:53 AM To: Multiple recipients of list ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com http://www.marshalsoftware.com www.marshalsoftware.com _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com http://www.marshalsoftware.com www.marshalsoftware.com _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) 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: Long running SQL Problem?
Why not code up a couple of SQL's and try it out? I just tried a couple of examples that match your code, and the NOT IN version was slightly faster, so I'm not frowning upon it. ;-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 02, 2002 9:13 PM Hi Gurus, My senior DBA always tell us that the not in command sucks and we are all encourage to use the select count(*). SQL A is greatly frowned upon and SQL B will be the best. SQL A : SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); SQL B : SELECT col1,col2 FROM Table_1 A WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE b.col3=a.col1 AND b.col4=a.col2)); Qn : Is it true ? Could someone shed some light ? Please advise. Thanks. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 8:19 PM To: CHAN Chor Ling Catherine (CSC) Hello Catherine Thanks first of all for your suggestions. The indexes were already in exitance before your email, so I did not even try that. But your query and that of Marco van Rooy ran exactly the same number of seconds. They are both basicly the same. Marco's looked like this... SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2 WHERE col3 = col1 AND col4 = col2); Because both yours and Marcos brought the data back in so short a time *16sec*, I have not yet experimented with any of the others. Thanks again Rgds Denham -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, March 27, 2002 2:00 PM To: '[EMAIL PROTECTED]' Hi Denham, I would like to know which solution is the fastest. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 7:44 PM To: Multiple recipients of list ORACLE-L Hi List Thank you to everyone who took the time to answer, I never realised that there could be so many solutions :) Rgds Denham -Original Message- Sent: Wednesday, March 27, 2002 10:53 AM To: Multiple recipients of list ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com http://www.marshalsoftware.com www.marshalsoftware.com _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com http://www.marshalsoftware.com www.marshalsoftware.com _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) 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: Greg Moore 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).
Long running SQL Problem?
Title: Long running SQL Problem? Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: Long running SQL Problem?
Hi, You could try the NOT EXIST flavour. It should be able to use indexes than Jack Denham Eva [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Long running SQL Problem? 27-03-2002 09:53 Please respond to ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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
RE: Long running SQL Problem?
Hi Denham, Suggestion 1) Perhaps you may create an index for table_1 (col1,col2) and table_2 (col3,col4) Suggestion 2) Try SELECT col1,col2 FROM Table_1 WHERE (0=(select count(*) from table_2 where col3=col1 and col4=col2)) Hope it helps. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 4:53 PM To: Multiple recipients of list ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) 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: Long running SQL Problem?
Should be better with select col1, col2 from table_1 minus select col3, col4 from table2 Iain Nicoll -Original Message- Sent: Wednesday, March 27, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: Long running SQL Problem?
Title: Long running SQL Problem? Hi List Thank you to everyone who took the time to answer, I never realised that there could be so many solutions :) Rgds Denham -Original Message-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Wednesday, March 27, 2002 10:53 AMTo: Multiple recipients of list ORACLE-LSubject: Long running SQL Problem? Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: Long running SQL Problem?
Try something like select a.col1||a.col2, b.col3||b.col4 from table1 a, table2 b where a.col1||a.col2 = b.col3||b.col4 (+)) WHERE b.col3||b.col4 IS NULL; I think that works. Denham Eva EvaDTo: Multiple recipients of list ORACLE-L @TFMC.co.za [EMAIL PROTECTED] Sent by: rootcc: Subject: Long running SQL Problem? 03/27/2002 03:53 AM Please respond to ORACLE-L Hello List Is there anyone who can give me a solution to this problem. It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); TIA Denham Eva Oracle DBA This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com -- 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: Long running SQL Problem? [stupid alternative to NOT IN]
ORACLE-L Digest -- Volume 2002, Number 086 From: Denham Eva [EMAIL PROTECTED] Date: Wed, 27 Mar 2002 10:58:23 +0200 Subject: Long running SQL Problem? ... Is there anyone who can give me a solution to this problem. get faster/more RAM, CPU, hard drives, etc? It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + rows. Is there perhaps a quicker more effecient way of doing this. SELECT col1,col2 FROM Table_1 WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2); these are indexed? probably no faster, but will probably barf out the initial chunks of output as it goes along, and thus seem slightly less boring to anyone observing the output during runtime: SELECT col1, col2 /* debug only: , col3, col4 */ FROM Table_1 t1, Table_2 t2 WHERE t1.col1 = t2.col3 (+) and t1.col2 = t2.col4 (+) and ( t2.col3 is null and t2.col4 is null ); btw, there is a similar alternative using exists, but I haven't found it to be any faster. regards, ep -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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:
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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
Create a file like
sqlplus -s scott/tiger !
select * from emp;
!
Then simply execute the file
Binay
---
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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).
---
The contents of this e-mail are confidential to the ordinary user
of the e-mail address to which it was addressed and may also be
privileged. If you are not the addressee of this e-mail you should
not copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this e-mail in error
please notify us by telephone or e-mail the sender by replying to
this message, and then delete this e-mail and other copies of it
from your computer system. Thank you.
We reserve the right to monitor all e-mail communications through
our network.
--
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
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 !
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 sys.dba_users a, v$session, v$sqlarea
where parsing_user_id=user_id AND address=sql_address(+)
and sid=15
!
I get nothing, like I get this :-
SQL*Plus: Release 8.1.5.0.0 - Production on Tue Dec 4 17:31:01 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production
SQL 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
select * from dual;
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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: DENNIS WILLIAMS
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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).
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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).
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
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
select * from dual;
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
Jared,
Using the 2nd option:-
sqlplus system/manager@orcl815 !
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 sys.dba_users a, v$session, v$sqlarea
where parsing_user_id=user_id AND address=sql_address(+)
and sid=15
!
I get nothing, like I get this :-
SQL*Plus: Release 8.1.5.0.0 - Production on Tue Dec 4 17:31:01 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production
SQL 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
select * from dual;
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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
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 [option] [user[/password] [@host]]
[@startfile [parm1] [parm2] ...]
where option ::= { -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: dynamic sql problem
Hi there, It could just be that you are missing your semicolons ';' off the end of your statements. Cheers, Kev. __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. Tel: 0141 568 2314 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 06 August 2001 21:57 To: Multiple recipients of list ORACLE-L HI, I am running a stored procdures which contains following dynamic sql ..i am getting error: ORA-00936 missing expression. for the bold statement while rest of statements r properly executing.. what might be the reason.. and temp_ are variables declared.. and name1,name2,name3 are dynamically generated table names.. str := 'alter table'||' '||name2||' '||'disable constraint'||' '||temp1_cons; execute immediate str; str := 'alter table'||' '||name3||' '||'disable constraint'||' '||temp_cons; execute immediate str; str :='update '||' '||name2||' '||' set id_sess=id_sess + '||' '||temp_id_sess_2; execute immediate str; str :='update '||' '||name3||' '||' set id_sess = id_sess +'||' '||temp_id_sess_3; execute immediate str; str :='create table'||' '||enum||' '||'as select * from'||' '||name1||' '||'union select * from'||' '||name2||' '||'union select * from'||' '||name3; execute immediate str; Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Thomas, Kevin 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).
dynamic sql problem
HI, I am running a stored procdures which contains following dynamic sql ..i am getting error: ORA-00936 missing expression. for the bold statement while rest of statements r properly executing.. what might be the reason.. and temp_ are variables declared.. and name1,name2,name3 are dynamically generated table names.. str := 'alter table'||' '||name2||' '||'disable constraint'||' '||temp1_cons; execute immediate str; str := 'alter table'||' '||name3||' '||'disable constraint'||' '||temp_cons; execute immediate str; str :='update '||' '||name2||' '||' set id_sess=id_sess + '||' '||temp_id_sess_2; execute immediate str; str :='update '||' '||name3||' '||' set id_sess = id_sess +'||' '||temp_id_sess_3; execute immediate str; str :='create table'||' '||enum||' '||'as select * from'||' '||name1||' '||'union select * from'||' '||name2||' '||'union select * from'||' '||name3; execute immediate str; Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: dynamic sql problem
Which is the problem statement? I didn't see the bold. David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Harvinder Singh Harvinder.Singh@metr To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] atech.com cc: Sent by:Subject: dynamic sql problem [EMAIL PROTECTED] 08/06/2001 03:57 PM Please respond to ORACLE-L HI, I am running a stored procdures which contains following dynamic sql ..i am getting error: ORA-00936 missing expression. for the bold statement while rest of statements r properly executing.. what might be the reason.. and temp_ are variables declared.. and name1,name2,name3 are dynamically generated table names.. str := 'alter table'||' '||name2||' '||'disable constraint'||' '||temp1_cons; execute immediate str; str := 'alter table'||' '||name3||' '||'disable constraint'||' ' ||temp_cons; execute immediate str; str :='update '||' '||name2||' '||' set id_sess=id_sess + '||' '||temp_id_sess_2; execute immediate str; str :='update '||' '||name3||' '||' set id_sess = id_sess +'||' '||temp_id_sess_3; execute immediate str; str :='create table'||' '||enum||' '||'as select * from'||' '||name1||' '||'union select * from'||' '||name2||' '||'union select * from'||' '||name3; execute immediate str; Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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).
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
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
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
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
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
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
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
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).
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 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).
urgent pls...PL/SQL problem..
hi list, i'm conerting my old db structure into a new one with entirely different constraints, references, etc.. the new structure holds many new tables some splitted from old ones with few new columns. and like. now i want to move my old data into this new one. i 'm doing it through pl/sql, writing procedures, etc. now my problem is i've a master table for skills. and a detailed table referencing it. previously the detailed table stores the values with no ref. to the master, now it is storing the code corresponding the master value. i want to compare the old detailed table and old master table's values to fetch the code from the master table. when i try to fetch this through cursor, the values containing null in detailed tableare returning error.. while rest going fine. how do i treat the nulls, the nvl(col,'0') to convert nulls to 0 is also not working. any idea. Saurabh Sharma [EMAIL PROTECTED]http://www.geocities.com/saurabh00pc/stride.html
pl/sql problem
Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: pl/sql problem
Thats a pain in the rear errer caused by DBMS_OUTPUT. DBMS_OUTPUT buffers all its data up and then ships it to you at one time. For this reason you need to define the buffer large enough to handle all your output. In your code, put the line DBMS_OUTPUT.ENABLE(2); or some such number to set your buffer. In this case I picked 20,000 for the buffer size. -Original Message- Sent: Wednesday, June 06, 2001 5:06 PM To: Multiple recipients of list ORACLE-L Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: pl/sql problem
The reason is the limitation of your output buffer. You should say 'SET SERVEROUTPUT ON SIZE 100' or give the appropriate dbms_output.enable comand and it will work. -Original Message- Sent: Wednesday, June 06, 2001 6:06 PM To: Multiple recipients of list ORACLE-L Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Gogala, Mladen 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: pl/sql problem
In addition to the reason for your error, which someone else has pointed out, this piece of code has another problem -- you never close your cursor. This will get you into no ends of trouble at some point in the future, so I though I should point it out. Also, a cursor for loop would be much cleaner, and you won't have to worry about closing the cursor. Like the following... declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); --enum t_enum_data.nm_enum_data%type;Don't need this variable declaration any more begin dbms_output.enable(90); -- This can be a value up to 99 for enumRec in c1 loop dbms_output.put_line(enumRec.nm_enum_data); end loop; end; / Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Harvinder Singh Harvinder.Singh@MetrTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] aTech.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: pl/sql problem 06/06/2001 06:05 PM Please respond to ORACLE-L Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-06512: at line 16 What might be the reason Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: SQL problem: retrieve child records if existent
Helmut Daiminger wrote: Hi! I have a SQL problem here, which I can't solve. I have a B-tree structure in a table (parentID, childID). If a parent has kids then select those kids. If not, select this parent. How do I code this in SQL? I tried using connect by but this doesn't include start point. Any idea? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger INET: [EMAIL PROTECTED] select t.id, t.name from your_table t where t.id = what you are looking for and not exists (select null from your_table t2 where t2.parent_id = t.id) union select t.id, t.name from your_table t where t.parent_id = what you are looking for Index on parent_id, please. Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: SQL PROBLEM
Title: MS access Can you try this: select count (1) from ( select1 from table_ a where ord_id =17 union select 1 from table_b where ord_id = 17) / Hi, Favour me insuggest ahint in writing a sql forfollowing scenaio: Table A has 2 columns ord_id and line_id example rows are: ord_id line_id 17 null null42 TableB has columns ord_id and line_id ord_id line_id 17 42 17 43 1744 I have to write a sql to count(*) from table A where ord_id=17 .. as u see from table B line 42 is row of ord_id=17 ..so i want count(*) should show =2 from tabel A but if i do count(*) from table A where ord_id=17..it shows 1.. i need to put another condition which will check line id with TABLE B and if it is for this particular order ...add that also in count(*). I know scenario is little fuzzy sorry for it..but if u got my point kindly respond. Thanks Harvinder
RE: SQL PROBLEM
Title: MS access Ido not seem to have exactlyunderstood u r problem..but try this anyway select count(*) from awhere l_id in (selectb.bl_idfroma,bwherea.o_id = 17anda.o_id = b.bo_id )OR(a.o_id = 17) COUNT(*)-- 2 1 row selected. The table data is as listed below SQL select * from a; O_ID L_ID -- -- 17NULLNULL 42 2 rows selected. SQL select * from b; BO_ID BL_ID-- -- 17 42 17 43 17 44 3 rows selected. Regards, Karthik Mohan TCS Consultant at GEP (HK) DialComm 3310962 ISD : 852-26290962 -Original Message-From: Niyi Olajide [mailto:[EMAIL PROTECTED]]Sent: Friday, May 25, 2001 4:36 PMTo: Multiple recipients of list ORACLE-LSubject: RE: SQL PROBLEM Can you try this: select count (1) from ( select1 from table_ a where ord_id =17 union select 1 from table_b where ord_id = 17) / Hi, Favour me insuggest ahint in writing a sql forfollowing scenaio: Table A has 2 columns ord_id and line_id example rows are: ord_id line_id 17 null null42 TableB has columns ord_id and line_id ord_id line_id 17 42 17 43 1744 I have to write a sql to count(*) from table A where ord_id=17 .. as u see from table B line 42 is row of ord_id=17 ..so i want count(*) should show =2 from tabel A but if i do count(*) from table A where ord_id=17..it shows 1.. i need to put another condition which will check line id with TABLE B and if it is for this particular order ...add that also in count(*). I know scenario is little fuzzy sorry for it..but if u got my point kindly respond. Thanks Harvinder
SQL PROBLEM for duplicate rows..
Hi Table A got some duplicated rows.. The requirement is to delete the duplicate records by maintaing one copy.. Is it manageable thru single query? or have to go for PL/SQL procedure? Pl. send me ur valuable inputs. Saravanakumar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: N. SARAVANA 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: SQL PROBLEM for duplicate rows..
I think this query eliminates the duplicated rows: delete from table_name where rowid not in (select maxrowid from (select column1,column2, max(rowid) maxrowid from den group by column1,column2)); - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 25, 2001 1:01 PM Hi Table A got some duplicated rows.. The requirement is to delete the duplicate records by maintaing one copy.. Is it manageable thru single query? or have to go for PL/SQL procedure? Pl. send me ur valuable inputs. Saravanakumar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: N. SARAVANA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= 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: SQL PROBLEM for duplicate rows..
I am sorry I made a mistake in naming . Correct one is below: delete from table_name where rowid not in (select maxrowid from (select column1,column2, max(rowid) maxrowid from table_name group by column1,column2)); - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 25, 2001 1:22 PM I think this query eliminates the duplicated rows: delete from table_name where rowid not in (select maxrowid from (select column1,column2, max(rowid) maxrowid from den group by column1,column2)); - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 25, 2001 1:01 PM Hi Table A got some duplicated rows.. The requirement is to delete the duplicate records by maintaing one copy.. Is it manageable thru single query? or have to go for PL/SQL procedure? Pl. send me ur valuable inputs. Saravanakumar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: N. SARAVANA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= 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: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= 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: SQL PROBLEM for duplicate rows..
Yes, It works .. Thanks.. On Fri, 25 May 2001, [iso-8859-1] Burçin Üstün Kýlýç wrote: I think this query eliminates the duplicated rows: delete from table_name where rowid not in (select maxrowid from (select column1,column2, max(rowid) maxrowid from den group by column1,column2)); - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 25, 2001 1:01 PM Hi Table A got some duplicated rows.. The requirement is to delete the duplicate records by maintaing one copy.. Is it manageable thru single query? or have to go for PL/SQL procedure? Pl. send me ur valuable inputs. Saravanakumar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: N. SARAVANA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= 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: N. SARAVANA 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).
SQL PROBLEM
Title: MS access Hi, Favour me insuggest ahint in writing a sql forfollowing scenaio: Table A has 2 columns ord_id and line_id example rows are: ord_id line_id 17 null null42 TableB has columns ord_id and line_id ord_id line_id 17 42 17 43 1744 I have to write a sql to count(*) from table A where ord_id=17 .. as u see from table B line 42 is row of ord_id=17 ..so i want count(*) should show =2 from tabel A but if i do count(*) from table A where ord_id=17..it shows 1.. i need to put another condition which will check line id with TABLE B and if it is for this particular order ...add that also in count(*). I know scenario is little fuzzy sorry for it..but if u got my point kindly respond. Thanks Harvinder
