If the developer has selected “Query”, “Stored Procedure” or” Table”, Nathean Analytics will display the following window where the developer can select the Data Connector they wish to query and then enter the query.
When creating a dataset on a Data Connector that links to a database the Nathean Analytics developer enters a SQL query that returns the dataset they wish to make available to users. For queries against the in-memory data cache, a LINQ query would be defined here. When connecting to an Oracle database, the user can enter the name of a table or stored procedure if they have selected Table or Stored Procedure in the preceding screen.
To include run-time values in the query to limit or alter the results of the query, the developer can add a parameter by prefacing a keyword with a colon e.g. :CATEGORYID_PARAMETER
If the Retain Master Field Properties checkbox is ticked, Nathean Analytics will retrieve meta data (datatype, field lengths, display formats etc.) for any of the fields in the query which have a matching Field Name in the Master Fields.
When using temporary tables with MS SQL be aware that the code must be re-entrant.
The protocol should be
- SET FMTONLY OFF;
- Set NOCOUNT OFF so that you are not reporting partial counts back to the environment
- Into into a temp table with the # table name syntax eg SELECT * INTO #RESULTS
- when finished processing data in the temp table SET NOCOUNT OFF
- select the data from the temp table
- and finally clean up by dropping it
if you are connecting to a version of MS SQL Server older than 2016 then you should add this to the end of the query
SET FMTONLY ON;
SET FMTONLY OFF; SET NOCOUNT ON; SELECT GETDATE() AS MY_DATE INTO #DATETABLE UPDATE #DATETABLE SET MY_DATE = DATEADD(MM,1, MY_DATE) SET NOCOUNT OFF SELECT * FROM #DATETABLE DROP TABLE #DATETABLE SET NOCOUNT OFF;