Altova DatabaseSpy 2023 Enterprise Edition

When you run SELECT statements in the SQL Editor, the retrieved data is displayed in the Result window (or multiple Result windows, if multiple queries were executed). You can change the appearance of retrieved data (such as color, background, font weight) based on custom SQL conditions. For example, you can have certain values in the Result grid formatted with yellow background if they exceed some threshold.

The image below illustrates an example of conditional formatting in a SQLite database. This example project is available at the following path: C:\Users\<username>\Documents\Altova\DatabaseSpy2023\DatabaseSpyExamples\Tutorial\NanonullEnterprise.qprj. In this example, the goal is to apply conditional formatting as follows:

•Records where quantity exceeds 200 must have coral background and bold font

•Records where quantity is less than 10 must have turquoise background and italic font.

Conditional formatting in SQLite syntax

As illustrated above, in order to support conditional formatting, the SQL statement must include an alias column that must be conventionally named DbSpyRowSettings. This instructs DatabaseSpy to process this column as conditional formatting instructions. Note that the alias column consists of a "CASE" construct. The WHEN branch is the SQL expression where you supply the condition to check for, in the corresponding database grammar. The THEN branch is a string that specifies formatting styles such as font weight or background color. This string must be quoted, in the grammar specific to the database, so that it looks like a string to the database.

The styles you can use are as follows:

Remarks:

•You can use multiple styles separated by semi-colons, for example: background-color:gold; font: bold; color: blue;. Whitespace between styles is not relevant.

•If the styles you entered are not recognized, DatabaseSpy will ignore them without displaying any errors or warnings.

•The DbSpyRowSettings column may appear at any location in the column list.

•The case of the DbSpyRowSettings column or the case of styles is not relevant (except for font names, see above).

•When a conditionally formatted cell, column or row is in focus (selected), the background color has no effect. Only text color and font are applied.

•For better display results, you might want to clear the Show grid with alternating colors check box in the Result View options.

Conditional formatting in various database kinds

The following are some database-specific examples of SQL statements that use conditional formatting. Note that the examples below illustrate just some of the ways to apply conditional formatting. If supported by the database, it is also possible to achieve the same result with more complex techniques, such as sub-queries, stored procedures, and so on.