October 14, 2010 at 4:06 am
Hi All,
I have situation like below.
I have coulmn called currencylocal in table. I am using currencylocal filed my report query.
Is there any way to add currency name or currency symbol based on the country name(Country name is also there in same table and in report query) to the currency in the report query.
present display:
Country currencylocal
United Kingdom20
Ireland 3423
India 20
Expected display:
Country currencylocal
United Kingdom20 Found symbol/GBP
US 3423$/dollor
India 20 symbol/Rs
Thanks in advance.
Ravi
October 14, 2010 at 4:19 am
I think you will have to add that column to your report from an appropriate table, mapping countries with currencies.
-- Gianluca Sartori
October 14, 2010 at 4:22 am
Yes.Right but that leads to be lot changes required in the DB and application side.
This report query just out of the application.
October 14, 2010 at 4:37 am
p.ravirao (10/14/2010)
Yes.Right but that leads to be lot changes required in the DB and application side.This report query just out of the application.
Well in that case. A CASE would probably be best
select
Country,
case when Country = 'United Kingdom' then '£'
when Country = 'Ireland' then '$'
when .....
else ''
end
from <table>
But seriously a table containing that would be best. It would be reusable and updatable. Like when a country changes currency. Then you would have to change the report(s) instead of just updatering the table.
/T
October 14, 2010 at 5:33 am
Thanks a lot. switch case is working fine.But the excuetion time is high...
October 14, 2010 at 5:52 am
p.ravirao (10/14/2010)
Thanks a lot. switch case is working fine.But the excuetion time is high...
Well since i dont know how many countries you have to support its hard to know. A case with every country in the world i would guess would be "rough".
Could do a table variable in that case.
Something like
declare @Currency table
(
Country varchar(64),
CurrencySymbol varchar(8)
primary key (Country)
)
insert into @Currency(Country, CurrencySymbol)
values
('Ireland', '$'),
('United Kingdom', '£'), ....
select t.*, IsNull(c.CurrencySymbol, '')
from <table> t
left join @Currency on c.Country = t.Country
Though preferably you would want something like CountryCode instead of the actual Countryname. To keep the size of the variable as small as possible (and the primary key)
/T
October 14, 2010 at 5:59 am
Thanks very much. it is very much useful stuff.I will test with table variable.
I have 45 countrries in table.
Here is my query snippet..i did changed as for my reqs.
select
ISNULL(ts_Countries.CountryName, 'Unknown Country') as Country,
case when ts_Countries.CountryName = 'United Kingdom' then CONVERT(varchar(12), CostLocal, 1) + '£'
when ts_Countries.CountryName = 'France' then CONVERT(varchar(12), CostLocal, 1) + '$'
when ts_Countries.CountryName = 'India' then CONVERT(varchar(12), CostLocal, 1) + 'Rs'
when ts_Countries.CountryName = 'Australia' then CONVERT(varchar(12), CostLocal, 1) + '$'
when ts_Countries.CountryName = 'Spain' then CONVERT(varchar(12), CostLocal, 1) + '$'
when ts_Countries.CountryName = 'New Zealand' then CONVERT(varchar(12), CostLocal, 1) + '$'
else ''
end,
from <tablename>
thanks again...
Ravi
October 14, 2010 at 6:03 am
select ISNULL(ts_Countries.CountryName, 'Unknown Country') as Country,
case when ts_Countries.CountryName = 'United Kingdom' then CONVERT(varchar(12), CostLocal, 1) + '£'
else ''
end,
Can we have a coulmn here instead of No cloumn name.?.
I mean case when ts_Countries.CountryName = 'United Kingdom' then CONVERT(varchar(12), CostLocal, 1) + '£'
As Costlocalwithsymbol...
giving error?
thnx,
Ravi
October 14, 2010 at 6:06 am
The column name goes after the END
case when ....
when ....
end as <column name>
/T
October 14, 2010 at 6:12 am
Perfect !.. thanks.Dont mind I am not familer with SQL..
October 14, 2010 at 6:18 am
p.ravirao (10/14/2010)
Thanks very much. it is very much useful stuff.I will test with table variable.I have 45 countrries in table.
Here is my query snippet..i did changed as for my reqs.
select
ISNULL(ts_Countries.CountryName, 'Unknown Country') as Country,
case when ts_Countries.CountryName = 'United Kingdom' then CONVERT(varchar(12), CostLocal, 1) + '£'
when ts_Countries.CountryName = 'France' then CONVERT(varchar(12), CostLocal, 1) + '$'
when ts_Countries.CountryName = 'India' then CONVERT(varchar(12), CostLocal, 1) + 'Rs'
when ts_Countries.CountryName = 'Australia' then CONVERT(varchar(12), CostLocal, 1) + '$'
when ts_Countries.CountryName = 'Spain' then CONVERT(varchar(12), CostLocal, 1) + '$'
when ts_Countries.CountryName = 'New Zealand' then CONVERT(varchar(12), CostLocal, 1) + '$'
else ''
end,
from <tablename>
thanks again...
Ravi
select
ISNULL(ts_Countries.CountryName, 'Unknown Country') as Country,
CONVERT(varchar(12), CostLocal, 1) +
case when ts_Countries.CountryName = 'United Kingdom' then '£'
when ts_Countries.CountryName = 'France' then '$'
when ts_Countries.CountryName = 'India' then 'Rs'
when ts_Countries.CountryName = 'Australia' then '$'
when ts_Countries.CountryName = 'Spain' then '$'
when ts_Countries.CountryName = 'New Zealand' then '$'
else ''
end,
from <tablename>
You can add the Case to another column that you have in the select. Makes it a bit easier to read. And i would guess a bit easier for SQL to handle.
/T
October 15, 2010 at 3:02 am
Thanks CELKO.
You are right. ISO 4217 currency codes look up is the best option to use.
Thanks again.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply