RE: dynamic sql problem

[Siddharth Haldankar]

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

RE: dynamic sql problem

[Charu Joshi]

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

RE: dynamic sql problem

[McBain, Neil SITI-ITDIEEE]

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

[Paulo Gomes]

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] 
  
   

dynamic sql problem

[Siddharth Haldankar]

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] 

 

Embedded SQL problem

[Martin, Alan (Contractor) (DLIS)]

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]

RE: SQL Problem - Merging Result sets

[Mercadante, Thomas F]

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 - Merging Result sets

[DENNIS WILLIAMS]

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

[Jeroen van Sluisdam]

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).

SQL Problem - Merging Result sets

[tjambu_fatcity]

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

[Zsolt Csillag]

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).

Re: Sql problem

[Tim Gorman]

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).

AW: Sql problem

[v . schoen]

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

[Khedr, Waleed]

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).

RE: Sql problem

[Stephane Faroult]

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).

Sql problem

[Zsolt Csillag]

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).

Dynamic SQL problem (Oracle 9<->8)

[Rafal Wojnar]

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: Follow-up :Long running SQL Problem?

[CHAN Chor Ling Catherine (CSC)]

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: Follow-up :Long running SQL Problem?

[Stephane Faroult]

"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: Long running SQL Problem?

[Jamadagni, Rajendra]

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: Long running SQL Problem?

[Kimberly Smith]

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-
>  ]
> 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  >
> 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
>   _
>
>   _
>
> 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: 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 REMO

RE: RE: Long running SQL Problem?

[Stephane Faroult]

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?

[Greg Moore]

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-
>  ]
> 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  >
> 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
>   _
>
>   _
>
> 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: 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).

RE: Long running SQL Problem?

[CHAN Chor Ling Catherine (CSC)]

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- 
 ] 
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  >
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 
  _  

  _  

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: 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? [stupid alternative to NOT IN]

[Eric D. Pierce]

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: Long running SQL Problem?

[tday6]

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 

 <[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?

[Denham Eva]

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?

[Nicoll, Iain (Calanais)]

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
 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?

[CHAN Chor Ling Catherine (CSC)]

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
 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?

[Jack van Zanen]

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, 

Long running SQL Problem?

[Denham Eva]

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 

Thanks: simple sql problem - 1 more problem

[Viraj Luthra]

 Thanks to all who responded, Appreciate it.

The grok approach was not working so had to go with the following one.

Now just 1 more problem is that how I do I set the environment, say if I want to do 
set head off, in the following command can I do that?

The following command I had to do :-

echo "SELECT a.username,substr(sql_text,1,100) as \"SQL 
TEXT\",substr(sql_text,101,250) as \"Second Text\",substr(sql_text,601,250) as \"Third 
Text\",substr(sql_text,851,250) as \"Fourth Text \",substr(sql_text,1101,250) as 
\"Fifth Text \" FROM sys.dba_users a, v\$session, v\$sqlarea where 
parsing_user_id=user_id AND address=sql_address(+) and sid = #sid ; 
" | $ORACLE_HOME/bin/sqlplus  #username/#passwd@$ORACLE_SID

--

On Tue, 04 Dec 2001 16:06:01  
 Reardon, Bruce (CALBBAY) wrote:
>After testing prompted by Jared's reply, you can use 1 of the 2 alternatives
>from Windows.
>
>That is, under windows the following works:
>echo select * from dual; | sqlplus scott/tiger@abcd
>
>I often use this type of syntax under NT to (say) log the time as below, but
>had never thought to try it with sqlplus.
>eg time example
>C:\>echo.|time|find "current" /i
>The current time is: 10:54:24.94
>
>Hope this helps someone,
>Bruce Reardon
>
>-Original Message-
>Sent: Wednesday, 5 December 2001 3:25
>
>As Scott said, in Unix you can use Input Redirection.   If you are in
>windows, you could always build the command as a .sql file and use the
>
>sqlplus -s scott/tiger @myfile.sql
>
>Command.
>
>-Original Message-
>Sent: Tuesday, December 04, 2001 10:01 AM
>
>From the unix command line you can run:
>
>sqlplus -s scott/tiger << EOF
>select * from emp;
>EOF
>
>
>Viraj Luthra wrote:
>> 
>> Hello all,
>> 
>> Why cannot I do :-
>> 
>> sqlplus -s scott/tiger select * from emp;
>> 
>> or sqlplus -s scott/tiger "select * from emp;"
>> 
>> Both the cases I get :-
>> 
>> Usage: SQLPLUS [] [[/] [@]]
>>[@ [] [] ...]
>> where  ::= { -s | -? }
>> -s for silent mode and -? to obtain version number
>> 
>> What is wrong above? I need to run the query from command line. How do I
>modify this :-
>> 
>> sqlplus -s scott/tiger select * from emp;
>> 
>> Rgds,
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Reardon, Bruce (CALBBAY)
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Reardon, Bruce (CALBBAY)]

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

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

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

Hope this helps someone,
Bruce Reardon

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

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

sqlplus -s scott/tiger @myfile.sql

Command.

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

>From the unix command line you can run:

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


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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Kevin Lange]

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

sqlplus -s scott/tiger @myfile.sql

Command.

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


>From the unix command line you can run:

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



Viraj Luthra wrote:
> 
> Hello all,
> 
> Why cannot I do :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> or sqlplus -s scott/tiger "select * from emp;"
> 
> Both the cases I get :-
> 
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }
> -s for silent mode and -? to obtain version number
> 
> What is wrong above? I need to run the query from command line. How do I
modify this :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> Rgds,
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Viraj Luthra
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Shafer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: simple sql problem

[Scott Shafer]

>From the unix command line you can run:

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



Viraj Luthra wrote:
> 
> Hello all,
> 
> Why cannot I do :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> or sqlplus -s scott/tiger "select * from emp;"
> 
> Both the cases I get :-
> 
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }
> -s for silent mode and -? to obtain version number
> 
> What is wrong above? I need to run the query from command line. How do I modify this 
>:-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> Rgds,
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Viraj Luthra
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Shafer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[DENNIS WILLIAMS]

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

Jared - Thanks for passing along this technique.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


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


 Jared,

Using the 2nd option:-

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

PL/SQL Release 8.1.5.0.0 - Production




But if I use the 1st option :-

eg

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

I get this error:-

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

ERROR at line 3:

ORA-00942: table or view does not exist

 


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

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

What am I doing wrong?

Rgds,
--

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
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


2001-12-04

Thread
BINAY . KUMAR


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 [] [[/] [@]]
   [@ [] [] ...]
where  ::= { -s | -? }
-s for silent mode and -? to obtain version number


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

sqlplus -s scott/tiger select * from emp;


Rgds,



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




---

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


2001-12-04

Thread
Tatireddy, Shrinivas (MED, Keane)


b'coz you have to type select * from v\$instance;"

you are passing arguments from shell. You have to escape $ like \$

HTH

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


 Even using  like this:-

echo "select * from v$instance;" | sqlplus system/manager

gives me the error :-

SQL> select * from v  
  *   
ERROR at line 1:  
ORA-00942: table or view does not exist   

Though I can do the same sql statement from inside sqlplus with ease, so
why does it give table or view does not exist?

I am confused ..

rgds,
--

On Mon, 03 Dec 2001 21:50:17  
 Ross Collado wrote:
>I don't believe SQLPlus will let you run a SQL statement in the command
line
>itself.  As you can see in Usage:, it doesn't have an option for this.
>However, if you insist on running SQL statements in the command line
itself,
>you can probably do something like (only in Unix):
>
>sqlplus -s scott/tiger << !
>select * from emp;
>!
>
>or 
>
>save your select * from emp; to a sql file and do :
>sqlplus -s scott/tiger @myfile.sql
>
>hth
>Ross
>
>> -Original Message-
>> From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
>> Sent: Tuesday, 4 December 2001 16:11
>> To: Multiple recipients of list ORACLE-L
>> Subject: simple sql problem
>> 
>> 
>> Hello all,
>> 
>> Why cannot I do :-
>> 
>> sqlplus -s scott/tiger select * from emp;
>> 
>> or sqlplus -s scott/tiger "select * from emp;"
>> 
>> Both the cases I get :-
>> 
>> Usage: SQLPLUS [] [[/] [@]]
>>[@ [] [] ...]
>> where  ::= { -s | -? }   
>> -s for silent mode and -? to obtain version number   
>> 
>> 
>> What is wrong above? I need to run the query from command 
>> line. How do I modify this :-
>> 
>> sqlplus -s scott/tiger select * from emp;
>> 
>> 
>> Rgds,
>> 
>> 
>> 
>> -- 
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> -- 
>> Author: Viraj Luthra
>>   INET: [EMAIL PROTECTED]
>> 
>> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>> San Diego, California-- Public Internet access / Mailing
Lists
>> 
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from).  You may
>> also send the HELP command for other information (like subscribing).
>> 
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Ross Collado
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tatireddy, Shrinivas (MED, Keane)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






RE: simple sql problem


2001-12-04

Thread
hemantchitale



If you are running it on Unix, you must "escape" the "$" in "v$instance".
thus, you would do
echo "select * from v\$instance;" | sqlplus system/manager

Note the "\" key before the "$".
Hemant


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

Please respond to ORACLE-L
   

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

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

 Subject: RE: simple sql problem   

   

   

   






 Even using  like this:-

echo "select * from v$instance;" | sqlplus system/manager

gives me the error :-

SQL> select * from v
  *
ERROR at line 1:
ORA-00942: table or view does not exist

Though I can do the same sql statement from inside sqlplus with ease, so
why does it give table or view does not exist?

I am confused ..

rgds,
--

On Mon, 03 Dec 2001 21:50:17
 Ross Collado wrote:
>I don't believe SQLPlus will let you run a SQL statement in the command
line
>itself.  As you can see in Usage:, it doesn't have an option for this.
>However, if you insist on running SQL statements in the command line
itself,
>you can probably do something like (only in Unix):
>
>sqlplus -s scott/tiger << !
>select * from emp;
>!
>
>or
>
>save your select * from emp; to a sql file and do :
>sqlplus -s scott/tiger @myfile.sql
>
>hth
>Ross
>
>> -Original Message-
>> From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
>> Sent: Tuesday, 4 December 2001 16:11
>> To: Multiple recipients of list ORACLE-L
>> Subject: simple sql problem
>>
>>
>> Hello all,
>>
>> Why cannot I do :-
>>
>> sqlplus -s scott/tiger select * from emp;
>>
>> or sqlplus -s scott/tiger "select * from emp;"
>>
>> Both the cases I get :-
>>
>> Usage: SQLPLUS [] [[/] [@]]
>>[@ [] [] ...]
>> where  ::= { -s | -? }
>> -s for silent mode and -? to obtain version number
>>
>>
>> What is wrong above? I need to run the query from command
>> line. How do I modify this :-
>>
>> sqlplus -s scott/tiger select * from emp;
>>
>>
>> Rgds,
>>
>>
>>
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> --
>> Author: Viraj Luthra
>>   INET: [EMAIL PROTECTED]
>>
>> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>> San Diego, California-- Public Internet access / Mailing Lists
>> 
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from).  You may
>> also send the HELP command for other information (like subscribing).
>>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Ross Collado
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the mes



RE: simple sql problem


2001-12-04

Thread
Viraj Luthra


 Even using  like this:-

echo "select * from v$instance;" | sqlplus system/manager

gives me the error :-

SQL> select * from v  
  *   
ERROR at line 1:  
ORA-00942: table or view does not exist   

Though I can do the same sql statement from inside sqlplus with ease, so why does it 
give table or view does not exist?

I am confused ..

rgds,
--

On Mon, 03 Dec 2001 21:50:17  
 Ross Collado wrote:
>I don't believe SQLPlus will let you run a SQL statement in the command line
>itself.  As you can see in Usage:, it doesn't have an option for this.
>However, if you insist on running SQL statements in the command line itself,
>you can probably do something like (only in Unix):
>
>sqlplus -s scott/tiger << !
>select * from emp;
>!
>
>or 
>
>save your select * from emp; to a sql file and do :
>sqlplus -s scott/tiger @myfile.sql
>
>hth
>Ross
>
>> -Original Message-
>> From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
>> Sent: Tuesday, 4 December 2001 16:11
>> To: Multiple recipients of list ORACLE-L
>> Subject: simple sql problem
>> 
>> 
>> Hello all,
>> 
>> Why cannot I do :-
>> 
>> sqlplus -s scott/tiger select * from emp;
>> 
>> or sqlplus -s scott/tiger "select * from emp;"
>> 
>> Both the cases I get :-
>> 
>> Usage: SQLPLUS [] [[/] [@]]
>>[@ [] [] ...]
>> where  ::= { -s | -? }   
>> -s for silent mode and -? to obtain version number   
>> 
>> 
>> What is wrong above? I need to run the query from command 
>> line. How do I modify this :-
>> 
>> sqlplus -s scott/tiger select * from emp;
>> 
>> 
>> Rgds,
>> 
>> 
>> 
>> -- 
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> -- 
>> Author: Viraj Luthra
>>   INET: [EMAIL PROTECTED]
>> 
>> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>> San Diego, California-- Public Internet access / Mailing Lists
>> 
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from).  You may
>> also send the HELP command for other information (like subscribing).
>> 
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Ross Collado
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






RE: simple sql problem


2001-12-03

Thread
Nirmal Kumar Muthu Kumaran

Title: RE: simple sql problem





Hi...


You cann't run sql statement in command line directly However you can in case of script file.


Make the query as a script file.  Then run it on SQLPLUS.


-- c:\test.sql
-
select * from emp
/
exit
/
--


c:\> sqlplus -s scott/tiger@oranir @c:\test


This will work.


HTH.



Rgds,
Nirmal,

-Original Message-
From:   Viraj Luthra [SMTP:[EMAIL PROTECTED]]
Sent:   Tuesday, December 04, 2001 8:11 AM
To: Multiple recipients of list ORACLE-L
Subject:    simple sql problem


Hello all,


Why cannot I do :-


sqlplus -s scott/tiger select * from emp;


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


Both the cases I get :-


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



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


sqlplus -s scott/tiger select * from emp;



Rgds,




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).








RE: simple sql problem


2001-12-03

Thread
Tatireddy, Shrinivas (MED, Keane)


try this


$ echo "select * from emp;" | sqlplus scott/tiger

srinivas

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 04, 2001 2:31 AM
To: Multiple recipients of list ORACLE-L


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



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

Please respond to ORACLE-L
 

 To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>   
 cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group)

 Subject: simple sql problem

 

 

 






Hello all,

Why cannot I do :-

sqlplus -s scott/tiger select * from emp;

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

Both the cases I get :-

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


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

sqlplus -s scott/tiger select * from emp;


Rgds,



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




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

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tatireddy, Shrinivas (MED, Keane)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






Re: simple sql problem


2001-12-03

Thread
Viraj Luthra


 Jared,

Using the 2nd option:-

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



But if I use the 1st option :-

eg

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

I get this error:-

SQL>   234  FROM dba_users a, v, v where parsing_user_id=user_id AND 
address=sql_address(+)  
 * 
  
ERROR at line 3:   
  
ORA-00942: table or view does not exist
  
   
  

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

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

What am I doing wrong?

Rgds,
--

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: simple sql problem

[Jared Still]

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

Try these:

1.

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

2.

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Ross Collado]

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

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

or 

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

hth
Ross

> -Original Message-
> From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 4 December 2001 16:11
> To: Multiple recipients of list ORACLE-L
> Subject: simple sql problem
> 
> 
> Hello all,
> 
> Why cannot I do :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> or sqlplus -s scott/tiger "select * from emp;"
> 
> Both the cases I get :-
> 
> Usage: SQLPLUS [] [[/] [@]]
>[@ [] [] ...]
> where  ::= { -s | -? }   
> -s for silent mode and -? to obtain version number   
> 
> 
> What is wrong above? I need to run the query from command 
> line. How do I modify this :-
> 
> sqlplus -s scott/tiger select * from emp;
> 
> 
> Rgds,
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Viraj Luthra
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ross Collado
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: simple sql problem

[hemantchitale]

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



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

Please respond to ORACLE-L
   

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

 cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group)
            
 Subject: simple sql problem   

   

   

   






Hello all,

Why cannot I do :-

sqlplus -s scott/tiger select * from emp;

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

Both the cases I get :-

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


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

sqlplus -s scott/tiger select * from emp;


Rgds,



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




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

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

simple sql problem

[Viraj Luthra]

Hello all,

Why cannot I do :-

sqlplus -s scott/tiger select * from emp;

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

Both the cases I get :-

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


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

sqlplus -s scott/tiger select * from emp;


Rgds,



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: dynamic sql problem

[Thomas, Kevin]

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).

Re: dynamic sql problem

[DBarbour]

Which is the problem statement?  I didn't see the bold.



David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002


   
  
Harvinder Singh
  
  
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).

dynamic sql problem

[Harvinder Singh]

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).

SOLVED: simple sql problem

[Viraj Luthra]

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

[Pulikkol Kumar]

Hi
 This will work out 

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





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pulikkol Kumar
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Swapna_Chinnagangannagari]

Title: RE: simple sql problem





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


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



OR


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


    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pulikkol Kumar
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Jacques Kilchoer]

Title: RE: simple sql problem





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


Here is a starting point to help you.


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


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


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


7 rows selected.


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

RE: simple sql problem

[Ramon Estevez]

Use Decode function

Ramon Estevez 



*809-565-3121 x 225

 

* [EMAIL PROTECTED]

 



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


Hello all,

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

select x+y from blah

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

How do I do this?

rgds,

raja


Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ramon Estevez
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: simple sql problem

[Jon Walthour]

Raja:

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

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

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


> Hello all,
> 
> If I have a situation, where I have the following:-
> 
> select x+y from blah
> 
> and if x+y > 1, eg 5 then the output can be 5,
> but if x+y < 1, eg, .92, then I need the output as 0.92.
> 
> How do I do this?
> 
> rgds,
> 
> raja
> 
> 
> Get 250 color business cards for FREE!
> http://businesscards.lycos.com/vp/fastpath/
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Viraj Luthra
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jon Walthour
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Pulikkol Kumar]

OR

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























-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pulikkol Kumar
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Amar Kumar Padhi]

Title: RE: simple sql problem



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

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

On Mon, 30 Jul 2001 22:00:23  
 Amar Kumar Padhi wrote: 
>I believe what you meant is if it is less 
than one then the output should be >1. >if so: > >select (case 
when x + y > 1 then x + Y > 
else  1 >    end) from blah; 
> > >-Original 
Message- >Sent: Tuesday, July 31, 
2001 9:10 AM >To: Multiple recipients 
of list ORACLE-L > > >Hello 
all, > >If I have a situation, where I have the following:- 
> >select x+y from blah > >and if x+y > 1, eg 5 
then the output can be 5, >but if x+y 
< 1, eg, .92, then I need the output as 0.92. > >How do I do this? 
> >rgds, > >raja > 
> >Get 
250 color business cards for FREE! >http://businesscards.lycos.com/vp/fastpath/ 
>-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: 
Viraj Luthra >  INET: 
[EMAIL PROTECTED] > >Fat City Network Services    -- (858) 
538-5051  FAX: (858) 538-5051 >San Diego, California    
-- Public Internet access / Mailing Lists > 
>To REMOVE yourself from this mailing list, 
send an E-Mail message >to: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
>the message BODY, include a line containing: 
UNSUB ORACLE-L >(or the name of 
mailing list you want to be removed from).  You may >also send the HELP command for other information (like 
subscribing). > 
Get 250 color business cards for FREE! 
http://businesscards.lycos.com/vp/fastpath/ 
-- Please see 
the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj 
Luthra   INET: 
[EMAIL PROTECTED] 
Fat City Network Services    -- 
(858) 538-5051  FAX: (858) 538-5051 San Diego, California    -- 
Public Internet access / Mailing Lists  
To REMOVE yourself from this mailing list, send 
an E-Mail message to: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: 
UNSUB ORACLE-L (or the name of mailing 
list you want to be removed from).  You may also send the HELP command for other information (like 
subscribing). 

RE: simple sql problem

[Swapna_Chinnagangannagari]

Title: RE: simple sql problem





Hello Raja,


You can try this way


1)select x+y sum1 from blah


2)col sum1 format 09.99


3)select x+y sum1 from blah


rgds
swapna


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


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

therefore, when i have, 


select x+y from blah


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


Any comments


rgds,


raja
--


On Mon, 30 Jul 2001 22:00:23  
 Amar Kumar Padhi wrote:
>I believe what you meant is if it is less than one then the output should be
>1.
>if so:
>
>select (case when x + y > 1 then x + Y
> else  1
>    end) from blah;
>
>
>-Original Message-
>Sent: Tuesday, July 31, 2001 9:10 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello all,
>
>If I have a situation, where I have the following:-
>
>select x+y from blah
>
>and if x+y > 1, eg 5 then the output can be 5,
>but if x+y < 1, eg, .92, then I need the output as 0.92.
>
>How do I do this?
>
>rgds,
>
>raja
>
>
>Get 250 color business cards for FREE!
>http://businesscards.lycos.com/vp/fastpath/
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Viraj Luthra
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California    -- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>



Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Viraj Luthra]

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

therefore, when i have, 

select x+y from blah

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

Any comments

rgds,

raja
--

On Mon, 30 Jul 2001 22:00:23  
 Amar Kumar Padhi wrote:
>I believe what you meant is if it is less than one then the output should be
>1.
>if so:
>
>select (case when x + y > 1 then x + Y
> else  1
>end) from blah;
>
>
>-Original Message-
>Sent: Tuesday, July 31, 2001 9:10 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello all,
>
>If I have a situation, where I have the following:-
>
>select x+y from blah
>
>and if x+y > 1, eg 5 then the output can be 5,
>but if x+y < 1, eg, .92, then I need the output as 0.92.
>
>How do I do this?
>
>rgds,
>
>raja
>
>
>Get 250 color business cards for FREE!
>http://businesscards.lycos.com/vp/fastpath/
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Viraj Luthra
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>


Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: simple sql problem

[Amar Kumar Padhi]

Title: RE: simple sql problem





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


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



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



Hello all,


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


select x+y from blah


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


How do I do this?


rgds,


raja



Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

simple sql problem

[Viraj Luthra]

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..

[Saurabh Sharma]

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 table are 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

Re: pl/sql problem

[Diana_Duncan]

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

   
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: pl/sql problem

[Gogala, Mladen]

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

[Kevin Lange]

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).

pl/sql problem

[Harvinder Singh]

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: SQL problem: retrieve child records if existent

[Stephane Faroult]

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).

SQL problem: retrieve child records if existent

[Helmut Daiminger]

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]

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..

[N. SARAVANA KUMAR]

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).

Re: SQL PROBLEM for duplicate rows..

[Burçin Üstün Kýlýç]

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..

[Burçin Üstün Kýlýç]

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).

SQL PROBLEM for duplicate rows..

[N. SARAVANA KUMAR]

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

[Mohan, Karthik (GEP)]

Title: MS access



 I do not seem to have exactly understood 
u r problem..but try this anyway
 
select  count(*) from    
awhere   l_id in 
(select b.bl_idfrom a,bwhere a.o_id = 
17and a.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  --    
--    17NULL  NULL 
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 
  (
  select 1 from table_ a where ord_id 
  = 17 
  union 
  
  select 1 from table_b where ord_id = 
  17)
  /
   
   
   Hi,
   
  Favour me in suggest a hint in writing a 
  sql for following scenaio:
   
  Table A has 2 columns ord_id and 
  line_id
   
  example rows are:
   
  ord_id 
  line_id
  17    
  null
  null  42
   
  Table  B 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

[Niyi Olajide]

Title: MS access



Can you try 
this:
 
select count (1) from 
(
select 1 from table_ a where ord_id 
= 17 
union 

select 1 from table_b where ord_id = 
17)
/
 
 
 Hi,
 
Favour 
me in suggest a hint in writing a sql for following 
scenaio:
 
Table 
A has 2 columns ord_id and line_id
 
example rows are:
 
ord_id 
line_id
17    
null
null  42
 
Table  B 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

[Diana_Duncan]

Harvinder,

I think, if I understand the problem, that the following will work:

select ord_id, count(*)
from (select nvl(a.ord_id, b.ord_id) ord_id
 from TableA a, TableB b
 where b.line_id = a.line_id
 union all
 select ord_id
 from TableA
 where line_id is null
)
group by ord_id
/

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

   
aTech.com>   cc:   

Sent by: Fax to:   

[EMAIL PROTECTED] Subject:     SQL PROBLEM  

   

   

05/24/2001 02:46 PM

Please respond to  

ORACLE-L   

   

   





Hi,

Favour me in suggest a hint in writing a sql for following scenaio:

Table A has 2 columns ord_id and line_id

example rows are:

ord_id line_id
17null
null  42

Table  B has columns ord_id and line_id

ord_id line_id
1742
1743
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



-- 
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).

SQL PROBLEM

[Harvinder Singh]

Title: MS access



Hi,
 
Favour 
me in suggest a hint in writing a sql for following 
scenaio:
 
Table 
A has 2 columns ord_id and line_id
 
example rows are:
 
ord_id 
line_id
17    
null
null  42
 
Table  B 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