Getting Started with Advanced Query in VDM

This guide explains how to write and run a basic SQL query in VDM’s Advanced Query editor. You will select columns from one table, filter the results, join a second table, sort the output, and save the View.

Advanced Query lets you enter SQL directly instead of building the query through the visual field, filter, and linking tools. A single query can join several tables and return one result set. Multi Query is a separate workflow for running multiple statements and returning separate result sets.

Before you begin

• Have a working VDM Connection Profile and permission to read the tables you need.
• Know the actual table names, column names, and the fields that relate your tables. Use the Tables and Fields panel to help identify them.
• Start with a new View, or save a copy of an existing View before changing its query.

About the examples: This guide uses fictional Orders and Customers tables with SQL Server syntax. The dbo prefix is a SQL Server schema name. These tables are not automatically installed with VDM. Replace the table names, columns, schema, and values with those in your database. Other database types may require different syntax.

Example data

Each order contains a CustomerID. We will use that field to find the customer’s name.

dbo.Orders

dbo.Customers

Customer 99 is intentionally missing from the Customers table so you can see how INNER JOIN and LEFT JOIN handle an unmatched order.

1. Connect to your database and enable Advanced Query

1. Open VDM and create a new View.
2. Open the database connection you want to query. Confirm that it is active.
3. Select Advanced Query under Query Builder to open the SQL editor.
4. Select Use Adv Query at the upper-right of the SQL editor to enable Advanced Query for this View.
5. Confirm that Adv Query appears at the top of the VDM window, above the ribbon. This indicates that the View is using Advanced Query.

Important: Opening the Advanced Query editor and entering SQL does not by itself enable Advanced Query. Select Use Adv Query before running the View so VDM executes your SQL instead of the standard query builder’s statement.

Enter one SQL statement at a time. As you work through the examples below, replace the previous statement with the next version. You do not need a Multi Query splitter or result-set name for this single-query workflow.

2. Select columns from one table

Enter a query that selects the columns you need:

SELECT OrderID, CustomerID, OrderStatus, OrderTotal
FROM dbo.Orders

SELECT lists the columns to return. FROM identifies the table to read. Commas separate the column names; there is no comma after the final column.

Select Run View and review the returned data in the Details grid. With the example data, this query returns all five orders. Listing columns explicitly makes it easier to understand and maintain the View than using SELECT *.

3. Filter the rows using WHERE

To return only open orders, add a WHERE clause:

SELECT OrderID, CustomerID, OrderStatus, OrderTotal
FROM dbo.Orders
WHERE OrderStatus = 'Open'

The example now returns four orders. Text values such as 'Open' use single quotes. Numeric values such as 100 are entered without quotes.

To return open orders with a total of at least 100, add a second condition:

SELECT OrderID, CustomerID, OrderStatus, OrderTotal
FROM dbo.Orders
WHERE OrderStatus = 'Open'
  AND OrderTotal >= 100

AND requires both conditions to be true. With the example data, orders 1001, 1004, and 1005 qualify.

Use IS NULL instead of = NULL. If combining AND and OR, use parentheses to make the intended grouping clear.

4. Join a second table using INNER JOIN

Replace the statement with this version to add the customer’s name:

SELECT
    o.OrderID,
    c.CustomerName,
    o.OrderStatus,
    o.OrderTotal
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON o.CustomerID = c.CustomerID
WHERE o.OrderStatus = 'Open'
  AND o.OrderTotal >= 100

o and c are table aliases: short names used to identify which table a column belongs to. For example, o.OrderID comes from Orders, and c.CustomerName comes from Customers.

ON o.CustomerID = c.CustomerID defines the relationship. INNER JOIN keeps only rows that match in both tables. The filter still requires an open order totaling at least 100.

With the example data, orders 1001 and 1005 are returned. Order 1004 is excluded because Customer 99 has no matching customer record.

5. Sort the results using ORDER BY

Add ORDER BY at the end of the query:

SELECT
    o.OrderID,
    c.CustomerName,
    o.OrderStatus,
    o.OrderTotal
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON o.CustomerID = c.CustomerID
WHERE o.OrderStatus = 'Open'
  AND o.OrderTotal >= 100
ORDER BY o.OrderTotal DESC, o.OrderID ASC

DESC sorts from highest to lowest; ASC sorts from lowest to highest. This example sorts by order total, then by order ID for equal totals.

The complete query returns:

6. Use LEFT JOIN when you need unmatched orders

If you want to keep every qualifying order even when its customer record is missing, replace INNER JOIN with LEFT JOIN:

SELECT
    o.OrderID,
    c.CustomerName,
    o.OrderStatus,
    o.OrderTotal
FROM dbo.Orders AS o
LEFT JOIN dbo.Customers AS c
    ON o.CustomerID = c.CustomerID
WHERE o.OrderStatus = 'Open'
  AND o.OrderTotal >= 100
ORDER BY o.OrderTotal DESC, o.OrderID ASC

This version returns orders 1005, 1004, and 1001. For order 1004, CustomerName is NULL, which may display as a blank in the grid.

The Orders table is on the left because it appears in FROM. A LEFT JOIN keeps its rows, subject to the WHERE conditions. Adding a WHERE condition on a Customers column can exclude unmatched rows; review that behavior when adding filters.

7. Verify and save your View

1. Confirm that Adv Query appears at the top of VDM, then select Run View after each change.
2. Check the returned columns, filter values, row count, and customer names against known source records.
3. Confirm that the join uses the correct relationship. Multiple matching rows in the joined table can produce multiple rows for one order.
4. Select Save or Save As and give the View a descriptive name, such as Open Orders with Customer Names.

For Advanced Query, place the selection, join, and filtering logic directly in the SQL statement shown here.

Troubleshooting

Related articles

• How to Open a Database Connection in VDM
• How to Use the Tables and Fields Panel in VDM
• Understanding SQL Joins in VDM
• How to Use Multi Query in Advanced Query
• InterSystems Caché Queries Fail When Using a Trailing Semicolon

For SQL Server syntax reference, see Microsoft’s SELECT, WHERE, and JOIN documentation.