RE: SQL PROBLEM

From: Mohan, Karthik (GEP) <Karthik.Mohan_at_gepex.ge.com>
Date: Fri, 25 May 2001 01:45:52 -0700
Message-ID: <F001.0030EAC5.20010525014533@fatcity.com>

<SPAN
class=70394708-25052001> I do not seem to have exactly understood u r problem..........but try this anyway <SPAN
class=70394708-25052001> 
<SPAN
class=70394708-25052001>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)
<SPAN
class=70394708-25052001> 
<SPAN
class=70394708-25052001> 
COUNT(*)----------        
2
 
<SPAN
class=70394708-25052001>1 row selected.
<SPAN
class=70394708-25052001> 
The
table data is as listed below ....
<SPAN
class=70394708-25052001> 
SQL> select * from a;
 
     
O_ID       L_ID<SPAN
class=70394708-25052001>  ----------<SPAN class=70394708-25052001>       
----------        17<SPAN
class=70394708-25052001>            NULL  <SPAN

class=70394708-25052001>NULL                 

42
 
2 rows selected.
 
SQL> select * from b;
 
    
BO_ID      BL_ID---------- <SPAN
class=70394708-25052001>        
----------       

17         
42        
17         
43        
17         44

 
3 rows selected.

Regards,
Karthik
Mohan
TCS Consultant at GEP
(HK)
DialComm 3310962
ISD : 852-26290962
 

<FONT face="Times New Roman"

  size=2>-----Original Message-----From: Niyi Olajide   [mailto:N.OLAJIDE_at_CGIAR.ORG]Sent: Friday, May 25, 2001 4:36   PMTo: Multiple recipients of list ORACLE-LSubject: RE:   SQL PROBLEM
<FONT

  size=2>Can you try
  this:
<FONT

  size=2><SPAN
  class=450062506-25052001> 
<FONT

  size=2>select count (1) from
  (
<FONT

  size=2>select 1 from table_ a where ord_id   = 17 
<FONT

  size=2>union   

<FONT

  size=2>select 1 from table_b where ord_id =   17)
<FONT

  size=2><SPAN
  class=450062506-25052001>/
<FONT

  size=2><SPAN
  class=450062506-25052001> 
<FONT

  size=2><SPAN
  class=450062506-25052001> 
<FONT

  size=2><SPAN
  class=450062506-25052001> Hi,
   
<SPAN

  class=500143917-24052001>Favour me in suggest a hint in writing a   sql for following scenaio:
<SPAN

  class=500143917-24052001> 
<SPAN

  class=500143917-24052001>Table A has 2 columns ord_id and   line_id
<SPAN

  class=500143917-24052001> 
<SPAN

  class=500143917-24052001>example rows are:
<SPAN

  class=500143917-24052001> 
<SPAN

  class=500143917-24052001>ord_id           line_id
<SPAN

  class=500143917-24052001>17                  null
<SPAN

  class=500143917-24052001>null              42
<SPAN

  class=500143917-24052001> 
<SPAN

  class=500143917-24052001>Table  B has columns ord_id and   line_id
<SPAN

  class=500143917-24052001> 
<SPAN

  class=500143917-24052001>ord_id           line_id
<SPAN

  class=500143917-24052001>17                  42
<SPAN

  class=500143917-24052001>17                  43
<SPAN

  class=500143917-24052001>17                44

<SPAN

  class=500143917-24052001> 
  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
<SPAN

  class=500143917-24052001>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   

<SPAN

  class=500143917-24052001>TABLE B and if it is  for this particular order   ...add that also in
<SPAN

  class=500143917-24052001>count(*).............   I
  know scenario is little fuzzy sorry for it..but if u got my point   

<SPAN

  class=500143917-24052001>kindly respond.
<SPAN

  class=500143917-24052001> 
<SPAN

  class=500143917-24052001>Thanks
<SPAN

  class=500143917-24052001>Harvinder Received on Fri May 25 2001 - 03:45:52 CDT