Straight & Reverse Check - kcehc eserveR & Straight

Question

Post reply

Straight & Reverse Check - kcehc eserveR & Straight

karthik M

One Orange Chip

Points: 29139
More actions
December 4, 2012 at 3:59 am

#265730

All,
I have two table as below.
create table currency
(
currencypair varchar(30) not null,
rate_value numeric(18,6) null
)
insert into currency
select 'BNGCZK',null
union
select 'CHFCLP',null
union
select 'CHFLKR',null
union
select 'CHFPEN',null
union
select 'CHFSIT',null
union
select 'CHFZMK',null
union
select 'CZKSIT',null
union
select 'DKKSIT',null
union
select 'EURAED',1.666000000
union
select 'EURBGN',1.9556000000
union
select 'EURBHD',1.4819000000
union
select 'EURBRL',1.3569000000
union
select 'EURCAD',1.3033000000
union
select 'EURCHF',1.2179300000
union
select 'EURCLP',null
union
select 'EURCZK',1.8650000000
union
select 'EURDKK',1.4365000000
union
select 'EUREEK',1.6426000000
union
select 'EUREGP',1.7137600000
union
select 'EURGBP',1.8253000000
union
select 'EURHKD',1.9230000000
union
select 'EURHRK',1.5305000000
union
select 'EURHUF',1.0000000000
create table off_currency_pair
(
ccy1 varchar(10),
ccy2 varchar(10),
rate_value numeric(18,6) null
)
insert into off_currency_pair
select 'BNG','CZK',1.0
union all
select 'CHF','CLP',1.0
union all
select 'CHF','LKR',1.0
union all
select 'CHF','PEN',1.0
union all
select 'CHF','SIT',1.0
union all
select 'CHF','ZMK',1.0
union all
select 'AED','AED',1.0
union all
select 'BGN','EUR',1.0
union all
select 'BHD','EUR',1.0
union all
select 'EUR','BRL',1.0
union all
select 'CAD','EUR',1.0
union all
select 'EUR','CHF',1.2
union all
select 'EUR','CLP',null
union all
select 'EUR','CZK',1.0
union all
select 'EUR','DKK',1.0
#1)correct matching
currency.currencypair = off_currency_pair.ccy1 & off_currency_pair.ccy2
#2) Reverse matching
currency.currencypair = off_currency_pair.ccy2 & off_currency_pair.ccy1
My Query:
select currencypair, ccy1, ccy2
from currency_test , off_currency_pair
where currency_test.currencypair = off_currency_pair.ccy1 + off_currency_pair.ccy2
Output1:
currencypairccy1ccy2
BNGCZKBNGCZK
CHFCLPCHFCLP
CHFLKRCHFLKR
CHFPENCHFPEN
CHFSITCHFSIT
CHFZMKCHFZMK
EURBRLEURBRL
EURCHFEURCHF
EURCLPEURCLP
EURCZKEURCZK
EURDKKEURDKK
#2)
select currencypair, ccy1, ccy2
from currency_test , off_currency_pair
where currency_test.currencypair = off_currency_pair.ccy2 + off_currency_pair.ccy1
currencypairccy1ccy2
EURBGNBGNEUR
EURBHDBHDEUR
EURCADCADEUR
I am looking for some alternate way (Interesting one! especially in a single query) to achieve the same.
Inputs are welcome!
karthik
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 1

I don't want to use

select currencypair, ccy1, ccy2

from currency_test , off_currency_pair

where (currency_test.currencypair = off_currency_pair.ccy2 + off_currency_pair.ccy1

or currency_test.currencypair = off_currency_pair.ccy1 + off_currency_pair.ccy2)

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

December 4, 2012 at 4:17 am

#1565211

I need a nice trick to do this 🙂

karthik

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 3

select currencypair, ccy1, ccy2

from currency_test , off_currency_pair

where currency_test.currencypair in (off_currency_pair.ccy2 + off_currency_pair.ccy1, off_currency_pair.ccy1 + off_currency_pair.ccy2)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lowell SSC Guru Points: 323518 More actions · Answer 4

great job on providing sample ddl and data!

does this do what you are after?

With CurrencySplit(currencypair,rate_value ,ccy1,ccy2)

AS

(

SELECT

currencypair,

rate_value,

LEFT(currencypair,3),

RIGHT (currencypair,3)

FROM currency

)

select

CurrencySplit.*,

T1.*

from CurrencySplit

INNER JOIN off_currency_pair T1

ON CurrencySplit.ccy1 = T1.ccy1

AND CurrencySplit.ccy2 = T1.ccy2

UNION

select

CurrencySplit.*,

T2.*

from CurrencySplit

INNER JOIN off_currency_pair T2

ON CurrencySplit.ccy2 = T2.ccy1

AND CurrencySplit.ccy1 = T2.ccy2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 5

Here's another

SELECT currencypair,LEFT(currencypair,3) AS ccy1,RIGHT(currencypair,3) AS ccy2

FROM currency

INTERSECT

(SELECT ccy1 + ccy2,ccy1,ccy2

FROM off_currency_pair

UNION

SELECT ccy2 + ccy1,ccy2,ccy1

FROM off_currency_pair);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

And another...but it does involve two tables scans, so I wouldn't recommend it...

SELECT currencypair

, ccy1

, ccy2

FROM

currency

, off_currency_pair

WHERE charindex(ccy1, currencypair) * charindex(ccy2, currencypair) = 4

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

GSquared SSC Guru Points: 260824 More actions · Answer 7

Are you looking for interesting/clever, or effective?

Honestly, all of the answers given so far are just different ways to tell the optimizer to do (essentially) the same thing. Of course, that's the whole point of a declarative language like SQL, where you say what end result you want and let the optimizer figure out how to do it.

If all you want it alternative ways to write it, here's my attempt at "clever":

ON ccy1 + ccy2 IN (currencypair, right(currencypair, 3) + left(currencypair, 3))

And here's "efficient":

ON currencypair in (ccy1 + ccy2, ccy2 + ccy1)

The second one might actually allow an index seek on the join, instead of a full scan. You'd need to test with a lot more data than your provided sample. It doesn't look at "second halfs" of any strings, so it's all leading edges, if the columns are indexed. It's just the OR/IN construct that might (or might not) ruin SARGability.

Note, this is the same as the first answer given, by Mark. There are only so many ways to skin this particular cat, without going into really silly possibilities. It's also the same as the one you said you don't want to use, it just has IN instead of OR. But IN is just shorthand for OR.

The Union versions are also the same. Just more typing to get the same result.

So, why the desire for a clever answer? And why the rejection of an effective one? Just curiousity, or some real need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Gsquard,

So, why the desire for a clever answer? And why the rejection of an effective one? Just curiousity, or some real need?

#1 --> Curiosity to get the diffrent answer

As you said (There are only so many ways to skin this particular cat)

#2 --> real need

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 9

The concatenated currencies iviolates of First Normal Form

How to design the first table?

Do I need to follow the same approach which you designed for both the tables?

karthik

ScottPletcher SSC Guru Points: 101118 More actions · Answer 10

Yes, if you can, it would be much better to store from and to currencies as separate columns.

But, either way, you don't need to store 2 conversion rates, only one, unless you allow non-standard rates that only apply one way. So, for example, always BNGCZK and never CZKBNG.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".