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\DatabaseSpy2024\DatabaseSpyExamples\Tutorial\. 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 SQL syntax that defines an alias column depends on the database type. See below for examples specific to various database kinds. For details, refer to the documentation of the database vendor.
The styles you can use are as follows:
Text can formatted as bold, italic or underline.
The font size can be expressed in fixed units (the same units as those in the Options dialog box) or as a percentage. A percentage value is relative to the font size defined in Options dialog box.
Unlike other styles, font names are case-sensitive. Font names may be quoted as long as the SQL statement remains valid.
Text color can be expressed as a hexadecimal value (case insensitive) or as a named color. For reference to all the named colors you can use, see https://www.w3.org/TR/2003/REC-SVG11-20030114/types.html, Section 4.2.
Background colors can have the same values as text colors.
•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.
Use the SWITCH function, see https://support.office.com/en-us/article/Switch-Function-D750C10D-0C8E-444C-9E63-F47504F9E379
Alternatively, use the IIF function, see https://support.office.com/en-us/article/IIf-Function-32436ECF-C629-48A3-9900-647539C764E3
IBM DB2 for i