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
<Harvinder.Singh@Metr To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
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
17 null
null 42
Table B has columns ord_id and line_id
ord_id line_id
17 42
17 43
17 44
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).