The SQL Search function is a sophisticated search tool which uses SQL statements on any of the Aquabase tables to search for site that meet the conditions of the SQL statement. It works exactly like the Create View function, but does not create a View, but rather allows browsing through sites which are found conforming to the SQL search condition(s).
You can set up as many conditions as you want by creating complex “WHERE” conditions which would find exactly what you want to find.
The SQL Search requires some knowledge of SQL but Aquabase does most of the query setup for you and if you only select from the 4 list boxes at the top there is almost no typing to be done, with the exception of the actual WHERE condition that you want to restrict the data to, e.g. a water level deeper than 50m or an EC > 200 or any other condition…these values have to be typed in.
So:
1.) Select the Aquabase Table by double-clicking on it…this will insert the table under the “Search for site” box, which has the beginning of the SQL query just above it. A relationship between the selected table and the “allsites” view will then automatically be set up in the “where” box, followed by the “AND”, which you will now have to complete. All tables will be “aliased”, e.g. t3 for the 3rd table, to improve readability and shorten the query.
2.) For the completion of the query select the field from the “Field” list box (double-click), which you want to use for condition (e.g. WATER_LEV)
3.) Then select the Operator from the last list box
4.) Type in the value: If the value is numeric just type the value, e.g. 200. If the value is a string (characters) then use single or double quotes, e.g. ‘B’ or ‘GRANITE’. If the value is a date or time then also use quotes, e.g. ‘20120601’ or ‘1345’.
There can be as many conditions as you like, but you have to make sure that you select all tables for which you want to use conditions first to set up the correct relationships and then add the conditions to the end of the statement in the box.
Examples of the “WHERE” condition:
-basicinf table (t3) selected:
(f.SITE_ID_NR = t3.SITE_ID_NR) AND t3.SITE_TYPE = ‘B’
-geology_ table (t28) selected:
(f.SITE_ID_NR = t28.SITE_ID_NR) AND t28.LITH_CODE = ‘GRNT’
-waterlev table (t79) selected:
(f.SITE_ID_NR = t79.SITE_ID_NR) AND t79.WATER_LEV > 100
AND t79.DATE_MEAS >= ‘20100101’
The chemistry queries are a bit more complicated, but again, relationshpis will be set up automatically:
- chem_001 selected, this will also select chem_000 automatically
(f.SITE_ID_NR = t9.SITE_ID_NR) AND (t9.CHM_REF_NR =
t10.CHM_REF_NR) AND t10.EC > 200
One could add:
AND t9.DEPTH_SAMP > 50
and the query will return all sites where the EC was > 200 and the depth sampled was > 50.
Once the query is set up you can click the “Start Search” button on the top left, which, if the query is valid, will search the first site where all the conditions in the WHERE clause are met. Otherwise you will receive an error message with an indication where there is a fault in the query, or an information message that no sites were found for the condition.
The navigator can be used to scroll through all the records found. By clicking the “Toggle Hide” button to the left of the navigator will “minimise” the form to save screen space.