In this post I want to discuss the advanced query inside VDM.
What Is Advanced Query?
Advanced query allows you to create your View using your own SQL statements. Advanced query gives you complete control over your query which in some cases can be much more powerful for getting the data you need.
If you run reports outside of VDM and want to run them with VDM, just copy the query and paste it into Advanced Query and click run!
Note: Advanced Query is a powerful tool but will require a general/advanced understanding of SQL to fully utilize.
Steps To Use Advanced Query
1. Open VDM
2. Click on the Advanced Query tab on the left side of VDM.
3. Begin constructing your query / paste your query and run the View.
IMPORTANT NOTE: Make sure VDM displays Advanced Query is Enabled.
Below are example SQL queries used for creating a View with Advanced Query.
1. Basic SELECT statement
SELECT Customers.City, Customers.Address, Customers.ContactName, Customers.CustomerID
FROM dbo.Customers Customers
2. SELECT statement with JOIN and WHERE.
SELECT Customers.ContactName, Customers.Address, Customers.City, Orders.OrderDate, Orders.ShippedDate, Orders.OrderID, Orders.EmployeeID, Orders.Freight, OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount, Employees.HomePhone, '@EXDateDiff'
FROM dbo.Customers Customers
INNER JOIN dbo.Orders Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN dbo.OrderDetails OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN dbo.Employees Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE (Orders.EmployeeID IN (1,4,7) And Orders.OrderDate >= '1997-10-01' And Orders.OrderDate <= '1997-10-31')