| Subject | Re: SQL statement |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Sun, 24 Apr 2016 09:49:12 +0200 |
| Newsgroups | dbase.getting-started |
On 23/04/2016 22:56, edward racht wrote: > Thank you both for your inspiration. It didn't occur to me to use 'null' or 'is not null' > > The code I chose, for now, follows. > > q.sql := 'select distinct GA FROM rs WHERE CE IS NOT NULL' > > I have placed it in a form using several combobox for querying a table to select the fields to base the printing params on. I need the 'distinct' to keep the list in the combobox to the shortest list. > > In this case CE is the first group and GA is the second group for the report params. > > Over time I realized that I needed to weed out the rows that do not have the first group as some selections turned up with empty reports. Now that you've explained that you need a list for a combobox the use of DISTINCT in your select statement is correct. You do, however, need to be careful about using WHERE CE IS NOT NULL as your where clause. The where clause may be working for you now but this is only because the records with an empty CE field have never had data entered in the field. This means that they contain nulls. If, however, you ever enter a value and then delete it the field will be blank but it will not be null. This, of course, may never happen but if it does you will have blanks in your list. To be quite sure that you will never have blanks in the list you should use either WHERE CE <> '' (dBASE checks for both empty strings and nulls) or WHERE NOT (CE = '' OR CE IS NULL) (Overkill. :-) but it's clear in you code what records you're excluding.) Mervyn. | |