Creating Yes/No Fields in SQL Queries

Published: January 27, 2010

Hey, Ted:

I want to retrieve a list of customers using SQL. I know how to select the customers I want. What I don't know how to do is create some columns that are not stored in the database, but must be built from information in other files. For example, I want a column that tells whether or not the customer has an open (unfulfilled) order. I'd also like a column that tells if he has ever bought a certain line of product. There are no fields in the customer master file that store this information. Can you help?

--Rick

Good question, Rick. You'll be glad to hear that this is not hard to do.

Let's assume you want all type-A customers (whatever that means).

select c.customerno, c.cusnam
  from customers as c
 where c.type = 'A'

Here they are:

CUSTOMERNO  CUSNAM
  12345     Cal E. Phornya
  23456     Billy Rubin
  34567     Polly Unsaturated
  45678     Molly Coddle

Let's assume that closed (or fulfilled) orders are indicated by a status code of "9" in the sales order header file. Here are the customers with open orders.

select distinct o.customerno, 'Yes' as HasActiveOrder
  from salesordh as o
 where o.status <> '9'

CUSTOMERNO  HASACTIVEORDER
  34567         Yes
  45678         Yes
  12345         Yes
  56789         Yes

Notice the use of DISTINCT to eliminate duplicate customer numbers from the list. Notice also that the literal "Yes" fills up a column named HasActiveOrder.

As for the customers who have ever ordered a certain line of product, let's assume the product line is in a class field in an item master file, and that the items a customer has ordered are in the sales order details file. Here are the customers who've ordered items from product line B3.

select distinct o.customerno, 'Yes' as BoughtB3
  from salesordh as o
  join salesordd as d
    on o.orderno = d.orderno
  join items as i
    on d.itemno = i.itemno
 where i.class='B3'

CUSTOMERNO  BOUGHTB3
  34567      Yes
  23456      Yes

This is the same sort of thing. DISTINCT removes duplicate customer numbers from the list, and the literal "Yes" indicates that the customer has bought product of class B3.

Now let's put it all together. We need to join the first query to the other two. We must use a left outer join, because some customers don't qualify for the last two queries. The HasActiveOrder and BoughtB3 columns will come back "null" for customers who are not returned by the last two queries, so we use the COALESCE function to convert nulls to the value No.

Here's the query:

select c.customerno, c.cusnam, 
       coalesce(act.HasActiveOrder,'No') as HasActiveOrder,
       coalesce(b3.BoughtB3,'No') as BoughtB3
  from customers as c
  left join 
          (select distinct o.customerno, 'Yes' as HasActiveOrder
                  from salesordh as o
                  where o.status <> '9') as act
    on c.customerno = act.customerno
  left join 
          (select distinct o.customerno, 'Yes' as BoughtB3
             from salesordh as o
             join salesordd as d
               on o.orderno = d.orderno
             join items as i
               on d.itemno = i.itemno
            where i.class='B3') as b3
    on c.customerno = b3.customerno
 where c.type = 'A'

And this is what the result might look like:

CUSTOMERNO CUSNAM             HASACTIVEORDER  BOUGHTB3
 12345     Cal E. Phornya     Yes             No
 23456     Billy Rubin        No              Yes
 34567     Polly Unsaturated  Yes             Yes
 45678     Molly Coddle       Yes             No

Isn't it great how we can put SQL SELECT statements in places once reserved for table and view names?

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot