StarLIMS is a web based LIMS that hosts data in Microsoft SQL Server or Oracle. The Generic LIMS Interface can be used to import data from the StarLIMS database. 

StarLIMS sample results are stored primarily in 2 tables:  ORDERS (Samples) and RESULTS.  The SAMPLE_PROGRAMS Table may be required to identify the Sample Location (i.e. the SAMPLOC field that is cross referenced to LIMS_LOC). The following examples use table structures that are from StarLIMS version 10.5, however we believe earlier versions of StarLIMS use a very similar structure:

EXAMPLES:

Example Queries for StarLIMS - Set in Source Configuration, Advanced Settings Source Query field:

NOTE: #SD# and #ED# will be replaced with the start date and end date of import date range when the query is executed.  They will be formatted properly for the source (StarLIMS) database type. 

Example 1: Basic - Get only approved Results where the Result Status (field S) is 'Done':

Select ORDERS.ORDNO AS SAMPNUM, ORDERS.SAMPDATE, ORDERS.SAMPLEDBY, ORDERS.SP_CODE, ORDERS.SAMPDESC, SAMPLE_PROGRAMS.PROGNAME AS SAMPLOC, RESULTS.Analyte, RESULTS.FINAL, RESULTS.UNITS, RESULTS.DATEENTER AS AUDITDATETIME, RESULTS.DATEENTER AS APPROVEDDATE, RESULTS.S  FROM ORDERS, RESULTS, SAMPLE_PROGRAMS
WHERE ORDERS.ORDNO = RESULTS.ORDNO And ORDERS.SP_CODE = SAMPLE_PROGRAMS.SP_CODE
AND DATEENTER>= #SD# AND DATEENTER < #ED#  AND RESULTS.S='Done' 
ORDER BY SAMPDATE,DATEENTER

Example 2: Mapping of Status to Data Approval Levels (Use Results.RN2 as Lab for Additional Info Import):

Select RESULTS.RN2 AS LAB,ORDERS.ORDNO AS SAMPNUM, ORDERS.SAMPDATE, ORDERS.SAMPLEDBY, ORDERS.SP_CODE, ORDERS.SAMPDESC, SAMPLE_PROGRAMS.PROGNAME AS SAMPLOC, RESULTS.Analyte, RESULTS.FINAL, RESULTS.UNITS, RESULTS.DATEENTER AS AUDITDATETIME, RESULTS.DATEENTER AS APPROVEDDATE, RESULTS.S, RESULTSTATUS=Case WHEN RESULTS.S = 'Done' THEN 0 WHEN RESULTS.S='Pending' THEN  -512 ELSE -1024 END,  ORDERS.STATUS 
FROM ORDERS, RESULTS, SAMPLE_PROGRAMS
WHERE ORDERS.ORDNO = RESULTS.ORDNO And ORDERS.SP_CODE = SAMPLE_PROGRAMS.SP_CODE AND DATEENTER>= #SD# AND DATEENTER < #ED# 
ORDER BY SAMPDATE,DATEENTER

Example 3: Filtering records by FOLDERS.DEPT (can speed up interface as it only looks at records we may import)"

Select RESULTS.RN2 AS LAB,'' as ANALYZEDBY,'' as ANALYZESTART, '' as SAMPLENOTE,  '' as RESULTCOMMENT,ORDERS.ORDNO AS SAMPNUM, FOLDERS.DEPT, ORDERS.SAMPDATE, ORDERS.SAMPLEDBY, ORDERS.SP_CODE, SAMPDESC, PROGNAME AS SAMPLOC, RESULTS.Analyte, RESULTS.testno, RESULTS.FINAL, RESULTS.UNITS, RESULTS.DATEENTER AS AUDITDATETIME, RESULTS.DATEENTER AS APPROVEDDATE, RESULTS.S, RESULTSTATUS=Case WHEN RESULTS.S = 'Done' THEN 0 WHEN RESULTS.S='Pending' THEN  -512 ELSE -1024 END,  ORDERS.STATUS 
FROM ORDERS, RESULTS, SAMPLE_PROGRAMS, FOLDERS  WHERE ORDERS.ORDNO = RESULTS.ORDNO And ORDERS.SP_CODE = SAMPLE_PROGRAMS.SP_CODE AND ORDERS.FOLDERNO=FOLDERS.FOLDERNO AND DATEENTER>= #SD# AND DATEENTER < #ED# 
AND FOLDERS.DEPT='Institute' 
ORDER BY SAMPDATE,DATEENTER