Skip to main content

Show SQL Statement

The Show SQL Statement feature lets you view the SQL code generated by your data model in the Data Model Designer. This code shows all selected fields, tables, and joins, and can help you understand or troubleshoot how the model retrieves data.

important

You must have at least one column in the Fields Table for the SQL statement to be generated.

View the SQL statement​

  1. In the Data Model Designer, click SQL Statement on the toolbar.
  2. Review the SQL code generated for your data model. Use this to understand table joins, selected fields, and filter logic.
  3. (Optional) Copy the SQL statement if you need to run or modify it in a database tool.
  4. Click Ok when finished.

Important behavior of the WHERE clause​

When you open the SQL Statement, you may see that it ends with:

WHERE 1 = 2

This placeholder filter is inserted intentionally to prevent the SQL statement from running unfiltered by default. Executing the generated SQL without adequate filters can severely impact your source system.

The generated SQL includes all table joins, even if some require filters normally provided by the end-user interface (for example, a Selection Page or Generic Filter). Without these filters, a query may generate massive datasets and potentially overload the database server.

Example of potential risk​

Consider a fact table containing 100,000 invoice lines joined to a currency rate table with 15,000 rows (rates for each day over 5 years):

  • The join is based on the currency, but not on the date.
  • The date filter is intentionally left for the user to select when running the report.
  • If run without a date filter, the query multiplies all rows:
    5 years × 365 days × 100,000 invoices = 182,500,000 rows
  • If the fact table has 5 million invoices, the result count becomes significantly higher.

By adding WHERE 1 = 2, no records are returned until you replace it with a safe filter. Always ensure an appropriate condition is added before executing.

Limit record counts​

To control output size when testing, you can use the TOP keyword in the SELECT statement.

SELECT TOP 1000 ...