Altova DatabaseSpy 2024 Enterprise Edition

Conditional Formatting

Home Prev Top Next

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\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.

ds_condformat_01

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:

 

Style

Remarks

Example

Text style

Text can formatted as bold, italic or underline.

font:bold

font:italic

font:underline

Font size

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.

font-size:10

font-size:150%

Font family

Unlike other styles, font names are case-sensitive. Font names may be quoted as long as the SQL statement remains valid.

font-family:Arial

Text color

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.

color:yellow

color:#FFFF00

Background color

Background colors can have the same values as text colors.

background-color:gold

background-color:#FFD700

 

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.

 

Database Kind

Example

Access

Use the SWITCH function, see https://support.office.com/en-us/article/Switch-Function-D750C10D-0C8E-444C-9E63-F47504F9E379

 

SELECT [id],
      [name],
      [quantity],
      SWITCH ([quantity] > 200, 'color:blue') AS [DbSpyRowSettings]
FROM   [products];

 

Alternatively, use the IIF function, see https://support.office.com/en-us/article/IIf-Function-32436ECF-C629-48A3-9900-647539C764E3

 

SELECT [id],
      [name],
      [quantity],
      IIF ([quantity] > 200, 'color:blue', '') AS [DbSpyRowSettings]
FROM   [products];

IBM DB2

IBM DB2 for i

Oracle

PostgreSQL

Progress OpenEdge

SQLite

Sybase

Teradata

SELECT "id",
      "name",
      "quantity",
      CASE WHEN "quantity" > 200 THEN 'color:blue;' END AS "DbSpyRowSettings"
FROM   "products";

IBM Informix

SELECT id,
      name,
      quantity,
      CASE WHEN quantity > 200 THEN 'color:blue;' END AS DbSpyRowSettings
FROM   products;

MariaDB

MySQL

SELECT `id`,
      `name`,
      `quantity`,
    CASE WHEN `quantity` > 200 THEN 'color:blue;' END AS `DbSpyRowSettings`
FROM   `products`;

SQL Server

SELECT [ProductID],
      [Name],
      [Quantity],
[DbSpyRowSettings] = CASE WHEN [Quantity] > 200 THEN 'color:blue;' END
FROM   [Products];

© 2017-2023 Altova GmbH