Creating and Using Command Tables in VDM

Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.

Article Goal

Explain what Command Tables are, how they work in VDM Views, and walk through three supported methods for creating them—highlighting best practices.

What Are Command Tables?

Command Tables in VDM act as subqueries placed within the FROM clause of a View. They’re used to:

• Simplify complex source structures

• Encapsulate reusable query logic

• Enable aggregate calculations (like totals or counts) to appear alongside detailed data

Tip: Command Tables let you work with summarized and detailed results in the same View—without having to write complex joins in the main query.

Why Use Command Tables?

• Clean up the source structure for reporting

• Support calculated or pre-aggregated results

• Improve performance by offloading logic to subqueries

• Allow non-SQL users to access predefined logic without rewriting it

Steps to Add a New Command Table

Step 1: Access the Command Tables Section

• Right-click on @CommandTables in the VDM tree

• Click Add

Step 2: Name Your Command Table

Enter a name for the new Command Table.

Important Note: Follow standard SQL naming conventions—avoid spaces or special characters.

Methods for Creating Command Tables

There are three supported methods. Below is a breakdown of each one, including pros and cons.

Method 1: Add Method (Not Recommended)

This method involves adding each field manually.

Steps:

1. Paste your SQL query into the Command Table text area

2. Click Add to add fields (repeat for each one)

3. Set the Data Type for each field

4. Optionally enter a Description

5. Set the Field Name

Note: This method is more error-prone and time-consuming. Use one of the import options instead.

Method 2: Import Method

This method uses fields from an existing View.

Steps:

1. Open the View that contains your desired fields

2. Create a new Command Table

3. Click Import

4. Verify Data Types

5. Optionally enter a Description

6. Verify Field Name and Table Name

Tip: This method is best when you're reusing logic from existing Views and want a quick setup.

Method 3: RS Import Method (Recommended)

This is the most streamlined and reliable method.

Steps:

1. Create a new Command Table

2. Paste your SQL Query into the Command Table text area

3. Click RS Import

4. Verify Data Types

5. Optionally add Descriptions

6. Confirm Table and Field Names

Best Practice: Use RS Import to speed up development and reduce manual entry errors.

Article Summary

Command Tables in VDM allow you to embed subqueries directly into your Views, giving you more control over complex data logic, summaries, and aggregation. They help you build cleaner, more maintainable Views and support various creation methods—RS Import being the most efficient and recommended approach.

Use Case: You want to calculate the total sales per customer and join it to a detail-level orders table—all within the same View. Use a Command Table to pre-aggregate the sales, then link it using VDM’s standard join interface.