From NewHaven Software Wiki

In addition to the standard invoice and mail list filter options provided in CMS, you also have the option to use a 'Write Own SQL' option. 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 then is the only valid option on the filter screen. You cannot combine it with other checkboxes. You can however customize a mail filter created in CMS.

For 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 easy to do as often times the only change 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 you selects should look like (namely the use of DISTINCT and which tables are being referenced):

Mail List Filter requirements

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

Invoice Filter requirements

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

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

Products Purchased

• Target customers on products purchased within a date range (3 examples). Date range is based off of the fulfill date, and these filters also exclude returned/canceled items.

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