Home » SQL & PL/SQL » SQL & PL/SQL » I need to put amount '0' if the row exist but has no amount in my "where " conditions. (Pl/ sql 7.0.2)
I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562096] Sat, 28 July 2012 01:23 Go to next message
arash_beterkun
Messages: 5
Registered: July 2012
Location: tehran
Junior Member

I need to put amount '0' if the row exist but has no amount in my "where " conditions.

the orginal commad is :

select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa


and the result is :

"result" tab in excel atached file.



i need this result:

"result 2" tab in excel atached file.

how can i have this ?
- all nessesary info are atached to the topic.

[Updated on: Sat, 28 July 2012 01:30]

Report message to a moderator

Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562097 is a reply to message #562096] Sat, 28 July 2012 01:29 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are no images here. Perhaps you'd better read OraFAQ Forum Guide.

Anyway: the question looks like you are looking for the NVL function. However, if WHERE clause causes that these records are not retrieved at all, then you might need to use outer self join.
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562098 is a reply to message #562097] Sat, 28 July 2012 01:32 Go to previous messageGo to next message
arash_beterkun
Messages: 5
Registered: July 2012
Location: tehran
Junior Member

i donno how . but the topic auto saved. i do editing it. give me some moment.
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562100 is a reply to message #562098] Sat, 28 July 2012 02:07 Go to previous messageGo to next message
arash_beterkun
Messages: 5
Registered: July 2012
Location: tehran
Junior Member

see it here.

ht tp: //www.mediafire.com /?69cc4ay6cyt9cr9
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562101 is a reply to message #562100] Sat, 28 July 2012 02:46 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The link is invalid as well (yes, I removed spaces). As I said: have a look at the Guide. It will instruct you to post a test case (CREATE TABLE and INSERT INTO sample data).
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562102 is a reply to message #562096] Sat, 28 July 2012 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562106 is a reply to message #562101] Sat, 28 July 2012 03:42 Go to previous messageGo to next message
arash_beterkun
Messages: 5
Registered: July 2012
Location: tehran
Junior Member

1st : the link is real . 3 space u need to del.

2nd. its a test case. i did send full result . please just delet '*' from the link mate (2x*).

ht*tp*://www.mediafire.com/?69cc4ay6cyt9cr9


os : win xp
Pl/sql 7.0.2 unlimited user lisence
oci : 9.2
oracle db : 11.1.0.6.0 enterprise
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562108 is a reply to message #562106] Sat, 28 July 2012 07:40 Go to previous messageGo to next message
arash_beterkun
Messages: 5
Registered: July 2012
Location: tehran
Junior Member

problem solved .


the answer is:

select t1.aaa, coalesce(t2.bbb_count, 0) bbb_count,
coalesce(t2.ccc_sum, 0) ccc_sum
from (
select distinct aaa
from nrb
) t1
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '50%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t2 on t1.aaa = t2.aaa
order by t1.aaa;


-> i have a new question:

this query will give me a table with 3 Columns. how can i make a table like excel sheet and loop the t.ddd line '50%' to t.ddd like '55%'. i mean i will have 15 Columns with same rows.
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562114 is a reply to message #562108] Sat, 28 July 2012 10:00 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 28 July 2012 10:13
With any SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Previous Topic: filter data in one row
Next Topic: Cursor For Loop
Goto Forum:
  


Current Time: Thu Mar 28 19:23:22 CDT 2024