Getting Started With Advanced Query

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')

10 views0 comments

Recent Posts

See All