same query, but with different return

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

same query, but with different return

Author

Topic

djokerss
Starting Member

28 Posts

Posted - 2005-11-28 : 04:08:47

i have 2 query , the fist like these :

******************************************

set dateformat dmy

declare hitungpiutang cursor scroll for
select a.ckdtrans,a.ntotal,
coalesce((select sum(ntotal) from tbretur where ckdtrans=a.ckdtrans and
lposting=1 and lbatal=2),0) as xretur,
coalesce((select sum(b.nbayar) from tbdetpiu b
left join tbpiu c on b.cnoref=c.cnoref
where c.lposting=1 and b.lbatal=2 and c.lbatal=2
and b.ckdtrans=a.ckdtrans),0) as xbayar
from tbtrans a
where a.ckdtrans='xxx'

declare @gcckdtrans char(20),@gctotal numeric,@gcretur numeric,@gcbayar numeric
open hitungpiutang
fetch next from hitungpiutang into @gcckdtrans,@gctotal,@gcretur,@gcbayar
while @@fetch_status=0
begin
select @gcckdtrans,@gctotal,@gcretur,@gcbayar

fetch next from hitungpiutang into @gcckdtrans,@gctotal,@gcretur,@gcbayar

end

close hitungpiutang
deallocate hitungpiutang

that query give me result xxx , 10000,0,153960

and the second is from the first query without declare

********************

select a.ckdtrans,a.ntotal,
coalesce((select sum(ntotal) from tbretur where ckdtrans=a.ckdtrans and
lposting=1 and lbatal=2),0) as xretur,
coalesce((select sum(nbayar) from tbdetpiu b
left join tbpiu c on b.cnoref=c.cnoref
where c.lposting=1 and b.lbatal=2 and c.lbatal=2
and b.ckdtrans=a.ckdtrans),0) as xbayar
from tbtrans a
where a.ckdtrans='xxx'

***********************

the second query give me the same data, except the fourth column ,
it return 24 , and this is the correct one.

why the first query for fourth column give me 153960 and the second 24 ? whats wrong with my query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-28 : 04:16:15

Is Null the reason?

Instead of
select sum(nbayar) from tbdetpiu b

use
select sum(IsNull(nbayar,0)) from tbdetpiu b

Madhivanan

Failing to plan is Planning to fail

djokerss
Starting Member

28 Posts

Posted - 2005-11-28 : 20:46:16

i've tried , but it doesnt change...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 02:28:58

Are you still getting error?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources