Optional SQL Select with REST Data Connection

joshuahirsch — Wed, 22 Dec 2021 14:44:26 GMT

Hey there,

so I am developing some Dashboards for a many customers.

I know the maximum or all possible fields. So it's a REST Connection, and they get a Response with just the fields that are existing.

My Question now:
How can I develop the Script so, that if there ist every possible field in the REST Response, everythings fine, and if there are a lot of fields missing, how will the script still continue to work?

Thanks in advance!

Re: Optional SQL Select with REST Data Connection

Vu_Nguyen — Mon, 25 Mar 2019 15:37:44 GMT

Hi,

SQL statement of REST connector does not throw any error if a filed does not exist in the query. It simply skips non-existing fields.

The auto-generated LOAD statement following the SQL query (if you create query from the Select Data wizard) however, may throw errors if the field does not exist. You can overcome this by replacing LOAD [fieldNames] with LOAD * . Or simply remove the LOAD statement.

For example, the following piece of script loads data from https://jsonplaceholder.typicode.com/posts, including field "nonexisting" that is not in the API source.

LIB CONNECT TO 'testJSON';

RestConnectorMasterTable:
SQL SELECT 
	"userId",
	"id",
	"title",
	"body",
        "nonexisting" //this field does not exist in the source
FROM JSON (wrap on) "root";

[root]:
NoConcatenate LOAD	*
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

topic Optional SQL Select with REST Data Connection in Connectivity & Data Prep

Optional SQL Select with REST Data Connection

Re: Optional SQL Select with REST Data Connection