Optional SQL Select with REST Data Connection

[joshuahirsch]

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!

[Vu_Nguyen · Accepted solution]

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;