common table expressions Archives - IT Jungle

Guru: ACS 1.1.8.3 Content Assist Includes Prompt For SQL!

February 10, 2020 Birgitta Hauser

STRSQL, the green-screen facility for executing SQL Statements, has had its days. Nevertheless, many SQL users still prefer this legacy interface over the Access Client Solutions (ACS) Run SQL Scripts facility. Others use third-party tools to execute SQL statements. The common excuse has been that the Run SQL Script facility does not include a way to prompt SQL commands, tables, and columns. In ACS version 1.1.8.3, Content Assist includes prompting for SQL statements.

Before ACS Version 1.1.8.3, no prompting was available within the Run SQL Scripts Facility. To access tables, views or materialized query tables (MQTs), we needed to know …
Read more
Guru: Debugging Common Table Expressions

October 21, 2019 Ted Holt

I cannot say enough good things about common table expressions. Words like wonderful and marvelous don’t begin to describe them. However, CTEs do add a bit of complexity to an SQL query, and when the result set doesn’t contain the correct results, any common table expression can be the culprit. Fortunately, debugging queries with common table expressions is not difficult.

To illustrate what I mean, let’s assume we have a query that retrieves shipment information for one day. It involves a few tables:
- a one-row table containing a shipment date
- a table of shipment header information
- a customer master table
…
Read more