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.

VIDEO TUTORIAL

Hints

When using temporary tables with MS SQL be aware that the code must be re-entrant.

The protocol should be

  1. SET FMTONLY OFF;
  2. Set NOCOUNT OFF so that you are not reporting partial counts back to the environment
  3. Into into a temp table with the # table name syntax eg SELECT * INTO #RESULTS
  4. when finished processing data in the temp table SET NOCOUNT OFF
  5. select the data from the temp table
  6. 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;

eg.:

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;
Revision: 7
Last modified: Dec 08, 2020

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment