SQL: how to use split_part in a multi table query?

Question

I am using PostgreSQL and Windows.

I have a diagram of the tables and their relationships:

I am trying to produce a result that has firstname, lastname and country.

firstname = because I only have "givenname" which includes first+middle names, I need to use "split_part" function to extract only the first name.

However, I cannot produce the results:

SELECT a.split_part(givenname, ' ', 1) AS "First Name", a.lastname AS "Last Name", b."Name" AS "Country" FROM employee as a, country as b
WHERE a.countryoforigin = b.countryID
ORDER BY givenname;

this shows error:

Now, if I don't use split_part, and directly use "givenname":

SELECT "givenname" AS "First Name", a.lastname AS "Last Name", b."Name" AS "Country" FROM employee as a, country as b
WHERE a.countryoforigin = b.countryID
ORDER BY givenname;

Is there a way to produce the result using split_part function? What would be the query?

Answer 1

You seem to be using “a.split_part” for some reason, the function is just “split_part”. Table aliases are used with columns, not functions. Therefore, the syntax should be: split_part(a.givenname.....

By the way, the Oracle-style join syntax you are using was superseded by ANSI join syntax about 30 years ago; you really ought to stop using it.

Here is one article about this:

https://oracle-base.com/articles/misc/sql-for-beginners-joins