Question of SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Is there anyone who can tell me what's wrong with
it'
Many thanks!
KennethKenneth wrote:
> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Is there anyone who can tell me what's wrong with
> it'
> Many thanks!
> Kenneth
Please post enough code to reproduce the problem and show us what is
wrong with it. Without even seeing what fn_currency does it's hard to
help you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here is the function of fn_currency. It simply convert foreign curreny amount
to base currency amount.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =@.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
"David Portas" wrote:
> Kenneth wrote:
> > Hello,
> >
> > I am new to SQL Server. I am writing a stored procedure with a sql like this
> > :
> >
> > SELECT
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> > FROM TableX
> > WHERE
> > ID = '1234'
> >
> > When I run the query, all the five column return same value and I am sure
> > the result is incorrect. Is there anyone who can tell me what's wrong with
> > it'
> >
> > Many thanks!
> > Kenneth
> Please post enough code to reproduce the problem and show us what is
> wrong with it. Without even seeing what fn_currency does it's hard to
> help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||This will not solve your former problem, but IMHO this is not a good
approach.
You should use a simple joined table and avoid the choice of the method at
the execution (multiply or divide) in the function.
Have a column with a coef and always multiply. This coef can be a computed
column or a column maintained by a trigger. ;-)
This could look like this :
select t1.amount, t1.amount * er.rate as converted_amount
from table t1
inner join exrate er
on t1.currency = er.source_currency
and er.destination_currency = 'USD'
This is a *much* faster.
"Kenneth" <Kenneth@.discussions.microsoft.com> a écrit dans le message de
news: B8BD30D8-1100-45A7-8615-8EDFCB7A494E@.microsoft.com...
> Here is the function of fn_currency. It simply convert foreign curreny
> amount
> to base currency amount.
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency
> => @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> "David Portas" wrote:
>> Kenneth wrote:
>> > Hello,
>> >
>> > I am new to SQL Server. I am writing a stored procedure with a sql like
>> > this
>> > :
>> >
>> > SELECT
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
>> > FROM TableX
>> > WHERE
>> > ID = '1234'
>> >
>> > When I run the query, all the five column return same value and I am
>> > sure
>> > the result is incorrect. Is there anyone who can tell me what's wrong
>> > with
>> > it'
>> >
>> > Many thanks!
>> > Kenneth
>> Please post enough code to reproduce the problem and show us what is
>> wrong with it. Without even seeing what fn_currency does it's hard to
>> help you.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>