From NewHaven Software Wiki

In addition to the standard invoice and mail list filter criteria provided in CMS, you also have the option to write your own SQL. This is useful for when you'd want to create a filter that is beyond the standard options available through the CMS interface.

It is important to note that when you select the 'Write Own SQL' checkbox, it supersedes all other criteria and becomes the only enabled option on the filter screen. You cannot combine it with other filter criteria. You can, however, customize a mail filter created in CMS.

In the Mail List Filter section you have the option of creating a filter using the checkboxes to get as close as you can to your desired filter. Once saved you would then click on View SQL to see the resulting SQL CMS has created based on the options selected. You cannot edit that SQL there but you can copy it and create a new filter with 'Write Own SQL' and paste/edit it there.

This can be very handy and is rather easy to do as, often times, the only modification desired is a change in Boolean values. For example, if you create a filter based on two customer description codes (CDC's) the default Boolean is 'OR'. In other words, customers will qualify for this filter if they have one code OR the other. If you only wanted customers who had both codes, you would create a custom filter, paste in the SQL from the original filter and change the OR to an AND.

Syntax is very important in these filters so if you are writing one from scratch, please pay attention to these examples for guidance on what your Selects should look like (namely the use of DISTINCT and which tables are being referenced):

Mail List Filter requirements

Every mail list filter must include (start with) at least the following:

SELECT DISTINCT C.*
FROM CUST C
WHERE ...

Invoice Filter requirements

Every invoice filter must include (start with) at least the following:

SELECT DISTINCT I.* FROM INVOICE I, ORDERHDR O
WHERE ...
and I.order_id = O.order_id

Mail list filter examples

Products Purchased

Target customers based on products fulfilled within a date range (3 examples) and thus excludes unfulfilled back orders and future ships. Date range is based off of the fulfill date. These filters also exclude fully returned/canceled items.

• In a Date Range

SELECT DISTINCT C.*
FROM CUST C, ITEMSORD I
WHERE (I.PROD_CODE = '1'
AND I.FULFILL_DT between '2010-01-31' and '2010-03-31'
AND ((I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT))
 AND (I.CUST_NUM = C.ID)

• Greater than a specified date

SELECT DISTINCT C.*
FROM CUST C, ITEMSORD I
WHERE (I.PROD_CODE = '1'
AND I.FULFILL_DT > '2010-01-31'
AND ((I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT))
 AND (I.CUST_NUM = C.ID)

• Ordered in the last 30 days

SELECT DISTINCT C.*
FROM CUST C, ITEMSORD I
WHERE (I.PROD_CODE = '1'
AND I.FULFILL_DT > (TODAY(*) - 30)
AND ((I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT))
 AND (I.CUST_NUM = C.ID)

• Sold by a specific salesperson, only shipped and not returned (no date range)

SELECT DISTINCT C.*
FROM CUST C INNER JOIN orderhdr o ON c.id = o.cust_num
INNER JOIN itemsord i ON o.order_id = i.order_id
WHERE o.proforma <> 1
AND o.tempsave <> 1
AND i.status > 5
AND i.order_qty > (i.cur_return + cur_cancel)
AND (i.prod_code = 'PRODUCT CODE'
AND o.SALES_ID = 'Salesperson Username')

• Customers that bought from a specified list of products in a selected date range. Exclude if they bought any from another list of products or if the customer is set to Do Not Mail Catalog.

(this query has to look at your entire order history and will take a long time to run)

SELECT DISTINCT C.* FROM CUST C
LEFT JOIN CUSTFLAGS CF ON CF.ID = C.ID AND CF.FLAG_ID = 1
JOIN ITEMSORD I ON I.CUST_NUM = C.ID
WHERE I.FULFILL_DT BETWEEN '2012-02-01' AND '2013-03-07'
AND (I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT
AND I.PROD_CODE IN ('PRODCODE-A','PRODCODE-B')
-- FLAG_ID <> 1 by matching on null with the left join
AND CF.FLAG_ID IS NULL 
AND C.ID NOT IN 
(SELECT IO.CUST_NUM FROM ITEMSORD IO WHERE IO.PROD_CODE IN (
-- Comma delimited list of product codes to match for exclusion from the customer list
'PRODCODE-X','PRODCODE-Y','PRODCODE-Z')
GROUP BY IO.CUST_NUM 
-- End customer exclusion list
);

CMS Customer Filter for customers with a specific item on Back Order

Targeting Catalog Requestors

Phone number Area code

SELECT DISTINCT C.*
FROM CUST C 
WHERE
(C.ID IN (SELECT CUST_NUM FROM PHONES
WHERE phone_num like 
'(297%'
or phone_num like 
'297'
or phone_num like 
'1297'
or phone_num like 
'1-297'
))

Orders Placed from Selected Order Sources

SELECT DISTINCT c.*
FROM CUST c, ORDERHDR o
WHERE (o.ORDER_SRC in
(SELECT ID FROM ORDERSRC WHERE name in
('Phoned In','Mailed In'))
AND o.ORDER_DT BETWEEN '2011-01-31' AND '2011-10-31'
AND o.CANCELLED = 0)
AND (o.CUST_NUM = C.ID)

Invoice created in a date range with a captured credit card but invoice is unshipped

SELECT DISTINCT C.*
FROM CUST C, INVOICE I
JOIN Payledger p ON p.order_id = i.order_id
and p.invoice_id = i.invoice_id
JOIN chargeinfo r ON p.payid = r.payid
WHERE I.ORDER_DT between '2012-08-31' and '2013-02-28'
AND I.SHIPPED_FL = '0'
AND r.edc = 1
AND (I.CUST_NUM = C.ID)

Last Modified

The Last Modified field was added in CMS 8.0 but does not yet have a corresponding mail filter option. Here are a couple of examples of how you can build this into your filters:

• Last Modified in a range of dates:

SELECT DISTINCT C.*
FROM CUST C 
WHERE (C.LAST_UPDAT BETWEEN '2011/05/02' AND '2012/05/02')

Change the dates as needed.

• Last Modified in the last 180 days:

SELECT DISTINCT C.*
FROM CUST C 
WHERE (C.LAST_UPDAT > (TODAY(*) - 180))

Change 180 to whatever number of days back is desired.

Invoice filter examples

• Target invoices shipping to Canada

SELECT DISTINCT I.* FROM Invoice I, Address A, Orderhdr O
WHERE I.SHIP_ID = A.ADDRESS_ID
and A.COUNTRY = '001'
and I.order_id = O.order_id

• Target invoices with packages shipping via a specific method (useful with Best Shipping since the order/invoice no longer has a shipping method)

SELECT DISTINCT I.* FROM Invoice I, Package P, Orderhdr O
WHERE I.order_id = P.order_id and I.invoice_id = P.invoice_id
and I.order_id = O.order_id
and P.ship_via in
(SELECT ship_code FROM SHIPMETH
WHERE SEL_LETTER = 'P')