skcah
Writing / Netsuite

SuiteQL: Get the Default Billing and Shipping Address

Return a customer default billing address, default shipping address or both with SuiteQL while retaining customers without an address.

Ryan Sheehy2 minute read

The location of the default-address condition determines whether customers without a matching address remain in the result.

Return the default shipping address

Place defaultshipping = 'T' inside the LEFT JOIN:

SELECT
  customer.id AS customer_id,
  customer.entityid AS customer_number,
  shipping.id AS address_book_id,
  shipping.label AS address_label,
  shipping_address.addr1,
  shipping_address.addr2,
  shipping_address.city,
  shipping_address.state,
  shipping_address.zip,
  shipping_address.country
FROM customer
LEFT JOIN customeraddressbook shipping
  ON shipping.entity = customer.id
 AND shipping.defaultshipping = 'T'
LEFT JOIN customeraddressbookentityaddress shipping_address
  ON shipping_address.nkey = shipping.addressbookaddress

This returns a customer even when no address-book row is marked as the default shipping address.

Return the default billing address

Use the same joins with the billing flag:

LEFT JOIN customeraddressbook billing
  ON billing.entity = customer.id
 AND billing.defaultbilling = 'T'
LEFT JOIN customeraddressbookentityaddress billing_address
  ON billing_address.nkey = billing.addressbookaddress

Return both defaults on one customer row

Join the address-book path twice with clear aliases:

SELECT
  customer.id,
  customer.entityid,
  billing_address.addr1 AS billing_addr1,
  billing_address.city AS billing_city,
  shipping_address.addr1 AS shipping_addr1,
  shipping_address.city AS shipping_city
FROM customer
LEFT JOIN customeraddressbook billing
  ON billing.entity = customer.id
 AND billing.defaultbilling = 'T'
LEFT JOIN customeraddressbookentityaddress billing_address
  ON billing_address.nkey = billing.addressbookaddress
LEFT JOIN customeraddressbook shipping
  ON shipping.entity = customer.id
 AND shipping.defaultshipping = 'T'
LEFT JOIN customeraddressbookentityaddress shipping_address
  ON shipping_address.nkey = shipping.addressbookaddress

One address-book entry can be both the default billing and default shipping address. The two joins still give the result useful billing and shipping column names.

Why the condition should not always go in WHERE

This filter:

WHERE shipping.defaultshipping = 'T'

removes rows where shipping is NULL. That is appropriate only when the report should contain customers that definitely have a default shipping address.

For an export that must contain every customer, keep the predicate in ON.