Remote Queries

SELECT * FROM TABLE
(
	EXECUTE_REMOTE_QUERY
	(
		QUERY => '<remote query>',
		OPTIONS => KV_PAIRS(datasource_name = '<data source name>')
	)
)

SELECT * FROM TABLE
(
	EXECUTE_REMOTE_QUERY
	(
		QUERY => '
			SELECT
			    e.last_name || '', '' || e.first_name AS "Employee_Name",
			    m.last_name || '', '' || m.first_name AS "Manager_Name"
			FROM
			    example.employee e
			    LEFT JOIN example.employee m ON e.manager_id = m.id
			WHERE
			    e.dept_id IN (1, 2, 3)
			ORDER BY
			    m.id ASC NULLS FIRST,
			    e.hire_date
		',
		OPTIONS => KV_PAIRS(datasource_name = 'example.jdbc_ds')
	)
)

SELECT
    e.last_name || ', ' || e.first_name AS "Employee_Name",
    d.code
FROM
	TABLE
	(
		EXECUTE_REMOTE_QUERY
		(
			QUERY => '
				SELECT last_name, first_name, dept_id
				FROM example.employee
			',
			OPTIONS => KV_PAIRS(datasource_name = 'example.jdbc_ds')
		)
	) e,
	example.department d
WHERE e.dept_id = d.id
ORDER BY 1