Vista Views and Query Parts

SQL In One Picture (Vista Edition)

In SQL, you read data from tables and views.

For reporting and most data queries, you should start from views like JCJM (Jobs) and JCCD (Job Cost Detail) because they’re shaped for application/reporting usage and can enforce view-level security.

Use tables when you’re writing database-side logic (like triggers) or when you intentionally need to bypass possible view-level data security/shaping.

Two big reasons views are common for reporting:

• Users typically don’t have permissions to select from base tables unless they’re granted db_owner (or higher).
• Data security can be applied to Views (not Tables).

Data security examples: restrict records by Company so users only see their assigned companies, or secure Employee records so only certain individuals can view them.

In the database, Tables are often prefixed with b or v. In most cases, you can get the View name by dropping the first character:

• bJCJM → JCJM
• bJCCD -> JCCD

There are exceptions (example: vSMWorkCompleted).

(Why b* And v* Exist)

info

• b-prefixed tables come from when Vista ERP was Bidtek.
• v-prefixed tables originated when the software was officially Viewpoint/Vista.

The Parts Of A SQL Query (And What They Do)

A basic reporting query is built from a few core clauses. Think of them as “lego blocks” you combine depending on the question.

• SELECT: which columns or calculations you want returned (the output).
• FROM: the base table/view you’re reading from (the starting dataset).
• JOIN: bring in related tables/views (connect datasets using keys).
• WHERE: filter rows before grouping/aggregation (company, status, posting month, etc.).
• GROUP BY: summarize many rows into fewer rows (totals by job, totals by month, etc.).
• ORDER BY: sort the final results for readability (top jobs by cost, newest month first, etc.).

Important: there are two different “orders” to understand:

• SQL clause order (how it must be written): FROM → JOIN → WHERE → GROUP BY → ORDER BY
• Build order (how it’s often easiest to draft while exploring): start with FROM, add a simple SELECT, add quick WHERE filters to narrow the rows, then add JOINs (written between FROM and WHERE), and finally add GROUP BY / ORDER BY as needed.

Template you can reuse:

SELECT
  -- columns and aggregates
FROM <base_view> AS b
JOIN <other_view> AS o
  ON ...
WHERE
  -- filters
GROUP BY
  ...
ORDER BY
  -- sorting
;