Home : External Source - Edit/View Variables
Q11298 - INFO: External Source - Edit/View Variables

Edit/View Variables (Interface Tab) - External Source is used to setup a query to pull data from other databases using Import From External Data Source

The external data source import utility allows you to import data from any OLEDB compliant database (Access, MS SQL, Oracle...).  In variable edit, you can setup an SQL Query to pull data from a database and place the result in the WIMS Variable.  After you have setup the data source connection and the SQL Query, use the Import, from External Data Source in Utilities (see External Data Source Import) to get the data.

FIELDS:

External Source: The name given to the External Sources connection that is setup in External Data Source Setup.

SQL Query: The SQL statement that returns a value that will be stored in the WIMS database.  The import utility will import the value in the first column/first row of the result set.  To use dates in the query, WIMS provides two constants that represent the Start and End Date/Time of the Variable Slot for which you are retrieving data.  See setting dates of queries below.

Data Approval: The data approval level that the data imported is set to. 

Using Dates in query:

WIMS provides two constants that represent the Start and End Date/Time of the Variable Slot for which you are retrieving data.  #SD# is located using the  button and represents the Start Date Time of the Data Slot for the variable.   #ED# is located using the  button and represents the End Date Time of the Data Slot for the variable.  You can also add or subtract from the times using the syntax #SD+-nI# where n represents the number of I (intervals) to add to the Start Date or End Date. 

For a Daily Variable getting data for Feb 18, 2016:

#SD# 2/18/2016 00:00:00
#ED# 2/18/2016 23:59:59
#SD-7D# 2/11/2016 00:00:00 (subtract 7 days)
#SD+8H# 2/18/2016 08:00:00
#ED+8H# 2/19/2016 07:59:59 
#SDBOM# 2/1/2016 00:00:00 (BOM - beginning of month)

For an Hourly Variable getting data for Feb 18, 2016 8AM:

#SD# 2/18/2016 08:00:00
#ED# 2/18/2016 08:59:59
#SD-1H# 2/18/2016 07:00:00 (subtract 1 hour)
#SD+15N# 2/18/2016 08:15:00 (N sets the interval to minutes)
#ED+1S# 2/18/2016 08:00:00 
#SDBOM# 2/1/2016 00:00:00 (BOM - beginning of month)

Supported Intervals:

Interval Description
Y Day of Year
Q Quarter
M Month
D Day
W Weekday
H Hour
N Minute
S Second


SEE ALSO:  Setup and Use the Import from External Data Sources Feature to Import Data From a Microsoft SQL Server database


EXAMPLES:

1. Get the number of labor hours for a day from Job Plus:

Select SUM(Hours) from WOLABOR where Date >= #SD# and Date <= #ED#

When data is imported for October 15, 2006 the query becomes:

Select SUM(Hours) from WOLABOR where Date >= #10/15/2006 00:00:00# and Date <= #10/15/2006 23:59:59#

 

2. Get the number of samples received from Lab Cal

 

SELECT COUNT(*) FROM LC_SAMPLE WHERE LC_SAMPLE.SAMPLEDTTIME >= '2006-12-31 00:00:00' and LC_SAMPLE.SAMPLEDTTIME <= '2006-12-31 23:59:59'

Note: The query used the MSSQL date format (i.e. yyyy-mm-dd hh:mm:ss) because the WIMS External Source Setup specifies that the date format as MSSQL.

HINT:  Use the Test button to run the query for the current day and see results.

3. Get the Standard Deviation from an Hourly WIMS variable from 8AM to 8AM of the next day

SELECT STDEV(CURVALUE) FROM DATADDH WHERE DATESTAMP >=#SD+8H# AND DATESTAMP <= #ED+8H#  AND VARID = 93

 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 6/3/2008 4:31 PM.
Last Modified on 2/18/2016 11:09 AM.
Last Modified by Scott Dorner.
Article has been viewed 3773 times.
Rated 4 out of 10 based on 3 votes.
Print Article
Email Article