Forum Discussion

Marco's avatar
Marco
Contributor II
3 months ago

currency names of the FX rates or the connection between the FX rates

Hi Everyone. I am running an SQL query, but I am now encountering an issue with currency conversions. The only thing my table contains is the column with the currency name, but it is not very helpf...
FX Rates
Infrastructure
  • sameburn's avatar
    sameburn
    3 months ago

    Hi Marco 

    Since this information does not exist in a table.  The only option available other than your CASE statement would be to get creative and do something with your SQL in a Business Rule instead.  For example, if you move your logic to a Business Rule e.g. Dashboard DataSet.  You have a bit more flexibility with how you can reference things e.g. using a dictionary.  Below is an untested example of how you can do this. 

    Here we create a placeholder NVARCHAR column called Currency in our SQL query.  We then loop through the DataTable result and use a Dictionary (this time the ID is the Key and the Name is the Value) to lookup the Name based on the SourceCurrencyId and populate the Currency column with the corresponding Currency Name

    This should give you same result, without that big CASE statement. 

    	' Get Currency Dictionary
	Dim currencyDict As Dictionary(Of Integer, String) = Currency.GetItems().ToDictionary(Function(kvp) kvp.Id, Function(kvp) kvp.Name)
	
	' Declare SQL	
	Dim sql As String = "WITH CTE
						 AS (
						 SELECT 
						  fxt.[UniqueID]
						 ,fxt.Name
						 ,fx.SourceCurrencyId
						 ,'Unknown' Currency
						 ,fx.Amount
						 FROM [FxRateType] fxt
						 LEFT JOIN [FxRate] fx
							ON fxt.UniqueID = fx.FxRateTypeID
						 WHERE                 
						 (
		                    Month(GETDATE()) = 1
		                    AND Name LIKE '%' + CAST((Year(GETDATE()) -1) AS NVARCHAR(4)) + 'M2%'
		                 )
		                 OR (
		                    Month(GETDATE()) > 1
		                    AND Name LIKE '%' + CAST(Year(GETDATE()) AS NVARCHAR(4)) + 'M2%'
		                 )
						    )
						 SELECT SourceCurrencyId, Currency, Amount FROM CTE"
	
	'Execute Query on App DB
	Dim dt As New DataTable()
	Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	    dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString(), True)
        If Not dt Is Nothing Then
			' prepare placeholder column
			dt.Columns("Currency").ReadOnly = False
			dt.Columns("Currency").MaxLength = -1
			' Loop on rows
            For Each dr As DataRow In dt.Rows
                'Process rows
				dr.BeginEdit
					' Lookup Currency Name from Dictionary by passing in SourceCurrencyId DataRow and assign value to Currency column
                	dr("Currency") = currencyDict.Item(dr("SourceCurrencyId"))
				dr.EndEdit
            Next
        End If
	End Using
Marco's avatar
Marco
Contributor II

Hi Sameburn.

I am currently using a CASE statement, but it is not a solution I am very satisfied with, as I have to assign the currency name according to its ID. That is why I wanted to know if this information can be obtained from another table or if there is a table that holds the relationships with the tables I previously mentioned.

    SELECT
        CASE
            WHEN fx.SourceCurrencyId = 0 THEN 'AED'
            WHEN fx.SourceCurrencyId = 1 THEN 'AFN'
            WHEN fx.SourceCurrencyId = 2 THEN 'ALL'
            WHEN fx.SourceCurrencyId = 3 THEN 'AMD'
            WHEN fx.SourceCurrencyId = 4 THEN 'ANG'
            WHEN fx.SourceCurrencyId = 5 THEN 'AOA'
            WHEN fx.SourceCurrencyId = 6 THEN 'ARS'
            WHEN fx.SourceCurrencyId = 7 THEN 'ATS'
            WHEN fx.SourceCurrencyId = 8 THEN 'AUD'
            WHEN fx.SourceCurrencyId = 9 THEN 'AWG'
            WHEN fx.SourceCurrencyId = 10 THEN 'AZN'
            WHEN fx.SourceCurrencyId = 11 THEN 'BAM'
            WHEN fx.SourceCurrencyId = 12 THEN 'BBD'
            WHEN fx.SourceCurrencyId = 13 THEN 'BDT'
            WHEN fx.SourceCurrencyId = 14 THEN 'BEF'
            WHEN fx.SourceCurrencyId = 15 THEN 'BGN'
            WHEN fx.SourceCurrencyId = 16 THEN 'BHD'
            WHEN fx.SourceCurrencyId = 17 THEN 'BIF'
            WHEN fx.SourceCurrencyId = 18 THEN 'BMD'
            WHEN fx.SourceCurrencyId = 19 THEN 'BND'
            WHEN fx.SourceCurrencyId = 20 THEN 'BOB'
            WHEN fx.SourceCurrencyId = 21 THEN 'BRL'
            WHEN fx.SourceCurrencyId = 22 THEN 'BSD'
            WHEN fx.SourceCurrencyId = 23 THEN 'BTN'
            WHEN fx.SourceCurrencyId = 24 THEN 'BWP'
            WHEN fx.SourceCurrencyId = 25 THEN 'BYR'
            WHEN fx.SourceCurrencyId = 26 THEN 'BZD'
            WHEN fx.SourceCurrencyId = 27 THEN 'CAD'
            WHEN fx.SourceCurrencyId = 28 THEN 'CC1'
            WHEN fx.SourceCurrencyId = 29 THEN 'CC2'
            WHEN fx.SourceCurrencyId = 30 THEN 'CC3'
            WHEN fx.SourceCurrencyId = 31 THEN 'CC4'
            WHEN fx.SourceCurrencyId = 32 THEN 'CC5'
            WHEN fx.SourceCurrencyId = 33 THEN 'CC6'
            WHEN fx.SourceCurrencyId = 34 THEN 'CC7'
            WHEN fx.SourceCurrencyId = 35 THEN 'CC8'
            WHEN fx.SourceCurrencyId = 36 THEN 'CDF'
            WHEN fx.SourceCurrencyId = 37 THEN 'CHF'
            WHEN fx.SourceCurrencyId = 38 THEN 'CLP'
            WHEN fx.SourceCurrencyId = 39 THEN 'CNY'
            WHEN fx.SourceCurrencyId = 40 THEN 'COP'
            WHEN fx.SourceCurrencyId = 41 THEN 'CRC'
            WHEN fx.SourceCurrencyId = 42 THEN 'CUP'
            WHEN fx.SourceCurrencyId = 43 THEN 'CVE'
            WHEN fx.SourceCurrencyId = 44 THEN 'CYP'
            WHEN fx.SourceCurrencyId = 45 THEN 'CZK'
            WHEN fx.SourceCurrencyId = 46 THEN 'DEM'
            WHEN fx.SourceCurrencyId = 47 THEN 'DJF'
            WHEN fx.SourceCurrencyId = 48 THEN 'DKK'
            WHEN fx.SourceCurrencyId = 49 THEN 'DOP'
            WHEN fx.SourceCurrencyId = 50 THEN 'DZD'
            WHEN fx.SourceCurrencyId = 51 THEN 'ECS'
            WHEN fx.SourceCurrencyId = 52 THEN 'EEK'
            WHEN fx.SourceCurrencyId = 53 THEN 'EGP'
            WHEN fx.SourceCurrencyId = 54 THEN 'ERN'
            WHEN fx.SourceCurrencyId = 55 THEN 'ESP'
            WHEN fx.SourceCurrencyId = 56 THEN 'ETB'
            WHEN fx.SourceCurrencyId = 57 THEN 'EUR'
            WHEN fx.SourceCurrencyId = 58 THEN 'FIM'
            WHEN fx.SourceCurrencyId = 59 THEN 'FJD'
            WHEN fx.SourceCurrencyId = 60 THEN 'FKP'
            WHEN fx.SourceCurrencyId = 61 THEN 'FRF'
            WHEN fx.SourceCurrencyId = 62 THEN 'GBP'
            WHEN fx.SourceCurrencyId = 63 THEN 'GEL'
            WHEN fx.SourceCurrencyId = 64 THEN 'GGP'
            WHEN fx.SourceCurrencyId = 65 THEN 'GHC'
            WHEN fx.SourceCurrencyId = 66 THEN 'GIP'
            WHEN fx.SourceCurrencyId = 67 THEN 'GMD'
            WHEN fx.SourceCurrencyId = 68 THEN 'GNF'
            WHEN fx.SourceCurrencyId = 69 THEN 'GRD'
            WHEN fx.SourceCurrencyId = 70 THEN 'GTQ'
            WHEN fx.SourceCurrencyId = 71 THEN 'GYD'
            WHEN fx.SourceCurrencyId = 72 THEN 'HKD'
            WHEN fx.SourceCurrencyId = 73 THEN 'HNL'
            WHEN fx.SourceCurrencyId = 74 THEN 'HRK'
            WHEN fx.SourceCurrencyId = 75 THEN 'HTG'
            WHEN fx.SourceCurrencyId = 76 THEN 'HUF'
            WHEN fx.SourceCurrencyId = 77 THEN 'IDR'
            WHEN fx.SourceCurrencyId = 78 THEN 'IEP'
            WHEN fx.SourceCurrencyId = 79 THEN 'ILS'
            WHEN fx.SourceCurrencyId = 80 THEN 'IMP'
            WHEN fx.SourceCurrencyId = 81 THEN 'INR'
            WHEN fx.SourceCurrencyId = 82 THEN 'IQD'
            WHEN fx.SourceCurrencyId = 83 THEN 'IRR'
            WHEN fx.SourceCurrencyId = 84 THEN 'ISK'
            WHEN fx.SourceCurrencyId = 85 THEN 'ITL'
            WHEN fx.SourceCurrencyId = 86 THEN 'JEP'
            WHEN fx.SourceCurrencyId = 87 THEN 'JMD'
            WHEN fx.SourceCurrencyId = 88 THEN 'JOD'
            WHEN fx.SourceCurrencyId = 89 THEN 'JPY'
            WHEN fx.SourceCurrencyId = 90 THEN 'KES'
            WHEN fx.SourceCurrencyId = 91 THEN 'KGS'
            WHEN fx.SourceCurrencyId = 92 THEN 'KHR'
            WHEN fx.SourceCurrencyId = 93 THEN 'KMF'
            WHEN fx.SourceCurrencyId = 94 THEN 'KPW'
            WHEN fx.SourceCurrencyId = 95 THEN 'KRW'
            WHEN fx.SourceCurrencyId = 96 THEN 'KWD'
            WHEN fx.SourceCurrencyId = 97 THEN 'KYD'
            WHEN fx.SourceCurrencyId = 98 THEN 'KZT'
            WHEN fx.SourceCurrencyId = 99 THEN 'LAK'
            WHEN fx.SourceCurrencyId = 100 THEN 'LBP'
            WHEN fx.SourceCurrencyId = 101 THEN 'LKR'
            WHEN fx.SourceCurrencyId = 102 THEN 'LRD'
            WHEN fx.SourceCurrencyId = 103 THEN 'LSL'
            WHEN fx.SourceCurrencyId = 104 THEN 'LTL'
            WHEN fx.SourceCurrencyId = 105 THEN 'LUF'
            WHEN fx.SourceCurrencyId = 106 THEN 'LVL'
            WHEN fx.SourceCurrencyId = 107 THEN 'LYD'
            WHEN fx.SourceCurrencyId = 108 THEN 'MAD'
            WHEN fx.SourceCurrencyId = 109 THEN 'MCF'
            WHEN fx.SourceCurrencyId = 110 THEN 'MDL'
            WHEN fx.SourceCurrencyId = 111 THEN 'MGA'
            WHEN fx.SourceCurrencyId = 112 THEN 'MKD'
            WHEN fx.SourceCurrencyId = 113 THEN 'MMK'
            WHEN fx.SourceCurrencyId = 114 THEN 'MNT'
            WHEN fx.SourceCurrencyId = 115 THEN 'MOP'
            WHEN fx.SourceCurrencyId = 116 THEN 'MRO'
            WHEN fx.SourceCurrencyId = 117 THEN 'MTL'
            WHEN fx.SourceCurrencyId = 118 THEN 'MUR'
            WHEN fx.SourceCurrencyId = 119 THEN 'MVR'
            WHEN fx.SourceCurrencyId = 120 THEN 'MWK'
            WHEN fx.SourceCurrencyId = 121 THEN 'MXN'
            WHEN fx.SourceCurrencyId = 122 THEN 'MYR'
            WHEN fx.SourceCurrencyId = 123 THEN 'MZN'
            WHEN fx.SourceCurrencyId = 124 THEN 'NAD'
            WHEN fx.SourceCurrencyId = 125 THEN 'NGN'
            WHEN fx.SourceCurrencyId = 126 THEN 'NIO'
            WHEN fx.SourceCurrencyId = 127 THEN 'NLG'
            WHEN fx.SourceCurrencyId = 128 THEN 'NOK'
            WHEN fx.SourceCurrencyId = 129 THEN 'NPR'
            WHEN fx.SourceCurrencyId = 130 THEN 'NZD'
            WHEN fx.SourceCurrencyId = 131 THEN 'OMR'
            WHEN fx.SourceCurrencyId = 132 THEN 'PAB'
            WHEN fx.SourceCurrencyId = 133 THEN 'PEN'
            WHEN fx.SourceCurrencyId = 134 THEN 'PGK'
            WHEN fx.SourceCurrencyId = 135 THEN 'PHP'
            WHEN fx.SourceCurrencyId = 136 THEN 'PKR'
            WHEN fx.SourceCurrencyId = 137 THEN 'PLN'
            WHEN fx.SourceCurrencyId = 138 THEN 'PTE'
            WHEN fx.SourceCurrencyId = 139 THEN 'PYG'
            WHEN fx.SourceCurrencyId = 140 THEN 'QAR'
            WHEN fx.SourceCurrencyId = 141 THEN 'RON'
            WHEN fx.SourceCurrencyId = 142 THEN 'RSD'
            WHEN fx.SourceCurrencyId = 143 THEN 'RUB'
            WHEN fx.SourceCurrencyId = 144 THEN 'RWF'
            WHEN fx.SourceCurrencyId = 145 THEN 'SAR'
            WHEN fx.SourceCurrencyId = 146 THEN 'SBD'
            WHEN fx.SourceCurrencyId = 147 THEN 'SCR'
            WHEN fx.SourceCurrencyId = 148 THEN 'SDG'
            WHEN fx.SourceCurrencyId = 149 THEN 'SEK'
            WHEN fx.SourceCurrencyId = 150 THEN 'SGD'
            WHEN fx.SourceCurrencyId = 151 THEN 'SHP'
            WHEN fx.SourceCurrencyId = 152 THEN 'SIT'
            WHEN fx.SourceCurrencyId = 153 THEN 'SKK'
            WHEN fx.SourceCurrencyId = 154 THEN 'SLL'
            WHEN fx.SourceCurrencyId = 155 THEN 'SML'
            WHEN fx.SourceCurrencyId = 156 THEN 'SOS'
            WHEN fx.SourceCurrencyId = 157 THEN 'SPL'
            WHEN fx.SourceCurrencyId = 158 THEN 'SRD'
            WHEN fx.SourceCurrencyId = 159 THEN 'STD'
            WHEN fx.SourceCurrencyId = 160 THEN 'SVC'
            WHEN fx.SourceCurrencyId = 161 THEN 'SYP'
            WHEN fx.SourceCurrencyId = 162 THEN 'SZL'
            WHEN fx.SourceCurrencyId = 163 THEN 'THB'
            WHEN fx.SourceCurrencyId = 164 THEN 'TJS'
            WHEN fx.SourceCurrencyId = 165 THEN 'TMM'
            WHEN fx.SourceCurrencyId = 166 THEN 'TND'
            WHEN fx.SourceCurrencyId = 167 THEN 'TOP'
            WHEN fx.SourceCurrencyId = 168 THEN 'TRL'
            WHEN fx.SourceCurrencyId = 169 THEN 'TRY'
            WHEN fx.SourceCurrencyId = 170 THEN 'TTD'
            WHEN fx.SourceCurrencyId = 171 THEN 'TVD'
            WHEN fx.SourceCurrencyId = 172 THEN 'TWD'
            WHEN fx.SourceCurrencyId = 173 THEN 'TZS'
            WHEN fx.SourceCurrencyId = 174 THEN 'UAH'
            WHEN fx.SourceCurrencyId = 175 THEN 'UGX'
            WHEN fx.SourceCurrencyId = 176 THEN 'USD'
            WHEN fx.SourceCurrencyId = 177 THEN 'UYP'
            WHEN fx.SourceCurrencyId = 178 THEN 'UYU'
            WHEN fx.SourceCurrencyId = 179 THEN 'UZS'
            WHEN fx.SourceCurrencyId = 180 THEN 'VAL'
            WHEN fx.SourceCurrencyId = 181 THEN 'VEB'
            WHEN fx.SourceCurrencyId = 182 THEN 'VEF'
            WHEN fx.SourceCurrencyId = 183 THEN 'VND'
            WHEN fx.SourceCurrencyId = 184 THEN 'VUV'
            WHEN fx.SourceCurrencyId = 185 THEN 'WST'
            WHEN fx.SourceCurrencyId = 186 THEN 'XAF'
            WHEN fx.SourceCurrencyId = 187 THEN 'XAG'
            WHEN fx.SourceCurrencyId = 188 THEN 'XAU'
            WHEN fx.SourceCurrencyId = 189 THEN 'XCD'
            WHEN fx.SourceCurrencyId = 190 THEN 'XDR'
            WHEN fx.SourceCurrencyId = 191 THEN 'XOF'
            WHEN fx.SourceCurrencyId = 192 THEN 'XPD'
            WHEN fx.SourceCurrencyId = 193 THEN 'XPF'
            WHEN fx.SourceCurrencyId = 194 THEN 'XPT'
            WHEN fx.SourceCurrencyId = 195 THEN 'YER'
            WHEN fx.SourceCurrencyId = 196 THEN 'ZAR'
            WHEN fx.SourceCurrencyId = 197 THEN 'ZMK'
            WHEN fx.SourceCurrencyId = 198 THEN 'ZWD'
            WHEN fx.SourceCurrencyId = 199 THEN 'UKP'
            WHEN fx.SourceCurrencyId = 200 THEN 'ROL'
            WHEN fx.SourceCurrencyId = 201 THEN 'GHS'
            WHEN fx.SourceCurrencyId = 202 THEN 'MZM'
            WHEN fx.SourceCurrencyId = 203 THEN 'SDD'
            WHEN fx.SourceCurrencyId = 204 THEN 'ZMW'
            WHEN fx.SourceCurrencyId = 205 THEN 'SSP'
            WHEN fx.SourceCurrencyId = 206 THEN 'CUC'
            WHEN fx.SourceCurrencyId = 207 THEN 'BYN'
            WHEN fx.SourceCurrencyId = 208 THEN 'PRB'
            WHEN fx.SourceCurrencyId = 209 THEN 'TMT'
            WHEN fx.SourceCurrencyId = 222 THEN 'MRU'
            WHEN fx.SourceCurrencyId = 223 THEN 'STN'
            WHEN fx.SourceCurrencyId = 224 THEN 'VES'
            WHEN fx.SourceCurrencyId = 225 THEN 'BOV'
            WHEN fx.SourceCurrencyId = 226 THEN 'CHE'
            WHEN fx.SourceCurrencyId = 227 THEN 'CLF'
            WHEN fx.SourceCurrencyId = 228 THEN 'COU'
            WHEN fx.SourceCurrencyId = 229 THEN 'MXV'
            WHEN fx.SourceCurrencyId = 230 THEN 'UYI'
            WHEN fx.SourceCurrencyId = 231 THEN 'UYW'
            WHEN fx.SourceCurrencyId = 232 THEN 'XSU'
            WHEN fx.SourceCurrencyId = 233 THEN 'XUA'
            WHEN fx.SourceCurrencyId = 234 THEN 'ZWL'
            WHEN fx.SourceCurrencyId = 235 THEN 'CHW'
            WHEN fx.SourceCurrencyId = 236 THEN 'CNH'
            WHEN fx.SourceCurrencyId = 237 THEN 'CNT'
            WHEN fx.SourceCurrencyId = 238 THEN 'KID'
            WHEN fx.SourceCurrencyId = 239 THEN 'NIS'
            WHEN fx.SourceCurrencyId = 240 THEN 'NTD'
            WHEN fx.SourceCurrencyId = 241 THEN 'SLS'
            WHEN fx.SourceCurrencyId = 242 THEN 'RMB'
            WHEN fx.SourceCurrencyId = 243 THEN 'USN'
            Else 'None'
        END AS Currency,
        fx.Amount AS Amount
    FROM
        (
            SELECT
                UniqueID
            FROM
                FxRateType
            WHERE
                (
                    Month(GETDATE()) = 1
                    AND NAME LIKE '%' + CAST((Year(GETDATE()) -1) AS NVARCHAR(4)) + 'M2%'
                )
                OR (
                    Month(GETDATE()) > 1
                    AND NAME LIKE '%' + CAST(Year(GETDATE()) AS NVARCHAR(4)) + 'M2%'
                )
        ) fxr
        LEFT JOIN(
            SELECT
                *
            FROM
                FxRate
            WHERE
                (
                    Month(GETDATE()) = 1
                    AND TimeId = CAST((Year(GETDATE()) -1) AS NVARCHAR(4)) + '004000'
                )
                OR (
                    Month(GETDATE()) > 1
                    AND TimeId = CAST(Year(GETDATE()) AS NVARCHAR(4)) + '004000'
                )
        ) fx ON fx.FxRateTypeID = fxr.UniqueID

 

sameburn's avatar
sameburn
Contributor II
3 months ago

Hi Marco 

Since this information does not exist in a table.  The only option available other than your CASE statement would be to get creative and do something with your SQL in a Business Rule instead.  For example, if you move your logic to a Business Rule e.g. Dashboard DataSet.  You have a bit more flexibility with how you can reference things e.g. using a dictionary.  Below is an untested example of how you can do this. 

Here we create a placeholder NVARCHAR column called Currency in our SQL query.  We then loop through the DataTable result and use a Dictionary (this time the ID is the Key and the Name is the Value) to lookup the Name based on the SourceCurrencyId and populate the Currency column with the corresponding Currency Name

This should give you same result, without that big CASE statement. 

	' Get Currency Dictionary
	Dim currencyDict As Dictionary(Of Integer, String) = Currency.GetItems().ToDictionary(Function(kvp) kvp.Id, Function(kvp) kvp.Name)
	
	' Declare SQL	
	Dim sql As String = "WITH CTE
						 AS (
						 SELECT 
						  fxt.[UniqueID]
						 ,fxt.Name
						 ,fx.SourceCurrencyId
						 ,'Unknown' Currency
						 ,fx.Amount
						 FROM [FxRateType] fxt
						 LEFT JOIN [FxRate] fx
							ON fxt.UniqueID = fx.FxRateTypeID
						 WHERE                 
						 (
		                    Month(GETDATE()) = 1
		                    AND Name LIKE '%' + CAST((Year(GETDATE()) -1) AS NVARCHAR(4)) + 'M2%'
		                 )
		                 OR (
		                    Month(GETDATE()) > 1
		                    AND Name LIKE '%' + CAST(Year(GETDATE()) AS NVARCHAR(4)) + 'M2%'
		                 )
						    )
						 SELECT SourceCurrencyId, Currency, Amount FROM CTE"
	
	'Execute Query on App DB
	Dim dt As New DataTable()
	Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	    dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString(), True)
        If Not dt Is Nothing Then
			' prepare placeholder column
			dt.Columns("Currency").ReadOnly = False
			dt.Columns("Currency").MaxLength = -1
			' Loop on rows
            For Each dr As DataRow In dt.Rows
                'Process rows
				dr.BeginEdit
					' Lookup Currency Name from Dictionary by passing in SourceCurrencyId DataRow and assign value to Currency column
                	dr("Currency") = currencyDict.Item(dr("SourceCurrencyId"))
				dr.EndEdit
            Next
        End If
	End Using