May 9, 2019 at 12:20 am
Hi my friends,
I need some help here, In this case I need that query return only a record, but is two record.
-- Create Table
DECLARE @TableLanguage table(
ID INT,
Language VARCHAR(5));
-- Insert in Table
INSERT @TableLanguage
(ID, Language)
SELECT 1,'ES-AA'
UNION SELECT 1,'ES-BB'
UNION SELECT 1,'EN-CC'
UNION SELECT 1,'EN-DD'
UNION SELECT 1,'PT-BR'
UNION SELECT 1,'PT-PT'
-- Create var for to simulate for parameters
DECLARE @country CHAR(5);
-- Fills Variable
SET @country = 'PT-BR'
-- Query
SELECT
ID,
Language,
SUBSTRING(Language,1,2) Language,
SUBSTRING( @country,1,2) Idioma
FROM @TableLanguage
WHERE
ID = 1 AND
Language = ( CASE
--checks the language of the received country
WHEN Language = @country THEN @country
--Check first available language
WHEN (SUBSTRING(Language,1,2)) = (SUBSTRING(@country,1,2))
THEN (
SELECT TOP 1 MPD.Language
FROM @TableLanguage as MPD
WHERE SUBSTRING(MPD.Language,1,2) = (SUBSTRING(@country,1,2))
)
-- return language default
ELSE 'EN-DD'
END)
May 9, 2019 at 12:04 pm
You get 2 results because of the
ELSE 'EN-DD'
The else clause misses in 5 of 6 of your @table results, so you get the following result:
1x PT-BR
5x EN-DD
5x EN-DD matches 1x of your entries in @table, so does PT-BR, thats why you get 2 results in the query
Append the following query (which is a parts of your original post) to visualize what i mean
-- Queryselect *FROM @TableLanguage-- Queryresult of subselectselect CASE WHEN Language = @country THEN @country----Check first available language-- WHEN (SUBSTRING(Language,1,2)) = (SUBSTRING('PT-BR',1,2)) --(SUBSTRING(@country ,1,2))-- THEN (SELECT TOP 1 MPD.Language-- FROM @TableLanguage as MPD-- WHERE SUBSTRING(MPD.Language,1,2) = (SUBSTRING('PT-BR',1,2)) --(SUBSTRING(@country,1,2))-- )---- return language defaultELSE 'EN-DD' ENDFROM @TableLanguageI am not sure what you are trying to achieve.
If @country is found in @table, use it, if not use default EN-DD, but what is the other stuff about?
With this setup you will always get 2 results (only 1 (EN-DD) if @country is not in @table at all) because all the ones that do not match will be defaulted to EN-DD.
Possible solution maybe? Its mostly what you made + the inner join reduce the number of results to 1
-- Query
SELECT a.ID,
a.Language,
SUBSTRING(a.Language,1,2) Language,
SUBSTRING( @country,1,2) Idioma --SUBSTRING( @country,1,2) Idioma
FROM @TableLanguage a
inner join (select * from @TableLanguage where Language =@country) b
on a.Language = b.Language
WHERE a.ID = 1
--checks the language of the received country
AND a.Language =
( CASE WHEN a.Language = @country
THEN @country
ELSE 'EN-DD' END)
----Check first available language
--WHEN (SUBSTRING(Language,1,2)) = (SUBSTRING('PT-BR',1,2)) --(SUBSTRING(@country ,1,2))
--THEN (SELECT TOP 1 MPD.Language
--FROM @TableLanguage as MPD
--WHERE SUBSTRING(MPD.Language,1,2) = (SUBSTRING('PT-BR',1,2)) --(SUBSTRING(@country,1,2))
--)
--
---- return language default
--
--ELSE 'EN-DD' END)
I want to be the very best
Like no one ever was
May 10, 2019 at 4:47 pm
Hi ktflash, thank for your return, but I Can with query below. Thank you.
-- Query
SELECT top (1) @country as Objetivo, P.ID, P.Language
FROM @TableLanguage AS P
WHERE P.ID = 1
AND (P.Language = @country
or SUBSTRING(P.Language,1,2) = SUBSTRING(@country,1,2)
or P.Language = 'EN-DD')
ORDER BY case when P.Language = @country then 1
when SUBSTRING(P.Language,1,2) = SUBSTRING(@country,1,2) then 2
else 3 end;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply