Home » SQL & PL/SQL » SQL & PL/SQL » Null Value during the Execution of Query (Windows XP)
Null Value during the Execution of Query [message #317063] Tue, 29 April 2008 06:43 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

When I try to take the data's using this query. They were null value for the document. Pls check and give me which point of query is wrong.

select
a.documentno,
a.documentdate,
a.particulars,
sum(case when glcode='90508' then cl_bal end) "90508",
sum(case when glcode='90509' then cl_bal end) "90509",
sum(case when glcode='64840' then cl_bal end) "64840"
from
(
(
select
a.documentno,
a.documentdate,
a.glcode,
nvl(a.debitamount,0) - nvl(a.creditamount,0) cl_bal,
a.particulars
from
ledger a
where
a.unitid=2 and substr(a.documentno,2,3) in ('221','212') and
a.documentdate between '01-apr-07' and '31-Mar-08' and
a.glcode in
(
'90508','90509'))
union
(
select
a.documentno,
a.documentdate,
a.glcode,
(nvl(a.creditamount,0) - nvl(a.debitamount,0)) cl_bal,
a.particulars
from
ledger a
where
a.unitid=2 and substr(a.documentno,2,3) in ('212','221') and
a.documentdate between '01-apr-07' and '31-Mar-08' and
a.glcode in
(
'64820'
)
)
)
a
group by
a.documentno,
a.documentdate,
a.particulars;

OUTPUT
------
DOCUMENT DOCUMENTD PARTICULARS 90508 90509 64840
72120001 07-MAY-07 BillNIL/04-05-2007
72120002 07-MAY-07 BillNIL./04-05-2007
72120003 07-MAY-07 Bill008/03-05-2007


But They were have the Values.
Example:

DOCUMENT DOCUMENTD PARTICULARS GLCOD DEBITAMT CREDITAMT
72120001 07-MAY-07 BillNIL/04-05-2007 61210 0 53150
72120001 07-MAY-07 BillNIL/04-05-2007 61210 1095 0
72120001 07-MAY-07 BillNIL/04-05-2007 64820 0 1095
72120001 07-MAY-07 BillNIL/04-05-2007 92509 53150 0

Thank You.
Re: Null Value during the Execution of Query [message #317065 is a reply to message #317063] Tue, 29 April 2008 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is unreadable for me if you don't format your query.
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).


Quote:
They were null value for the document.

Which one?

Quote:
.documentdate between '01-apr-07' and '31-Mar-08'

If "documentdate" is of DATE datatype, this is wrong. You can't correctly compare strings and dates.

Regards
Michel
Re: Null Value during the Execution of Query [message #317819 is a reply to message #317063] Sat, 03 May 2008 22:26 Go to previous message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
What is 'unitid' (I don't see it in your sample data)? (And I won't try and read this again until it is correctly formatted).
Previous Topic: Character set problem -- related to OS...
Next Topic: how can i identified very recent DML transaction
Goto Forum:
  


Current Time: Tue Dec 06 10:02:40 CST 2016

Total time taken to generate the page: 0.16198 seconds