Board index » delphi » What is the difference ?

2004-04-30 06:22:59 PM
delphi276
Hi guys,
Could someone tell me what is the diffrence in these queries.
( Assume they are functions )
Func 1:
....
set x = ( select sum(case type
when 'B' then ( value*ccy_rate)
when 'A' then -( value*ccy_rate)
end
)
from fo_posting
where folio_id=675 )
....
Func 2:
....
set x=(select sum(value*ccy_rate) from fo_posting where folio_id=675 and
type='B' )
set x = x - ( select sum(value*ccy_rate) from fo_posting where folio_id=675
and type='A' )
....
Table fo_posting is some kind of transaction table. You know, debit, credit
things.
"type" is kind of transaction.
The first one returns wrong result !
Are these two queries should return same result ?
Or something wrong with the "case" statement.
I am confused with this.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (www.grisoft.com).
Version: 6.0.671 / Virus Database: 433 - Release Date: 28/04/2004
 

Are you absolutely sure that [type] is holding only 'A' and 'B' values?
"Erkin ESKIER" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅ? news:XXXX@XXXXX.COM...

Yes. Column does not allow NULLs.
"Vitali Kalinin" <XXXX@XXXXX.COM>writes
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (www.grisoft.com).
Version: 6.0.671 / Virus Database: 433 - Release Date: 28/04/2004
 

And of course rules are applied.
"Vitali Kalinin" <XXXX@XXXXX.COM>writes
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (www.grisoft.com).
Version: 6.0.671 / Virus Database: 433 - Release Date: 28/04/2004
 

So select distinct [type] from fo_posting shows this:
type
----------
A
B
Correct? Also can you explain why do you think that first query gives wrong
results but second correct one (sample fo_posting table and "wrong" and
"correct" results for this sample table can help)
"Erkin ESKIER" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅ? news:XXXX@XXXXX.COM...

A simplified table is;
CREATE TABLE [dbo].[_atest] (
[post_id] [int] IDENTITY (1, 1) NOT NULL ,
[type_of] [char] (1) ,
[tran_value] [float] NULL ,
[ccy_rate] [float] NULL
) ON [PRIMARY]
Sample values are;
type
_of tran_value ccy_rate
---- ------------------------- ------------
B 5000.0 1.0
B 30.0 1005.91
B 30.0 1005.91
A 90531.899999999994 1.0
B 30177.0 1.0
B 12575.0 1.0
B 15088.65 1.0
A 15088.65 1.0
B 15088.65 1.0
A 15088.65 1.0
A 17574.700000000001 1.0
B 30.0 1005.91
A 30177.299999999999 1.0
"Vitali Kalinin" <XXXX@XXXXX.COM>writes
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (www.grisoft.com).
Version: 6.0.671 / Virus Database: 433 - Release Date: 28/04/2004
 

Now I see it. You are faced with accuracy of float point calculations. You
can improve accuracy of your query by if you will cast values to money type,
but IMHO if you should use MONEY datatype.
To illustrate first approach you can consider this:
set nocount on
CREATE TABLE [atest] (
[post_id] [int] IDENTITY (1, 1) NOT NULL ,
[type_of] [char] (1) ,
[tran_value] [float] NULL ,
[ccy_rate] [float] NULL
)
insert into [atest] values('B', 5000.0,
1.0)
insert into [atest] values('B', 30.0,
1005.91)
insert into [atest] values('B', 30.0,
1005.91)
insert into [atest] values('A', 90531.899999999994, 1.0)
insert into [atest] values('B', 30177.0,
1.0)
insert into [atest] values('B', 12575.0,
1.0)
insert into [atest] values('B', 15088.65,
1.0)
insert into [atest] values('A', 15088.65,
1.0)
insert into [atest] values('B', 15088.65,
1.0)
insert into [atest] values('A', 15088.65,
1.0)
insert into [atest] values('A', 17574.700000000001, 1.0)
insert into [atest] values('B', 30.0,
1005.91)
insert into [atest] values('A', 30177.299999999999, 1.0)
declare @x float
select @x = sum(
case [type_of]
when 'B' then ( cast(tran_value as money)*cast(ccy_rate as
money))
when 'A' then -1.0 * ( cast(tran_value as money)*cast(ccy_rate as
money))
end
)
from atest
print 'wrong'
select @x
select @x = sum(tran_value*ccy_rate) from [atest] where [type_of]='B'
select @x = @x - sum(tran_value*ccy_rate) from [atest] where [type_of]='A'
print 'correct'
select @x
set nocount off
go
drop table [atest]