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

NOTE: When writing your own SQL keep in mind if you are going to be using these statements in Compound Mail List Filters, you will need to return C.ID, not C.* This makes these individual filters suitable for use only as components for Compound filters. They can no longer be used on their own. Our recommendation is to label them as compound filter components. Like COMP_Products_By_Date and COMP_Not_Select_Products. Doing this you will know not to use them by themselves. To use the filters by themselves and to combine different filters together, you have to use joins and not compound filters.

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

Click the title link to redirect to an article specifically on this topic.

Targeting Catalog Requestors

Click the title link to redirect to an article specifically on this topic.

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

Customers having more than one Customer Description Code

In CMS it is easy to find customers who have any one code, or one of several, but you'll need custom SQL like the following to find customers who must have more than one of a list of codes to qualify.

SELECT DISTINCT C.*
FROM CUST C  
WHERE C.ID IN (
  SELECT CUST_NUM FROM CUSTDESC
  WHERE CUSDESKE in ('0525','0600')
  GROUP BY CUST_NUM
  HAVING COUNT(CUST_NUM) >= 2 -- 2 = the number of values to find
)

• Change the cusdeske values to correspond to the desired customer description codes (CDC) per the CUSDESCO table
• Change the integer in the last line to indicate the number of CDC matches required

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

• Targets fulfilled invoices with a specific adcode that does not use a specific term and the order does not contain an item ending with M:

SELECT DISTINCT I.* FROM INVOICE I, ORDERHDR O
WHERE I.order_id = O.order_id
AND O.adcode = ' HOMESHOW'
AND O.term_id <> '002'
AND I.fulfill_dt > '2010-01-01'
AND I.order_id|I.invoice_id NOT IN
(SELECT order_id|invoice_id 
FROM itemsord
WHERE fulfill_dt > '2010-01-01'
AND prod_code LIKE '%M')

• Single Item Orders - The technique used here to identify which invoice are single product is unusual and noteworthy. There is a join against a sub-select because a sub-select on its own is only useful when looking for a single field and in this case we need both order_id and invoice_id.

SELECT DISTINCT I.* FROM Invoice I
JOIN ORDERHDR O ON O.order_id = I.order_id
JOIN ITEMSORD T ON T.order_id = I.order_id AND T.invoice_id = I.invoice_id
JOIN (SELECT order_id, invoice_id FROM itemsord
GROUP BY ORDER_ID, Invoice_id
HAVING COUNT(line_id) = 1) j ON j.order_id = I.order_id AND j.invoice_id = I.invoice_id
WHERE I.TRANS_TYPE <> 'R' --Excluding returns
AND T.PROD_CODE IN ('SUB1','SUB2','SUB3')

• Invoices by State and Carrier - We wrote this for invoice filtering by state. This shows the use of a sub-select that is not part of a join:

SELECT DISTINCT I.* FROM Invoice I
JOIN Orderhdr O ON O.order_id = I.order_id
JOIN Address A ON A.ADDRESS_ID = I.SHIP_ID
WHERE A.STATE IN
('ME','NH','VT','MA','CT','RI','NJ','DE','MD','DC','NY','PA','VA','NC','SC','GA','TN','KY','WV','OH','AL')
AND O.SHIP_VIA IN (SELECT ship_code FROM SHIPPERS WHERE SHIPPER = 'UPS')

Related Articles

• Compound Mail List Filters