postmodern-n (null)

[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

Null

I have a few groups of countries in the countries table. They should not have a latitude, 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))))))

Not-Null

Similarly, you can use :not-null

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

Nullif

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))

4              

(let ((divisor 0))

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

         :single))

0