Home » SQL & PL/SQL » SQL & PL/SQL » sql query shows matched but not ummatched records
sql query shows matched but not ummatched records [message #238917] Mon, 21 May 2007 00:34 Go to next message
adil_akbar
Messages: 8
Registered: May 2007
Location: Pakistan
Junior Member
dear sir, I want to show all matched & unmatched record from table1 (trans_detail1 a). I have used left outer join but invail.
pls solve the query.

select a.ccode,
c.descp,
nvl(sum(b.ob),0) ob,
nvl(sum(a.debit),0) debit,nvl(sum(a.credit),0) credit,
nvl(sum(b.ob),0)+nvl(sum(a.debit),0)-nvl(sum(a.credit),0) cb
from trans_detail1 a,chart c,
select ccode, nvl(sum(debit),0)-nvl(sum(credit),0) ob from trans_detail1
where tdate < '01-Apr-07' group by ccode) b
where a.tdate(+) between '01-JAN-07' and '30-Apr-07'
and a.ccode(+) = b.ccode
and a.ccode = c.code
group by a.ccode,c.descp

Adil
Re: sql query shows matched but not ummatched records [message #238924 is a reply to message #238917] Mon, 21 May 2007 00:47 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
you have put (+) on wrong side and plus compare date with date

select a.ccode, 
c.descp,
nvl(sum(b.ob),0) ob,
nvl(sum(a.debit),0) debit,nvl(sum(a.credit),0) credit,
nvl(sum(b.ob),0)+nvl(sum(a.debit),0)-nvl(sum(a.credit),0) cb
from trans_detail1 a,chart c,
(select ccode, nvl(sum(debit),0)-nvl(sum(credit),0) ob from trans_detail1
where tdate < to_date('01-Apr-2007','dd-Mon-yyyy') group by ccode) b
where a.tdate between to_date('01-JAN-2007','dd-Mon-yyyy') and to_date('30-Apr-2007','dd-Mon-yyyy')
and a.ccode = b.ccode (+)
and a.ccode = c.code (+)
group by a.ccode,c.descp



[Updated on: Mon, 21 May 2007 01:01]

Report message to a moderator

Re: sql query shows matched but not ummatched records [message #238928 is a reply to message #238924] Mon, 21 May 2007 00:56 Go to previous messageGo to next message
adil_akbar
Messages: 8
Registered: May 2007
Location: Pakistan
Junior Member
still records from table1 are not shown.

select a.ccode,
c.descp,
nvl(sum(b.ob),0) ob,
nvl(sum(a.debit),0) debit,nvl(sum(a.credit),0) credit,
nvl(sum(b.ob),0)+nvl(sum(a.debit),0)-nvl(sum(a.credit),0) cb
from trans_detail1 a,chart c,
(select ccode, nvl(sum(debit),0)-nvl(sum(credit),0) ob from trans_detail1
where tdate < to_date('01-Apr-2007','dd-Mon-yyyy') group by ccode) b
where a.tdate between to_date('01-JAN-2007','dd-Mon-yyyy') and to_date('30-Apr-2007','dd-Mon-yyyy')
and a.ccode = b.ccode (+)
and a.ccode = c.code
group by a.ccode,c.descp
/
Re: sql query shows matched but not ummatched records [message #238932 is a reply to message #238928] Mon, 21 May 2007 00:59 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
you missed (+) in the last join condition

and a.ccode = c.code (+)

Re: sql query shows matched but not ummatched records [message #238934 is a reply to message #238932] Mon, 21 May 2007 01:01 Go to previous messageGo to next message
adil_akbar
Messages: 8
Registered: May 2007
Location: Pakistan
Junior Member
it still does not work.

select a.ccode,
c.descp,
nvl(sum(b.ob),0) ob,
nvl(sum(a.debit),0) debit,nvl(sum(a.credit),0) credit,
nvl(sum(b.ob),0)+nvl(sum(a.debit),0)-nvl(sum(a.credit),0) cb
from trans_detail1 a,chart c,
(select ccode, nvl(sum(debit),0)-nvl(sum(credit),0) ob from trans_detail1
where tdate < to_date('01-Apr-2007','dd-Mon-yyyy') group by ccode) b
where a.tdate between to_date('01-JAN-2007','dd-Mon-yyyy') and to_date('30-Apr-2007','dd-Mon-yyyy')
and a.ccode = b.ccode (+)
and a.ccode = c.code(+)
group by a.ccode,c.descp

Re: sql query shows matched but not ummatched records [message #238937 is a reply to message #238934] Mon, 21 May 2007 01:03 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
need to have create table and insert scripts alongwith result you see for the query and expected results for us to know what is happening?
Previous Topic: Temp table and cursor (merged topics)
Next Topic: Two tables join query.
Goto Forum:
  


Current Time: Sat Dec 03 10:11:35 CST 2016

Total time taken to generate the page: 0.10863 seconds