Home » SQL & PL/SQL » SQL & PL/SQL » qry help plz ... (merged similar topics by same user)
qry help plz ... (merged similar topics by same user) [message #284021] Wed, 28 November 2007 10:38 Go to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
please help me to solve a problem ..

oracle version 9i ..
i have two tables T1_master and T1_child
for each entry entry in master table child has multiple entries.
t1_master
col1 | col2 | col3 
-----|------|-------
 1   |  25  |  Y
 2   |  30  |  N
t1_child
cola|  colb | colc |cold
----|-------|------|-----
1   |	0   | 5    | R
1   |	10  | 15   | I
1   |	15  | 10   | R
2   |   15  | 20   | I
2   |	15  | 10   | R



i need to join both tables and i need
to get sum of colb only if any of the value in colb has 0
ie, ineed to return sum(colb) where cola =1 since colb has '0' for cola=1 .

thanks in advance..

[Updated on: Wed, 28 November 2007 10:54]

Report message to a moderator

Re: help .. [message #284028 is a reply to message #284021] Wed, 28 November 2007 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Post what you already tried.

Regards
Michel
query help plz ... [message #284044 is a reply to message #284021] Wed, 28 November 2007 11:13 Go to previous messageGo to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
I have aquery like this

select a.val1 ,b.val3,a.val4
from tab1 a ,tab2 b 
where a.val1=b.val1 
and b.val5  in coalesce(
(select column1 from tab3 where column3='AA'),b.val5)

my idea is if "(select column1 from tab3 where column3='AA')"
returns null
then i have to take all values of b.val5 else b.val5 should
satisfy the values from column1.
if i code like this i'm getting single fetch returns
too many rows .is there any way to rewrite this..

we are using both 9i and 10g..

thanks in advance ...
Re: query help plz ... [message #284155 is a reply to message #284044] Thu, 29 November 2007 01:04 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
may be you can try this.

code not tested
select a.val1 ,b.val3,a.val4
from tab1 a ,tab2 b 
where a.val1=b.val1 
and b.val5  in 
(select coalesce(column1,b.val5) from tab3 where column3='AA')


regards,

[Updated on: Thu, 29 November 2007 01:05]

Report message to a moderator

Re: qry help plz ... (merged similar topics by same user) [message #284174 is a reply to message #284021] Thu, 29 November 2007 01:39 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SELECT (SELECT SUM (t1.colb)
          FROM t1_child t1)
  FROM t1_master mast, t1_child CHILD
 WHERE mast.col1 = CHILD.cola AND EXISTS (SELECT 1
                                            FROM t1_child
                                           WHERE CHILD.colb = '0')


Regards,
Kiran.
Re: qry help plz ... (merged similar topics by same user) [message #284179 is a reply to message #284174] Thu, 29 November 2007 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULL value and non existent row is not the same thing.

Regards
Michel
Re: qry help plz ... (merged similar topics by same user) [message #284191 is a reply to message #284021] Thu, 29 November 2007 02:42 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ kiran :

Exists part doesn't have correlation with Main Query. Moreover If Joining with Colb with value 0 may result into the set of rows with Colb=0 (Other rows will be ignored). So I afraid it may not work well.

I suggest ,

  SELECT  mast.col1,SUM (CH.colb) 
  FROM t1_master mast, t1_child CH 
 WHERE mast.col1 = CH .cola AND  EXISTS (SELECT 1  FROM t1_child CH1  
 	   			                   WHERE CH.cola =CH1.cola
					 	   AND	CH1.colb =0 )
 group by mast.col1


Thumbs Up
Rajuvan.

[Updated on: Thu, 29 November 2007 02:46]

Report message to a moderator

Previous Topic: How To Sort Data Without Using Order BY Clause
Next Topic: ORA-00054: resource busy and acquire with NOWAIT specified(Error)
Goto Forum:
  


Current Time: Fri Dec 09 21:19:25 CST 2016

Total time taken to generate the page: 0.22615 seconds