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

On this edition the text comes back damaged and the second query returns no rows, because of the Unicode text problem described in Verify COUNTRY Records and Field Values. The view itself is fine: the same view on the 5.5.x editions 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.