Skip to main content
nikos101
nikos101
Inspiring
February 4, 2010
Answered

String scalar Function in ms sql2005

  • February 4, 2010
  • 1 reply
  • 1650 views

Can someone  help me with a function?

I have some sql:

  SELECT depositReceivedCurrency ,SUM(depositReceivedAmount) AS currencyAmount
    FROM    dbo.tbTrades
     WHERE clientID = 1 AND  depositReceivedCurrency IS  NOT NULL
      GROUP BY depositReceivedCurrency

That returns a table like so:

EUR    35459.00
GBP    1000.00

Note: there could be more than 2 currencies

I would like a function that would take this table and out put a string like this:

"EUR    35459.00 / GBP    1000.00"  [ + / ith currency amount]

Possible? here's a chance to show off

This topic has been closed for replies.
Correct answer -__cfSearching__-

I've removed the stuff for simplicity and this gets me what I want:

    SELECT   ( (

SELECT  ' ' + depositReceivedCurrency + ' ',
        CAST(SUM(depositReceivedAmount) AS VARCHAR) + '   '
FROM    dbo.tbTrades
WHERE   clientID = 1
        AND depositReceivedCurrency IS  NOT NULL
GROUP BY depositReceivedCurrency
                                FOR
                                  XML PATH('')
                                
                                 )
                                )

the string

EUR 35459.00    GBP 1000.00    USD 324.00 

however if I do this:

  DECLARE @ResultVar VARCHAR


    SET @ResultVar = (
   
    SELECT   ( (

SELECT  ' ' + depositReceivedCurrency + ' ',
        CAST(SUM(depositReceivedAmount) AS VARCHAR) + '   '
FROM    dbo.tbTrades
WHERE   clientID = 1
        AND depositReceivedCurrency IS  NOT NULL
GROUP BY depositReceivedCurrency
                                FOR
                                  XML PATH('')
                                
                                 )
                                )
                     )

    select @ResultVar

I get nothing


however if I do this:

....

I get nothing

You just forgot to define the varchar length

      DECLARE @ResultVar VARCHAR(max)

Depending on how you are using the function, you may want to do some performance tests. While they are convenient, functions that perform a subquery on every row in a resultset can take a toll on performance. Especially when used on a large number of records.

Message was edited by: -==cfSearching==-

1 reply

A
Adam Cameron.
Inspiring
February 4, 2010

It doesn't require any showing off.

<cfquery name="q" datasource="dsn">

SELECT cast(depositReceivedCurrency as varchar) + ' ' + cast(SUM(depositReceivedAmount) as varchar) AS currencyAmount
FROM    dbo.tbTrades
WHERE clientID = 1 AND  depositReceivedCurrency IS  NOT NULL
GROUP BY depositReceivedCurrency

</cfquery>

<cfoutput>#arrayToList(q["currencyAmount"], " / ")#</cfoutput>

If it's just for output that you want it in the slash-delimited list, there's no point doing it in the DB.  In fact it's probably the wrong place to do it.

--

Adam

nikos101
nikos101Author
Inspiring
February 4, 2010

awesome thanks, is is possible to do in ms sql?

nikos101
nikos101Author
Inspiring
February 4, 2010

The thing is is this will be part of a much larger query thats why I need it in a function

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices