query help

[sonaliak]

I have a union query:
Select NVL(sum(Subledger_Amount),0.00)
from ciscoSubledger
where Subledger_Acct_Number=42702 and Subledger_Work_ID = 740
and Subledger_Type=1
union
Select NVL(sum(Subledger_Amount),0.00)
from ciscoSubledger
where Subledger_Acct_Number=42702 and Subledger_Work_ID = 740
and Subledger_Type=2

which returns
NVL(SUM(SU
----------
0
32590.95
2 rows selected.
Is there any way to sum up these 2 results in the same query..I want it to return 1 row with value(result) 32590.95.


I also tried this query but it returns 0
select NVL(sum(b.Subledger_Amount),0.00) - NVL(sum(a.Subledger_Amount),0.00)
from ciscoSubledger a, ciscoSubledger b
where a.Subledger_Acct_Number=42702 and a.Subledger_Work_ID = 740 and a.Subledger_Type=1
and ( b.Subledger_Acct_Number=42702 and b.Subledger_Work_ID=740 and b.Subledger_Type=2)

as
select count(*)
from ciscoSubledger a
where a.Subledger_Acct_Number=42702 and a.Subledger_Work_ID = 740 and a.Subledger_Type=1

returns 0 rows. even though..

select count(*)
from ciscoSubledger b
where b.Subledger_Acct_Number=42702 and b.Subledger_Work_ID = 740 and b.Subledger_Type=2
this returns values..
Any way of doing this query. I even tried decode but no luck.

Thanks

[Sairaj]

Hi,

I am not sure but still you can try this

Select NVL(sum(Subledger_Amount),0.00)
from ciscoSubledger
where Subledger_Acct_Number=42702 and Subledger_Work_ID = 740
and Subledger_Type in(1,2);

Good luck
Prasad.

[sonaliak]

I am sorry. I wrote "sum up" by mistake.
I want to subtract these 2 results and not sum up.
result of this should be 32590.95 !
I could not do it in a join query...because cout(*) was 0 for
select count(*)
from ciscoSubledger a
where a.Subledger_Acct_Number=42702 and a.Subledger_Work_ID = 740 and a.Subledger_Type=1


thanks

[Sairaj]

Then why not use minus instead of union.

Prasad.

[sonaliak]

I had tried it.
Minus returns 0 too !!

[Sairaj]

Did you try writing a PL/SQL block with a check on whether it exists or not. If exists, you can take the difference by minus else return the value from the condition which has records.

Prasad.

[jmodic]

If you want to subtract the sums of (Subledger_Type=2 - Subledger_Type=1) try the following:

Select
NVL(sum(DECODE(Subledger_Type,1,-Subledger_Amount, Subledger_Amount)),0.00)
from ciscoSubledger
where Subledger_Acct_Number=42702 and Subledger_Work_ID = 740
and Subledger_Type IN (1 ,2);

[sambavan]

Solution provided earlier on Minus would work.

Sam

[Edited by sambavan on 06-21-2001 at 05:41 PM]

[jmodic]

Originally posted by sambavan
Solution provided earlier on Minus would work.

Sam

[Edited by sambavan on 06-21-2001 at 05:41 PM]

No it wouldn't. MINUS set operator is something totaly diferent as a "-" subtraction operator. It returns the rows from the first query that do not apear in the resultset of the second query. So in Sonali's example using a MINUS operator would of cours result in "no rows returned".

[sambavan]

Ahhh!!!!,

My posting was a good example for a tired mind. Wanted to post that "minus solution would not work" but ended up missing the not Too much of things to think today.

To prove the fact I, even tried it .

Here the try I made:

SQL> select sum(amount) from ledger where type = 1
2 minus
3 select sum(amount) from ledger where type = 2;

SUM(AMOUNT)
===========
0

SQL> select sum(amount) from ledger where type = 2
2 minus
3 select sum(amount) from ledger where type = 1;

SUM(AMOUNT)
===========
1600


SQL> insert into ledger values (1, 100);

1 row created.

SQL> select sum(amount) from ledger where type = 2
2 minus
3 select sum(amount) from ledger where type = 1;

SUM(AMOUNT)
===========
1600


I should myself for that mistake.

Sam