Replace empty/null string with result from another record

Question

I have a language table and want retrieve specific records for a selected language. However, when there is no translation present I want to get the translation of another language.

TRANSLATIONS

TAG      LANG  TEXT
"prog1" |  1 | "Programmeur"
"prog1" |  2 | "Programmer"
"prog1" |  3 | "Programista"
"prog2" |  1 | ""
"prog2" |  2 | "Category"
"prog2" |  3 | "Kategoria"
"prog3" |  1 | "Actie"
"prog3" |  2 | "Action"
"prog3" |  3 | "Dzialanie"

PROGDATA

ID | COL1   | COL2 
 1 | "data" | "data"
 2 | "data" | "data"
 3 | "data" | "data"

If I want translations from language 3 based on the ID's in table PROGDATA then I can do:

SELECT TEXT FROM TRANSLATIONS, PROGDATA 
WHERE TRANSLATIONS.TAG="prog" & PROGDATA.ID 
    AND TRANSLATIONS.LANG=3

which would give me:
"Programista"
"Kategoria"
"Dzialanie"

In case of language 1 I get an empty string on the second record:
"Programmeur"
""
"Actie"

How can I replace the empty string with, for example, the translation of language 2?
"Programmeur"
"Category"
"Actie"

I tried nesting a new select query in an IIf() function but that obviously did not work.

SELECT 
  IIf(TEXT="",
    (SELECT TEXT FROM TRANSLATIONS, PROGDATA 
    WHERE TRANSLATIONS.TAG="prog" & PROGDATA.ID 
    AND TRANSLATIONS.LANG=2),TEXT) 
FROM TRANSLATIONS, PROGDATA 
WHERE TRANSLATIONS.TAG="prog" & PROGDATA.ID 
AND TRANSLATIONS.LANG=3

If the preferred language is not available, how do you choose which language to use instead? — Smandoli, Dec 06 '13 at 17:50
The default language is always populated e.g. that could be language 2. Anyway, it's hard coded. — David K, Dec 09 '13 at 08:30
If the default language is not populated then it's a developer error ;) In that case I should generate an error (but, I will do that outside SQL). — David K, Dec 09 '13 at 08:42

score 0 · Answer 1 · answered Dec 06 '13 at 17:54

A SWITCH or CASE statement may work well. But try this:

SELECT 
  IIf(TEXT="",
    (SELECT TEXT AS TEXT_OTHER FROM TRANSLATIONS, PROGDATA 
    WHERE TRANSLATIONS.TAG="prog" & PROGDATA.ID 
    AND TRANSLATIONS.LANG=2),TEXT) AS TEXT_FINAL

I am using TEXTOTHER and TEXTFINAL to reduce ambiguity in your field names. Sometimes this helps.

You may even need to apply the principle to the table name:

(SELECT TEXT AS TEXT_OTHER FROM TRANSLATIONS AS TRANSLATIONS_ALT...

Also, make sure your criterion is correct: an empty string, not a Null value.

 IIf(TEXT="", ...
 IIf(ISNULL(TEXT), ...

I could not get this to work in Access. "Invalid Memo, OLE, or Hyperlink Object in subquery 'TEXT'". It probably happens because the subquery returns multiple results. So I tried comparing the TAG from the first and second query: TRANSLATIONS_ALT.TAG = TRANSLATIONS_MAIN.TAG. However, that did not help. — David K, Dec 09 '13 at 11:41

score 0 · Answer 2 · answered Dec 06 '13 at 18:00

0

You can join TRANSLATIONS table again to get a "default" translation and use a CASE in the SELECT Statement.

SELECT 
CASE 
   WHEN ISNULL(Translation.TEXT,"") = "" THEN DefaultLang.TEXT
   ELSE Translation.Text
END
FROM TRANSLATIONS AS DefaultLang,TRANSLATIONS as Translation, PROGDATA 
WHERE 
DefaultLang.TAG="prog" & PROGDATA.ID AND Translation.TAG="prog" & PROGDATA.ID
AND DefaultLang.LANG=2
AND Translation.LANG=3

answered Dec 06 '13 at 18:00

fossilcoder

59
6

Access uses `Nz()` for this purpose; `IsNull()` returns only True or False. However, it is recommended to build an `Iif()` statement instead of using `Nz()`, due to Access' engine preferences. See http://allenbrowne.com/QueryPerfIssue.html#nz – Smandoli Dec 06 '13 at 18:32
Apparently, there is no CASE statement in MS Access. http://stackoverflow.com/questions/772461/case-statement-in-access – David K Dec 09 '13 at 11:16
Instead you have to use IIf() – David K Dec 09 '13 at 11:35

score 0 · Answer 3 · answered Dec 06 '13 at 19:47

it is a pseudo-code idea... I d try to add a checkEmpty function for each value returned. if is not empty, return the same.. if is empty return a new search from other languaje. You need to cheak that the new value is not empty again of course.

create function checkEmpty( @word varchar(10), @languageNumber integer) returns varchar(10)
  as
  begin
    declare @newWord
    declare @newlanguage

   if @word <> '' then return @word else
        begin
        //select new language
        case  languageNumber of
        3 then @newlanguage = 1;
        2 then @newlanguage = 3;
        1 then @newlanguage = 2;

        //search new lenguage
        @newWord= SELECT   TEXT  FROM TRANSLATIONS, PROGDATA 
        WHERE TRANSLATIONS.TAG="prog" & PROGDATA.ID 
            AND TRANSLATIONS.LANG=@newlanguage  

    return @newWord
    end;

 end;

//FUNCTION CALL

SELECT   dbo.checkEmpty(TEXT)   FROM TRANSLATIONS, PROGDATA 
WHERE TRANSLATIONS.TAG="prog" & PROGDATA.ID 
    AND TRANSLATIONS.LANG=3

Calling a VBA function from an Access query creates problems for efficiency and, more importantly in most cases, code maintainability. So this can't be recommended over a straight SQL solution such as was accepted. It is, however, a creative and workable idea. — Smandoli, Dec 09 '13 at 14:26

score 0 · Accepted Answer · answered Dec 09 '13 at 11:53

0

I canabalized the solutions of @fossilcoder and @Smandoli and merged it in one solution:

SELECT
  IIf (
    NZ(TRANSLATION.Text,"") = "", DEFAULT.TEXT, TRANSLATION.TEXT)
FROM 
  TRANSLATIONS AS TRANSLATION,
  TRANSLATIONS AS DEFAULT, 
  PROGDATA
WHERE
  TRANSLATION.Tag="prog_" & PROGDATA.Id
  AND 
  DEFAULT.Tag="prog" & PROGDATA.Id
  AND
  TRANSLATION.LanguageId=1 
  AND 
  DEFAULT.LanguageId=2

I never thought of referencing a table twice under a different alias

answered Dec 09 '13 at 11:53

David K

333
2
12

1

Why IF when you can use NZ (or ISNULL) directly? ....SELECT NZ(TRANSLATION.Text, DEFAULT.TEXT).... – Dec 09 '13 at 11:58
NZ(TRANSLATION.Text,"") = "" is a double check. It first checks for NULL and then for an empty string. DEFAULT should be used when TRANSLATION is either NULL or "" – David K Dec 09 '13 at 14:41

Replace empty/null string with result from another record

4 Answers4