Searching with the SQL Query Designer

LIMS smart search feature contains one text box input which is used against multiple fields, resulting in irrelevant search results.  When more detailed search is required SQL Query Designer allows users to be as specific as they need to be to precisely pin point exact data needed.

  1. Expand "Sample Manager"
  2. Click on "Find Sample (Work Orders)"
  3. Click on "Sql Designer" button

2. SQL Designer Overview

  1. Selection Criteria drop down menus used to build the query
  2. Preview of query command
  3. Options to set-up various sorting
  4. Button to execute the query

3. Selection Criteria

Before building a new query always clear the command that is in the preview window

  1. When writing the first command this drop down field will be gray.  Subsequent commands will require a selection of "OR" or "AND".
  2. Select the object on which to perform the search, by clicking on the drop down
  3. Select the operator for the command by clicking on the drop down
  4. Type in search criteria

3.1. Example Command

In the below example user is searching for data on a specific bulk code "807749" and requires only generic, approved samples created within the last year and sorted by Sample ID.

3.1.1. Define Bulk Code - Command

  1. Click on "Select an Item" drop down
  2. Select "SAMPLE_TYPE" from the list.  (LIMS column name for the bulk code field is SAMPLE_TYPE)
  3. The equals operator is usually selected by default
  4. Type in bulk code which you would like to search for
  5. Once your query is set-up, click "Select"
  6. The query will be appended in the preview window below
  7. If this is the only command you want to run click the execute query button above.

If you execute this query as written above, result will be all the samples associated with bulk code 807749 to a maximum of 500 records.  If you require only generic Samples follow next step.

3.1.2. Select Only "Generic" Samples - Command

Generic samples in LIMS means that sample originally logged in by the lab or JDE.  This will exclude all the customer specific samples with market synonyms that are created by Print CoA or MKSyn jobs.  In other words any samples where "CUSTOMER_ID" field is blank

  1. This drop down is available because you are adding to an existing command.  Click drop down and select "AND" or "OR".  What you choose depend on how you want the search to be carried out.  Do you want both commands evaluated? Then choose "AND".  Do you want one or the other command to be evaluated? Then choose "OR"
  2. Choose "CUSTOMER_ID" field
  3. Since you cannot write a query such as "=     " (equals to nothing) there is an option to choose "IS NULL" which means the field you selected will be searched for where it's blank
  4. After query command is set up click "Select" this will append the next command in the preview window
  5. If this is the two commands you want to run click the execute query button or follow next step.

If you execute this query as written above all the samples associated with bulk code 807749 and those that have a blank field for CUSTOMER_ID (Generic samples) will be returned with a maximum of 500 records.  If you require only Approved Samples follow next step.

3.1.3. Select Only Approved Samples - Command

  1. Click to select "AND" if you'd like to run search against all the commands.
  2. Click to select "SAMPLE_STATUS"
  3. Click to Select "="
  4. Type in Status "A" to filter out all the records except for those in "A" status
  5. When command is ready click on "Select" button to append it into the preview window
  6. If these are all the command you want to execute press on execute query button or follow next step

If you execute this query as written above all the samples associated with bulk code 807749 and those that have a blank field for CUSTOMER_ID (Generic samples) and that have a Sample Status of "A" will be returned with a maximum of 500 records.  If you require only Samples created in the last year follow next step.

3.1.4. Select Only Records Manufactured in the Last Year - Command

  1. Click to select "AND" if you'd like to run search against all the commands.
  2. Click to select "MFG_DATE", this choice will change what the window looks like to accommodate date input
  3. Click to Select ">"
  4. Type in a date value that is exactly one year ago.
  5. When command is ready click on "Select" button to append it into the preview window
  6. If these are all the command you want to execute press on execute query button or follow next step to sort your results

If you execute this query as it's written above all the samples associated with bulk code 807749 and those that have a blank field for CUSTOMER_ID (Generic samples) and that have a Sample Status of "A" and that were manufactured in the last year (until September 17th 2018) will be returned with a maximum of 500 records.  If you require to sort your results appropriate follow the next step

3.1.5. Sorting Query Output

Default sorting will show when you open the SQL Designer.  To clear it "CTRL+click" to select each item and then click on double arrows to the left.

  1. Select if you'd like your query results to show Ascending or Descending
  2. Click on the field by which you'd like to sort.  SAMPLE_ID or MFG_DATE field is usually a good option.
  3. Click on the double right arrows to append the field by which to sort

You can select multiple fields by which to sort following steps 1-3 above.

When ready to execute the query click on the execute query button

4. General Info

The number of commands you can put into the query is vast.  Or you can make a simpler query with one or two command.  Anytime fields are not added correctly you might see a red warning that tells you what was done incorrectly.