This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:statements:qualify:home [2010/07/04 16:24] tdo |
dev:sql:statements:qualify:home [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 23: | Line 23: | ||
| ===== Example ===== | ===== Example ===== | ||
| The first two qualify statements are completely separate and are included to demonstrate the individual counts that qualify for the separate, given criteria. This qualify statement shows that there are 25 customers in the cities of Denver, Chicago, and Dallas. | The first two qualify statements are completely separate and are included to demonstrate the individual counts that qualify for the separate, given criteria. This qualify statement shows that there are 25 customers in the cities of Denver, Chicago, and Dallas. | ||
| - | >qualify customers where city in ('denver', 'chicago', 'dallas') | + | |
| - | 25 CUSTOMERS records qualify | + | >qualify customers where city in ('denver', 'chicago', 'dallas') |
| + | 25 CUSTOMERS records qualify | ||
| This next qualify statement shows that there are a total of 272 customers in the states of California, Colorado, and Texas. | This next qualify statement shows that there are a total of 272 customers in the states of California, Colorado, and Texas. | ||
| - | >qualify customers where state in ('ca','co','tx') | + | |
| - | 272 CUSTOMERS records qualify | + | >qualify customers where state in ('ca','co','tx') |
| + | 272 CUSTOMERS records qualify | ||
| The next series of statements demonstrate the combined qualifying counts and the retrieval the qualified rows. | The next series of statements demonstrate the combined qualifying counts and the retrieval the qualified rows. | ||
| First, qualify all the customers in the states of California, Colorado, and Texas. 272 rows qualified. | First, qualify all the customers in the states of California, Colorado, and Texas. 272 rows qualified. | ||
| - | >qualify customers where state in ('ca','co','tx') | + | >qualify customers where state in ('ca','co','tx') |
| - | 272 CUSTOMERS records qualify | + | 272 CUSTOMERS records qualify |
| Next, qualify the customers in the cities of Denver, Chicago, and Dallas, that were included in the previous 272 qualified rows. | Next, qualify the customers in the cities of Denver, Chicago, and Dallas, that were included in the previous 272 qualified rows. | ||
| - | >qualify customers where AND city in('denver','chicago','dallas') | + | >qualify customers where AND city in('denver','chicago','dallas') |
| - | 14 CUSTOMERS records qualify | + | 14 CUSTOMERS records qualify |
| Remember that there are actually 25 customers in Denver, Chicago, and Dallas, but because of the SAMELIST operator 'AND' preceding the criteria, these 25 qualified records were intersected with the 272 qualified records. Obviously, Chicago is not in any of the three states, so the number of rows qualified by the city criteria dropped. Apparently, there are 10 customers in the city of Chicago. | Remember that there are actually 25 customers in Denver, Chicago, and Dallas, but because of the SAMELIST operator 'AND' preceding the criteria, these 25 qualified records were intersected with the 272 qualified records. Obviously, Chicago is not in any of the three states, so the number of rows qualified by the city criteria dropped. Apparently, there are 10 customers in the city of Chicago. | ||
| To retrieve the 14 customers, we run a SELECT statement with the 'ODXID' option. | To retrieve the 14 customers, we run a SELECT statement with the 'ODXID' option. | ||
| - | >select company, city, state from customers with 'odxid' | ||
| - | |||
| - | COMPANY | ||
| - | |||
| - | CITY | ||
| - | |||
| - | STATE | ||
| - | |||
| - | Fred Schmid TV and Appliances | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Cognos Corporation | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | HMO Colorado, Inc. | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | National Conference State Legislatures | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Anschutz Corporation | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Blue Cross & Blue Shield of Colorado | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Bock & Bock CPAS | ||
| - | |||
| - | Dallas | ||
| - | |||
| - | TX | ||
| - | |||
| - | The Denver Post | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Highland Park Independent School Dist. | ||
| - | |||
| - | Dallas | ||
| - | |||
| - | TX | ||
| - | |||
| - | National Cable Television Institute | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Texas Utilities Services, Inc. | ||
| - | |||
| - | Dallas | ||
| - | |||
| - | TX | ||
| - | |||
| - | Random Access | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Compusys Inc. | ||
| - | |||
| - | Denver | ||
| - | |||
| - | CO | ||
| - | |||
| - | Interactive Software | ||
| - | Denver | + | >select company, city, state from customers with 'odxid' |
| - | CO | ||
| It is also important to note that Denver, TX and Dallas, CO would also have been qualified, if those cities existed in this database. | It is also important to note that Denver, TX and Dallas, CO would also have been qualified, if those cities existed in this database. | ||
| Since we did not include an ORDER BY clause in the SELECT statement, the order the rows are returned is not guaranteed. | Since we did not include an ORDER BY clause in the SELECT statement, the order the rows are returned is not guaranteed. | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||