Home » SQL & PL/SQL » SQL & PL/SQL » SQL query do not show all records ...
SQL query do not show all records ... [message #238269] Thu, 17 May 2007 04:47 Go to next message
adil_akbar
Messages: 8
Registered: May 2007
Location: Pakistan
Junior Member
Dear Sir,
I am unable to extract records which I want.
I have used left outer join to show matched &
unmatched records, but it shows only matched records.

In a table, some codes which do not appear are before
Deccember 2006 and do not appear in Jan to March 2007
in Tdate column.

pls help me.

select a.ccode,sum( a.debit)- sum(a.credit)
ob,sum(b.debit) ,sum(b.credit) from trans1 a,
trans1 b
where a.ccode = b.ccode(+)
and a.tdate < '01-Apr-07' and
b.tdate between '01-Jan-07' and '30-Mar-07'
group by a.ccode
Laughing
Re: SQL query do not show all records ... [message #238271 is a reply to message #238269] Thu, 17 May 2007 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Problem 1) Unmatched records from table TRANS1 would have all their values null. You are explicitly asking for records with a value in the column TDATE :
and b.tdate between '01-Jan-07' and '30-Mar-07'

Rewrite that line as:
and b.tdate(+) between '01-Jan-07' and '30-Mar-07'

Problem 2)
You are using strings when you should be using dates.
This is just storing up a world of trouble for later.
Everywhere you use a strig literal like '01-Apr-07', you should use
to_date('01-Apr-07','dd-mon-rr')

Re: SQL query do not show all records ... [message #238274 is a reply to message #238271] Thu, 17 May 2007 05:02 Go to previous messageGo to next message
adil_akbar
Messages: 8
Registered: May 2007
Location: Pakistan
Junior Member
sir,
i have modified the query but the same result,
it did not show all records ...

Pls reply Embarassed
Re: SQL query do not show all records ... [message #238277 is a reply to message #238274] Thu, 17 May 2007 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the query... formatted.

Regards
Michel
Re: SQL query do not show all records ... [message #238338 is a reply to message #238269] Thu, 17 May 2007 07:54 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
adil_akbar wrote on Thu, 17 May 2007 05:47

and a.tdate < '01-Apr-07' and
b.tdate between '01-Jan-07' and '30-Mar-07'




You can never get any results from a query like this, since any value that is less than the string "01-A" cannot possibly be greater than the string "01-J", at least not in the english alphabet, where "A" is the fist letter and "J" is the 10th letter.
Previous Topic: functions
Next Topic: Cummulative selection
Goto Forum:
  


Current Time: Fri Dec 02 18:54:26 CST 2016

Total time taken to generate the page: 0.17934 seconds