Translate to foreign languages in SQL query

I am trying to figure out how to translate names stored in an AS/400 table called Clients (ID, FNAME, MNAME, LNAME,...) into foreign languages. The first attempt is doing that into Arabic.

The DB admin created a dictionary table with 2 columns ARABDICT (ENGNAME, ARBNAME). He did not create a primary key in order to join the tables.

I am using this query and it is not returning anything, once I got an error

Correlation without qualification occurred for column FNAME to table Clients

and then no errors are even returned.

SELECT fname, a.arbname
FROM QS36F.Clients, QS36F.ARABDICT a
WHERE a.arbname = (SELECT arbname 
                   FROM QS36F.Clients c, QS36F.ARABDICT 
                   WHERE c.FNAME LIKE '%engname%')

Solution

The issue with your query is that it tries to use subqueries and lacks proper correlation and joins. You should use a join directly between the Clients table and the ARABDICT table based on the ENGNAME. Here’s how you can write the query to achieve the translation:

SELECT c.FNAME, a.ARBNAME
FROM QS36F.Clients c
JOIN QS36F.ARABDICT a
ON c.FNAME = a.ENGNAME;

In this query:

We perform an inner join between Clients (c) and ARABDICT (a).
The join condition is c.FNAME = a.ENGNAME, which matches the English name in the Clients table with the English name in the ARABDICT table.

This query will return all first names from the Clients table along with their corresponding Arabic translations from the ARABDICT table. If you need to match other name columns (like MNAME, LNAME, etc.), you can adjust the query accordingly:

SELECT c.FNAME, a.ARBNAME AS FNAME_ARABIC,
       c.MNAME, a2.ARBNAME AS MNAME_ARABIC,
       c.LNAME, a3.ARBNAME AS LNAME_ARABIC
FROM QS36F.Clients c
LEFT JOIN QS36F.ARABDICT a ON c.FNAME = a.ENGNAME
LEFT JOIN QS36F.ARABDICT a2 ON c.MNAME = a2.ENGNAME
LEFT JOIN QS36F.ARABDICT a3 ON c.LNAME = a3.ENGNAME;

This version will give you Arabic translations for the first name, middle name, and last name, if they exist.