Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Straight & Reverse Check - kcehc eserveR &...
Add to briefcase
12 posts, Page 1 of 2
12»»

Straight & Reverse Check - kcehc eserveR & Straight Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
karthik M
Posted Tuesday, December 04, 2012 3:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,008, Visits: 2,467
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:
currencypair ccy1 ccy2
BNGCZK BNG CZK
CHFCLP CHF CLP
CHFLKR CHF LKR
CHFPEN CHF PEN
CHFSIT CHF SIT
CHFZMK CHF ZMK
EURBRL EUR BRL
EURCHF EUR CHF
EURCLP EUR CLP
EURCZK EUR CZK
EURDKK EUR DKK


#2) 
select currencypair, ccy1, ccy2
from currency_test , off_currency_pair
where currency_test.currencypair = off_currency_pair.ccy2 + off_currency_pair.ccy1

currencypair ccy1 ccy2
EURBGN BGN EUR
EURBHD BHD EUR
EURCAD CAD EUR


I am looking for some alternate way (Interesting one! especially in a single query) to achieve the same.

Inputs are welcome!

karthik
Post #1392344
karthik M
Posted Tuesday, December 04, 2012 4:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,008, Visits: 2,467
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
Post #1392353
karthik M
Posted Tuesday, December 04, 2012 4:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,008, Visits: 2,467
I need a nice trick to do this :)

karthik
Post #1392354
Mark-101232
Posted Tuesday, December 04, 2012 4:18 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 1,499, Visits: 18,159
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)

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1392357
Lowell
Posted Tuesday, December 04, 2012 5:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 11,613, Visits: 27,669
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1392395
Mark-101232
Posted Tuesday, December 04, 2012 6:25 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 1,499, Visits: 18,159
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);

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1392420
mister.magoo
Posted Tuesday, December 04, 2012 6:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 1,289, Visits: 3,857
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
Post #1392441
GSquared
Posted Tuesday, December 04, 2012 6:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
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
Post #1392459
karthik M
Posted Tuesday, December 04, 2012 10:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,008, Visits: 2,467
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
Post #1392789
CELKO
Posted Thursday, December 06, 2012 9:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
These are not tables; they have no keys. The concatenated currencies iviolates of First Normal Form. The ISO currency code is CHAR(3). Your over-sized columns wll evenutally fill the talbe with garbage data. Throw out both tables and do it right:

CREATE TABLE Currency_Conversion_Rates
(source_currency_code CHAR(3) NOT NULL
CHECK (source_currency_code LIKE '[A-Z][A-Z][A-Z]'),
target_currency_code CHAR(3) NOT NULL,
CHECK (target_currency_code LIKE '[A-Z][A-Z][A-Z]'),
CHECK (source_currency_code <> target_currency_code),
PRIMARY KEY (source_currency_code, target_currency_code),
conversion_rate DECIMAL(10,5) NOT NULL
CHECK (conversion_rate > 0.00000));

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1393585
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.