Understanding the SQL Expression of the Analytical View

It is useful to be able to quickly understand the SQL form of the analytical view from the user interface.

Consider the SQL form of the analytical views that we built in the earlier steps.

Please note that the SQL expression of the analytical view cannot be changed. Instead, you should delete the analytical view and create a new analytical view with an updated definition.

  1. Examine the SQL statement that represents the first analytical view, geo.geo_features_lv_av_1, by clicking Show SQL on its line.

    Viewing the SQL definition of the analytical view
    Viewing the SQL Definition of the Analytical View
  2. Similarly, look at the SQL statement that represents the second analytical view, geo.national_geo_features_av_1, by clicking Show SQL.

    Viewing the SQL definition of the analytical view
    Viewing the SQL Definition of the Analytical View
  3. Consider and compare the two statements:

    geo.geo_features_lv_av_1 is an analytical view created over a logical view

    SELECT TA_0.feature_class Feature_Class, TA_0.state_alpha State_Alpha, sum_no_finalize(1) Record_Count FROM geo.geo_features_lv TA_0 GROUP BY 1, 2

    geo.national_geo_features_av_1 is an analytical view created over a base table

    SELECT TA_0.feature_class Feature_Class, TA_0.county_name County_Name, sum_no_finalize(1) Record_Count, TA_0.state_alpha State_Alpha FROM geo.national_geo_features TA_0 GROUP BY 1, 2, 4
    • TA_0 is the automatically generated alias for the base table or logical view. It is not the same entity in these two statements.
    • sum_no_finalize(1) is the function that returns record count.