Creating Yes/No Fields in SQL Queries
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?
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?