LVP SQL
LogViewPlus has a custom SQL engine which executes against in memory log entries. This is important because it means LogViewPlus does not write your log entries to another data store. We call our custom SQL implementation LogViewPlus SQL or LVP SQL.
SQL statements in LogViewPlus are based on Transact-SQL, or T-SQL, which is Microsoft's version of SQL used extensively in SQL Server. Our SQL engine is tested against a SQL Server back-end to ensure SQL statement compatibility and accuracy of results. In fact, if you're writing a SQL statement and unsure of the syntax, it can be extremely helpful to search the web for "how to X in SQL Server", the resulting answer should also be supported in LogViewPlus. If you find an example where this is not the case, please do let us know.
LogViewPlus only supports SQL SELECT statements. Other SQL statements such as INSERT or CREATE TABLE are not supported.
LogViewPlus currently only supports one table called CurrentView which can be aliased with the abbreviation CV. Therefore, the FROM target of your SELECT statement should always be CurrentView or CV as there is only one view. JOIN statements are not supported.
The CurrentView data matches the data which is currently visible in the Log Entry Grid. The data returned by your SQL statement will change automatically as as the data in the current view changes - such as when you navigate between filters or log files. The data columns available to your SQL query will also change based on the current selection. For example, when using a message parser. In some scenarios, this may cause an existing SQL statement to move between states of being valid and invalid. This behaviour is normal and expected.
Changes to the CurrentView may also impact any data displayed in a Dashboard.
The following keywords and functions are supported. This list is a nearly complete mirror of the functionality available in T-SQL SELECT statements with the notable exception of JOINs which are not supported as there is only one data table (see above).
Keywords
|
Keyword
|
Description
|
|
The SELECT command is used to select data from a database. The data returned is stored in a result table, called the result set.
| |
|
The WHERE command filters a result set to include only records that fulfill a specified condition.
| |
|
The AND command is used with WHERE to only include rows where both conditions is true.
| |
|
The NOT command is used with WHERE to only include rows where a condition is not true.
| |
|
The GROUP BY command is used to group the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG).
| |
|
The ORDER BY command is used to sort the result set in ascending or descending order.
| |
|
BY
|
See ORDER or GROUP.
|
|
The HAVING command is used instead of WHERE with aggregate functions.
| |
|
The AS command is used to rename a column or table with an alias.
| |
|
The SELECT DISTINCT command returns only distinct (different) values in the result set.
| |
|
The LIMIT, SELECT TOP or ROWNUM command is used to specify the number of records to return.
| |
|
Selects the first X percent of the records matched by the query.
| |
|
The FROM command is used to specify which table to select or delete data from.
| |
|
IS
|
See IS NULL or IS NOT NULL.
|
|
A field with a NULL value is a field with no value. This is distinct from a field with an empty or default value.
| |
|
GO
|
Used to indicate end of statement. Currently allowed but ignored.
|
|
The CASE command is used is to create different output based on conditions.
| |
|
WHEN
|
See CASE.
|
|
THEN
|
See CASE.
|
|
ELSE
|
See CASE.
|
|
END
|
See CASE.
|
|
The ASC command is used to sort the data returned in ascending order.
| |
|
The DESC command is used to sort the data returned in descending order.
|
String Functions
|
Function
|
Description
|
|
The CHAR() function returns the character based on the ASCII code.
| |
|
The CHARINDEX() function searches for a substring in a string, and returns the position.
| |
|
The CONCAT() function adds two or more strings together.
| |
|
The LEFT() function extracts a number of characters from a string (starting from left).
| |
|
The LEN() function returns the length of a string.
| |
|
The LOWER() function converts a string to lower-case.
| |
|
The LTRIM() function removes leading spaces from a string.
| |
|
The NCHAR() function returns the Unicode character based on the number code.
| |
|
The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.
| |
|
The RIGHT() function extracts a number of characters from a string (starting from right).
| |
|
The RTRIM() function removes trailing spaces from a string.
| |
|
The STR() function returns a number as a string.
| |
|
The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.
| |
|
The SUBSTRING() function extracts some characters from a string.
| |
|
The UPPER() function converts a string to upper-case.
|
Date Functions
|
Function
|
Description
|
|
The DATEPART() function returns a specified part of a date.
| |
|
The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.
| |
|
The DAY() function returns the day of the month (from 1 to 31) for a specified date.
| |
|
The MONTH() function returns the month part for a specified date (a number from 1 to 12).
| |
|
The YEAR() function returns the year part for a specified date.
|
Math Functions
|
Function
|
Description
|
|
The ABS() function returns the absolute value of a number.
| |
|
The CEILING() function returns the smallest integer value that is larger than or equal to a number.
| |
|
The FLOOR() function returns the largest integer value that is smaller than or equal to a number.
| |
|
The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).
| |
|
The ROUND() function rounds a number to a specified number of decimal places.
|
Conversion Functions
|
Function
|
Description
|
|
The CAST() function converts a value (of any type) into a specified datatype.
| |
|
The COALESCE() function returns the first non-null value in a list.
| |
|
The CONVERT() function converts a value (of any type) into a specified datatype.
| |
|
The ISNULL() function returns a specified value if the expression is NULL.
| |
|
The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression.
|
Aggregation Functions
|
Function
|
Description
|
|
The AVG() function returns the average value of an expression.
| |
|
The COUNT() function returns the number of records returned by a select query.
| |
|
The MAX() function returns the maximum value in a set of values.
| |
|
The MIN() function returns the minimum value in a set of values.
| |
|
The SUM() function calculates the sum of a set of values.
|