Using SQL Views from Microsoft

When writing reports using any tool, one of the first things to decide is what tables to use. You may need multiple tables to create the report you need, and you may want data from different sets of tables, like WORK, OPEN and HIST. Also, many of the fields, like SOPTYPE, use integer values instead of words.

For example, you may want to create a report that includes WORK and HIST sales transactions. To do that, you would probably need a union query such as this:

SELECT SOPTYPE, SOPNUMBE, CUSTNMBR, DOCAMNT FROM SOP10100
UNION ALL
SELECT SOPTYPE, SOPNUMBE, CUSTNMBR, DOCAMNT FROM SOP30200

If you want to display words for SOPTYPE, you can use case statements such as this:

CASE SOPTYPE
WHEN 1 THEN ‘QUOTE’
WHEN 2 THEN ‘ORDER’
WHEN 3 THEN ‘INVOICE’
WHEN 4 THEN ‘RETURN’
ELSE ‘OTHER’
END AS SOPTYPEDESC

You could save commonly-used expressions and even create user-defined functions. But what if you could use SQL objects that already existed?

The good news is there are SQL objects—views—that ship from Microsoft.

There are a host of views available. In the example above, there is an SQL view called SalesTransactions, which combines the data from UPR10100 and UPR30200. The line items are in a separate view named SalesLineItems.

To get a list of the views, log into SQL Server Management Studio and expand the views folder.

If you want to see how the views are created, right-click on a view and select “Script View as,” >> “Create To” >> “New Query Editor Window.”

If you run a select * from SalesTransactions statement, notice the results.

Notice the SOP Type field, now with a space to make it more presentable in a report, shows familiar terms such as Order and Invoice.

There are many published views, but to make future reporting easier, you’ll want to create your own. Just remember to make good backups of the queries that create them.

Hopefully, the above information will be useful and allow you to create reports more easily than using tables.

Leave a Reply

Your email address will not be published. Required fields are marked *