Query Help - 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 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-04-05 : 12:26:48

Is there an easy way to do this that I'm missing?


select  @localeId = LocaleId
from    Locale
where   LanguageCultureCode = @languageCultureCode

select  @defaultLocaleId = LocaleId
from    Locale
where   LanguageCultureCode = 'en-US'

select  PL.DisplayName
from    Product P
        inner join ProductLocale PL on PL.ProductId = P.ProductId and PL.LocaleId = --(the specified locale or default locale if it doesn't exist)

as the comment indicates in the code above, i want to return the display names for the specified language. or if it doesn't exist, the default english display name.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 12:32:06

[code]select PL.DisplayName
from Product P
inner join ProductLocale PL where PL.ProductId = P.ProductId and PL.LocaleId = Coalesce(@localeId, @defaultLocaleId)[/code]

Note that above query will work assuming @localeID is set to NULL when desired locale is not found.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-04-05 : 12:33:33

actually the locale will be found, as it will exist in the locale table. it may not exist in the ProductLocale table. meaning the product will not have a definition for the existing locale. so that won't work for what i'm trying to do

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 12:35:57

Or this:


select  @localeId = LocaleId
from    Locale
where   LanguageCultureCode = @languageCultureCode

select  @defaultLocaleId = LocaleId
from    Locale
where   LanguageCultureCode = 'en-US'

select  	PL.DisplayName
from	Product P
	join ProductLocale PL on PL.ProductId = P.ProductId
	join (Select LocaleId from Locale where LanguageCultureCode in (@languageCultureCode, 'en-US') ) t on PL.LocaleId = t.LocaleId

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-04-05 : 12:46:30

that almost works. if both exist, it returns both. i want the specified language code to take precedence

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-04-05 : 13:00:14

FYI this doesn't work either:


declare @localeId int
declare @defaultLocaleId int

select  @localeId = LocaleId
from    Locale
where   LanguageCultureCode = 'es-MX'

select  @defaultLocaleId = LocaleId
from    Locale
where   LanguageCultureCode = 'en-US'

select  PL.DisplayName
from    Product P
        inner join ProductLocale PL on PL.ProductId = P.ProductId and PL.LocaleId = (case when PL.LocaleId = @localeId then @localeId else @defaultLocaleId end)

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-04-05 : 16:15:15

ok i got this to work. here's the solution:


declare @localeId int
declare @defaultLocaleId int

select  @localeId = LocaleId
from    Locale
where   LanguageCultureCode = 'es-MX'

select  @defaultLocaleId = LocaleId
from    Locale
where   LanguageCultureCode = 'en-US'

select coalesce (
          max (case when localeId = @localeId then DisplayName end),
          max (case when localeId = @defaultLocaleId then displayName end),
          ''
       ) as displayName
  from ProductLocale PL
where LocaleId in (@localeId, @defaultLocaleId)
group by ProductId
order by DisplayName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources