1

I am relativity newbie in SQL so please do not judge strictly. I have a table valued function.Within that function I have a this code:

 DECLARE @totalPayment DECIMAL(18,4),
         @totalPaymentConverted DECIMAL(18,4),
         @fromCurrencyIdForPaymentSystemsId CHAR(3);

 DECLARE @GetTotalPaymentAmount AS TABLE 
 (
   Amount     DECIMAL(18,4),
   CurrencyId CHAR(3)
 );

INSERT INTO @GetTotalPaymentAmount
  SELECT    SUM(Amount),CurrencyId
  FROM      [dbo].[fn_DepositWithdrawReport]()
  WHERE     OperationTypeId = 2
  GROUP BY  CurrencyId;

This insert return met table like this

 A      C
-----|------
550  | USD
650  | EU
     |

I want to pass each of this values Amount and CurrencyId to the Scalar valued Function as Input parameter like this:

[dbo].[function_Valued](@totalPayment,@fromCurencyId,@toCurrencyId)

And sum that values for instance if we consider the table above:

[dbo].[functin_scalar](550, USD, EU) = 450
[dbo].[function_scalar](650, EU, EU) = 650

Get total 1100

Put simply I want to calculate sum amount and I need to convert it in one currency. I have been told to use APPLY but I cannot understand how to use it in my code. Maybe someone could show in code example?

  • Could you add [function_scalar] code? Maybe you can use an INLINE UDF instead of a scalar function o some user can show you how to use an APPLY solution. – McNets Jan 26 '18 at 13:38
  • And please tag your SQL Server version. – McNets Jan 26 '18 at 13:42
  • I could show bu t i am sure that i cannot change the code of that function in our system it is used literally everywhere.It is scalar valued function with decimal return type – So_oP Jan 26 '18 at 13:48
  • I did some test passing a list of int't to an inline UDF and there is a way if you 're interested. – McNets Feb 7 '18 at 12:10
  • Yeah sure .Bu i've deleted the post. – So_oP Feb 7 '18 at 12:40
2

It is fairly straightforward with scalar functions, although your description is very confusing and hard to follow with regard to function names and parameter names. Anyway, when you have a scalar function and a table of values that you want to pass as arguments to the function one row at a time, you can go like this:

SELECT
  dbo.function_scalar(Amount, CurrencyId, 'EU')
FROM
  @GetTotalPaymentAmount
;

That will give you a column of values each of which is the result for a corresponding row of the table. At this point you can specify Amount and CurrencyId to return them along with the results to verify the results match the input values.

Now, as I have understood from your description, the results will be amounts in the same currency. You can get the total amount by aggregating the column returned by the function, for which you simply need to apply SUM() to the function result:

SELECT
  SUM(dbo.function_scalar(Amount, CurrencyId, 'EU'))
FROM
  @GetTotalPaymentAmount
;

That is basically it. If you need to return the result as a single-row single-column dataset or otherwise use it as a derived table in another query, you will probably want to assign an alias to the aggregated column by changing the SUM line to this:

  TotalAmount = SUM(...)

or this:

  SUM(...) AS TotalAmount

You can also just store the result in a variable if necessary:

  @TotalAmount = SUM(...)
1
CREATE TABLE payments (Amount DECIMAL(18,4), CurrencyId CHAR(3));
CREATE TABLE change (FromCurrency VARCHAR(10), ToCurrency VARCHAR(10), Factor DECIMAL(18,4));
INSERT INTO payments VALUES (550.0, 'USD'), (650, 'EU');
INSERT INTO change VALUES('USD', 'EU', 0.8040), ('EU', 'EU', 1.0);
GO
SELECT * FROM payments;
GO
Amount   | CurrencyId
:------- | :---------
550.0000 | USD       
650.0000 | EU        

Just to show you how could implement an APPLY solution. The APPLY part will be executed for every record of payments table. In this case I've not used any UDF.

SELECT      p.Amount, p.CurrencyID, c.Factor,
            p.Amount * COALESCE(c.Factor, 0) AS AmountEU,
            SUM(p.Amount * COALESCE(c.Factor, 0)) 
                OVER (ORDER BY (SELECT NULL) ROWS UNBOUNDED PRECEDING) Total
FROM        payments p
OUTER APPLY (SELECT Factor
             FROM   change
             WHERE  FromCurrency = p.CurrencyID
             AND    ToCurrency = 'EU') c;
GO
Amount   | CurrencyID | Factor | AmountEU     | Total        
:------- | :--------- | :----- | :----------- | :------------
550.0000 | USD        | 0.8040 | 442.20000000 | 442.20000000 
650.0000 | EU         | 1.0000 | 650.00000000 | 1092.20000000

Then if you have a scalar UDF function similar to this:

CREATE FUNCTION getCurrency(@Amount DECIMAL(18,4), @FromC VARCHAR(10), @ToC VARCHAR(10))
RETURNS DECIMAL(18,4)
AS
BEGIN
    DECLARE @Value DECIMAL(18,4);

    SELECT @Value = Factor
    FROM   change
    WHERE  FromCurrency = @FromC
    AND    ToCurrency = @ToC;

    RETURN COALESCE(@Value, 0) * @Amount;
END
GO

In this case you don't need an APPLY solution, you can use your function directly in your code in this way.

Notice in both queries I've added a cumulative total using a Window Function.

SELECT      p.Amount, p.CurrencyID,
            dbo.getCurrency(p.Amount, p.CurrencyID, 'EU') AS AmountEU,
            SUM(dbo.getCurrency(p.Amount, p.CurrencyID, 'EU')) 
                OVER (ORDER BY (SELECT NULL) ROWS UNBOUNDED PRECEDING) Total
FROM        payments p
GO
Amount   | CurrencyID | AmountEU | Total    
:------- | :--------- | :------- | :--------
550.0000 | USD        | 442.2000 | 442.2000 
650.0000 | EU         | 650.0000 | 1092.2000

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.