QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

QUERY

Author

Topic

evogli
Starting Member

7 Posts

Posted - 2012-02-09 : 13:07:08

Hello,

I need a query for this scenario. I have a table with ID, LANG, VALUE for language strings.

I want to select the value for a specific language through a stored procedures, but if there's not record for that language, I want to select where lang = 1 by default.

Something like this:

if(count(*) from LANGSTRINGS WHERE LANG = @LANG) IS NULL
select ID, VALUE from LANGSTRINGS where lang = 1
else
select id value from langstring where lang = @lang

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:10:36

if exists(select * from langstrings where lang = @lang)
begin
select * from langstrings where lang = @lang
end
else
begin
select * from langstrings where lang = 1
end

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 13:48:04

[code]
select columns...
from
(
select *,dense_rank() over (order by case when lang=@lang then 0 else 1 end asc) as rnk
from langstrings
where lang in (@lang,1)
)t
where rnk=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evogli
Starting Member

7 Posts

Posted - 2012-02-10 : 02:46:55

thanks for the replies. I think though that both example would fail in my case. Consider the following

ID LANG VALUE
1 1 'Cat'
2 2 'Gatto'
3 1 'Mouse'

So, Lang 2 has no entry for 'Mouse'. In this case if I do a select for language 2 it should select 'Mouse' as a default since there is no reference for it in Lang 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:29:55

sorry thats not clear. whats the rule for selecting Value associated to a language? there's no mouse existing for lang 2 in the table data shown so didnt understand how you will get mouse for lang 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evogli
Starting Member

7 Posts

Posted - 2012-02-10 : 09:43:39

Basically the idea is that language strings can be entered by an administrator for any new language they create. The master language has all the strings and they can only be updated not deleted. So for any new language there could be missing equivalents that the admin has not entered yet. There's more columns in the table to filter (like category, etc), but there's no need to show them.

The point is that if an equivalent entry is missing for language 2, the query should select the value from language 1. So if 'mouse' is missing for lang 2, then it should select 'mouse' from lang 1.

Hope it makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:55:50

what do you mean by equivalent entry? why you selected'Mouse' and not 'Cat'? whats rule for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evogli
Starting Member

7 Posts

Posted - 2012-02-10 : 10:50:12

Yes, sorry about the confusion. The extra columns provide those filters.

There's a column called IDENTIFIER. Each string has a unique identifier.

In this case:

ID LANG VALUE IDENTIFIER
1 1 'Cat' CAT_STRING
2 2 'Gatto' CAT_STRING
3 1 'Mouse' MOUSE_STRING

Because there is no MOUSE_STRING for lang2, then it should select the one from lang1.

sorry I didn't mention that from the beginning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:58:31

what if there was a lang 3 with MOUSE_STRING? in that case which one will it choose?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evogli
Starting Member

7 Posts

Posted - 2012-02-10 : 11:18:19

The default is lang1 (English). If admin has not entered strings for other languages, it should get the default from English.

This is so the gui will always have a value, and it's also easy to see which strings have not been created because they will be in english and stand out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 11:25:44

This should get you started up
you might need to add an insert to get this inserted to your table


SELECT m.LANG, COALESCE(n.VALUE,o.VALUE) AS VALUE, 
m.IDENTIFIER
FROM
(
SELECT LANG,IDENTIFIER
FROM (SELECT DISTINCT IDENTIFIER FROM table)t
CROSS JOIN (SELECT DISTINCT lang FROM table)t1
)m
LEFT JOIN table n
ON n.LANG = m.LANG
AND n.IDENTIFIER = m.IDENTIFIER
LEFT JOIN table o
ON o.IDENTIFIER = m.IDENTIFIER
AND o.LANG=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evogli
Starting Member

7 Posts

Posted - 2012-02-14 : 15:08:14

Thank you visakh16. Your query helped me out. Can I get your email address, I need to write you privately about another query. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 15:10:06

quote:
Originally posted by evogli

Thank you visakh16. Your query helped me out. Can I get your email address, I need to write you privately about another query. Thanks.

Please post it here as a new thread as more people will be able to take a look and you'll get quick results]
There're lot of experts here who might be able to give you much better suggestions...I'll definitely try my part too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources