Use a View for Friendly Names and Shorter Queries

A view is a saved query that programs can read like a table. It helps when the imported column names are awkward, when people need only a few of the columns, or when the same long query would otherwise be typed into every report. A view doesn't change the APPX file or the imported table, so it is also a safe way to rename columns without editing ASQL's script.

Create the view once, for example in WinSQL:

CREATE VIEW country_list (country_name, region_name, words_in_address, postcode_position)
AS SELECT name, region_label, no_words, post_code_loc FROM DMO_DMO_COUNTRY

Then read it like a table:

SELECT * FROM country_list ORDER BY country_name
SELECT country_name FROM country_list WHERE region_name = 'County' ORDER BY country_name

The second query returns France, Ireland and UK.

A few things to know:

• The CREATE VIEW statement has to fit within the statement limits in Handle Wide Files and Long SQL Scripts. After that, every query against the view stays short.
• The view keeps the names of the table and columns it reads. If the table is dropped, the view stays, but queries on it fail with -20005 Table/View/Synonym not found. Once the table is imported again with the same column names, the view works again on its own. If a new import changes a column name, drop the view and create it again.
• The view's own column names follow the SQL name rules, so a reserved word needs double quotes there too.
• To remove the view, run DROP VIEW country_list. The table and the APPX file are not affected.