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 Filters

Requirements

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

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

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.

Customers with Unshipped Orders in a Date Range

SELECT DISTINCT C.*
FROM CUST C 
LEFT OUTER JOIN ORDERHDR O ON (C.ID = O.CUST_NUM)
WHERE
((O.order_dt BETWEEN '2012-05-18' AND '2013-09-18')
AND (isnull(o.shipped_dt,'1899-12-30') = '1899-12-30'))
AND (C.EMAIL <> '')

By First Order Date

SELECT DISTINCT C.*
FROM CUST C
WHERE
(C.FIRST_DATE BETWEEN '2013/08/15' AND '2013/08/15')

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

Customers with a Company Name

SELECT DISTINCT C.*
FROM CUST C
WHERE C.COMPANY <> ''

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.

All Recipients for a Customer (e.g. Amazon)

If you are processing orders with Amazon or similar partner, you'll only get shipping addresses and not billing information from them since Amazon handles all of the payments. As such these orders will import into CMS with Amazon as the customer and always attach themselves to your Amazon customer record. If you have the option enabled in CMS Order Entry Options, new customer records will also be created for each ship-to. It may be helpful to be able to identify those ship-to customer records. This might be to tag them with a customer description code, exclude from an upcoming mailing, etc. This SQL can be used as a mail list filter to find those recipient customer records that have ordered through Amazon (or whoever your orders are coming from.)

SELECT DISTINCT C.*
FROM CUST c
WHERE C.ID in
(SELECT a.cust_num
FROM Address a
Join recipient r ON r.address_id = a.address_id
Join recipient_order ro ON ro.recipient_id = r.recipient_id
Join orderhdr o ON o.order_id = ro.order_idWhere o.cust_num = '0000518128')

Change the customer number in the last line to match your billing account for Amazon. Zero pad to ten total characters as shown in the above example.

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)

Customers with a declined credit card in last 30 days

SELECT DISTINCT C.*
FROM CUST C
JOIN paylist l ON l.ID = C.ID
JOIN paytypes t ON t.pay_type = l.pay_type
WHERE l.badpayment = '1'
AND l.DELETED = '0'
AND t.credit_car = '1'
AND l.createdate > (TODAY(*)-30)

Filter based on one of the customer user defined fields where the value is greater than X

SELECT DISTINCT C.*
FROM CUST C
WHERE (REGEXP_SUBSTR(C.USERCODE1,'\d+\.*\d*') > 12.4)

• This method allows you to perform a greater or less than query on a user field which is not explicitly defined as numeric. That said, this filter will fail if you have any non-numeric values in the corresponding user-defined field in any of the customer records.
• It is important to understand that any time you are running a "greater than" or "less than" operator against a numeric value you must not use quotes. Otherwise SQL will interpret that as a string comparison. And string comparing numeric values will not be accurate.

Invoice filters

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

Examples

• Target invoices whose item total is greater than $X

SELECT DISTINCT I.* FROM Invoice I, Orderhdr O
WHERE I.ITEM_TOT > 300
and I.order_id = O.order_id

Modify the 300 in this example to represent the desired item total threshold. This item total take into account line and order level discounts but does not include S&H, taxes, or other order level charges.

• 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 specific methods. This is useful with Best Shipping since the order/invoice no longer has a shipping method or if you're trying to filter on the actual shipping choice used instead of the underlying service level (which is how the shipping method filters work normally)

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.shipmethid in
(SELECT shipmethid FROM SHIPMETH
WHERE SEL_LETTER in ('C','UC'))

• 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