Syntax EXTERNAL_QUERY

[tl99]

I need to implement in the first query the external query (2. screentshot). could someone help me? thank you so much.

[ms4446]

The "Syntax EXTERNAL_QUERY" error is indicating that there is a syntax error in the use of the EXTERNAL_QUERY() function. The correct syntax for the EXTERNAL_QUERY() function is:

EXTERNAL_QUERY(connection_id, query_string)

In the original query, the connection ID was not enclosed in quotation marks. The corrected version of the query is:

SELECT
LastName
FROM EXTERNAL_QUERY("projects/bi-2023hs-proj-tm/locations/eu/connections/bi-2023hs-connection-tm",
"SELECT LastName from Person.Person;");

Corrected Version of 2nd Query 

The corrected version of 2nd Query is:

SELECT
LastName
FROM EXTERNAL_QUERY("projects/bi-2023hs-proj-tm/locations/eu/connections/bi-2023hs-connection-tm",
"SELECT LastName from Person.Person;");

Subquery Implementation

The subquery implementation in the updated version of 1st Query is technically correct. However, it is important to note that this will join the results of the external query with the results of the main query on the LastName column. This means that if there are multiple people with the same last name in both datasets, they will all be joined together.

Updated Version of 1st Query 

The updated version of 1st Query  can be modified to use the IN operator to filter the results of the main query based on the results of the external query. The corrected version of 1st Query  is:

SELECT
p1.LastName
FROM
Person.Person as p1
INNER JOIN
Sales.Customer as t1 ON
p1.CustomerID = t1.CustomerID
WHERE
p1.LastName IN (SELECT LastName FROM EXTERNAL_QUERY("projects/bi-2023hs-proj-tm/locations/eu/connections/bi-2023hs-connection-tm",
"SELECT LastName from Person.Person;"))
ORDER BY
t1.CustomerID DESC
LIMIT 1;

This will ensure that only rows from the main query where the LastName column matches a value in the results of the external query will be returned.

Combining Data from Both Queries

If the goal is to combine data from both queries, then the join conditions and selected columns can be adjusted. For example, the following query would join the results of the two queries on the CustomerID column:

SELECT
p1.LastName,
p2.LastName
FROM
Person.Person as p1
INNER JOIN
(SELECT CustomerID, LastName FROM EXTERNAL_QUERY("projects/bi-2023hs-proj-tm/locations/eu/connections/bi-2023hs-connection-tm",
"SELECT CustomerID, LastName from Person.Person;")) AS p2 ON
p1.CustomerID = p2.CustomerID
ORDER BY
p1.CustomerID DESC
LIMIT 1;

This would return a table with two columns: LastName from the main query and LastName from the external query