S-SQL Examples N

I have a few "groups of countries" in a countries table that should not have a latitude such as the EU. As a result so I would expect to be able to find them by looking for records with null in the latitude field. The second example is similar, looking for any countries with either a null latitude or null longitude. In my particular database, the only "country" matching that is the EU.

(query (:select 'id 'name
                :from 'countries
                :where (:is-null 'latitude)))

((6 "EU"))

(query (:select 'name :from 'countries
        :where (:or (:is-null 'latitude)
                    (:is-null 'longitude))))

(("EU"))

The next gives the number of records (without using the sql count operator) from countries where the currency field was both not null and did not consist of just a blank string.

(length
 (query (:select 'id
         :from 'countries
         :where (:and (:not (:= "" 'countries.currency))
                      (:not (:is-null 'countries.currency))))))

Similarly, you can use :not-null

(query (:select 'ta :from 'a :where (:not-null 'ta)))

Suppose you want to perform division, but do not know if the divisor could be zero. The following will handle that and return 0 in that case.

(let ((divisor 3))
  (query (:select (:coalesce (:/ 12 (:nullif divisor 0 )) 0))
         :single))

(let ((divisor 0))
  (query (:select (:coalesce (:/ 12 (:nullif divisor 0 )) 0))
         :single))