dbMap/Web - SQL Reference
SQL, or Structured Query Language, is the basis of scripts which are used to extract information from databases. In dbMapWeb, SQL is used to define data selection and reporting. It is also used in the creation of data loaders.
The typical end user does not need to understand SQL. A wide range of default queries are shipped and maintained by Petrosys. Site specific queries are usually tailored to meet the business requirements of a company when Petrosys is installed. Petrosys recommends that larger sites appoint a database administrator who can become familiar with SQL to the point of being able to extend the site's query resource.
The SQL queries used by dbMapWeb for reporting and selection are all maintained in the 'primary database', so that they are readily accessible.
Extensions and Variants of SQL
SQL is implemented by the database engine, which for Petrosys is usually Oracle. Other database engines include Microsoft SQL Server, MySQL and Postgres. There is an official 'ANSI standard' for the SQL language, and queries written in this should ideally work in all engines. However, to exploit the competitive advantages of the various database engines and to support legacy implementations, the actual SQL used in applications for Oracle and SQL Server, for example, is frequently incompatible.
In addition to 'syntax incompatibility' many vendors introduce extensions to the SQL language. Extensions that you are likely to encounter in dbMapWeb are:
Petrosys defined functions, such as ps_well_path_tvd_m(), to do things such as compute downhole locations. These are described in SQL QUERIES - PETROSYS FUNCTIONS
Petrosys specific extensions that allow runtime prompting for parameters such as names, dates, or numbers to be used in a query, such as '~Seismic survey year~', and described in SQL QUERIES - RUNTIME PARAMETERS
ESRI ArcSDE extensions to allow spatial querying of ArcSDE data stores
Oracle spatial extensions, such as SDO_RELATE(), allowing spatial querying of Oracle Spatial data stores.
SQL Query Name and Owner
A stored SQL query is referenced by a name and an owner. No two queries can have the same name and owner, but it is possible to have the same name under a different owner.
The name can be up to 50 characters in length and can include alphabetic or numeric characters, as well as symbols such as _ or -. Characters such as % , * & ! # should not be included in the name as they often have alternate meanings.
There are two possible types of 'owners':
Public |
Everybody will be allowed to use the SQL script. |
UserName |
SQL scripts can also be stored under the name of the database user. These scripts will not be available to other users and will not appear in the list of scripts when another user has logged in. |
Queries with Public ownership have restricted update access, however if you highlight the query and then click on the New button the defaults will match the Public query. You can then modify the query and save it to a personal query.
Creating, Editing and Viewing SQL Queries
SQL queries are built using Petrosys Pro.
Role of OBJECT_UID in Petrosys Queries
When a query is run in Petrosys, Petrosys needs to know which of the items returned by the query is the 'key' with which it can link the output of that query to other objects that Petrosys works with, such as rows in a selection list or symbols on a map.
The standard way of doing this is to allocate the alias OBJECT_UID to that key value, for example in a well query:
SELECT A.UWI OBJECT_UID, A.* FROM WELL
OBJECT_UID is used to link the query to the data to be selected for a report or map in which a data selection file or area of interest is in use. Reports that are to run independently of external data selections should not include the OBJECT_UID.
Here is a more complex example of a query that extracts well header data and downhole formation picks:
SELECT A.UWI OBJECT_UID,
B.TOP_DEPTH PICK_MD,
A.UWI THE_UWI,
A.WELL_NAME||A.WELL_NUMBER WELL_NAME_NO,
C.FORM_NAME PICK_NAME,
PS_WELL_PATH_LAT(:OBJECT_UID, :PICK_MD) POSTING_LAT,
PS_WELL_PATH_LON(:OBJECT_UID, :PICK_MD) POSTING_LON,
PS_WELL_PATH_TVD_FT(:OBJECT_UID, :PICK_MD) "TVD(ft)",
PS_WELL_PATH_TVD_M(:OBJECT_UID, :PICK_MD) "TVD(m)",
func_get_well_colour(a.well_name) PS_SYMBOL_COLOUR
FROM WELL A,
WELL_FORMATION B,
FORMATION C
WHERE A.UWI=B.UWI
AND B.FORM_ID=C.FORM_ID
Legacy Object Identification – UID and UIDLIST
Early implementations of dbMapWeb required queries to include keywords such as %UID% and %UIDLIST% to link applications objects to query elements. This construct is still supported but it is no longer necessary.