SAP Business One Customer Lookup Query
SQL queries to find customers by name or code, with billing and shipping addresses.
These are sample queries you can run on Manager Boost. Use them as-is, or edit and customize on our platform. Go custom or use our pre-built queries — you're in control.
Key Concepts
CardType Field
OCRD stores all business partners. Use CardType to filter: 'C' = Customer, 'S' = Supplier (vendor), 'L' = Lead. Always include CardType = 'C' for customer queries.
Address Structure
OCRD has basic address fields, but full addresses are in CRD1. Each customer can have multiple addresses — use AdresType to distinguish:
AdresType = 'B'— Billing (bill-to)AdresType = 'S'— Shipping (ship-to)
Default Addresses
MailAddres (default billing) and ShipToDef (default shipping) on OCRD contain the address name — not the address itself. Join CRD1 matching this name to get the default address details.
Contact Persons
OCRD has primary contact fields (Phone1, E_Mail). For multiple contacts, join OCPR which has one row per contact person with their role/position.
Available Fields Reference
OCRD (Customer Master)
CardCode CardName CardFName Phone1 Phone2 Cellular Fax E_Mail Address City Country ZipCode MailAddres ShipToDef Currency CreditLine Balance
CRD1 (Addresses)
Address AdresType Street Block City County State Country ZipCode Building StreetNo
Find Customer by Name
Basic lookup with main contact info:
SELECT
CardCode,
CardName,
Phone1,
Cellular,
E_Mail
FROM OCRD
WHERE CardType = 'C'
AND CardName LIKE '%acme%'
ORDER BY CardName| CardCode | CardName | Phone1 | Cellular | E_Mail |
|---|---|---|---|---|
| C10045 | Acme Corporation | 555-0123 | 555-0124 | info@acmecorp.com |
With Default Billing & Shipping
Join CRD1 twice — once for billing, once for shipping — filtering to default addresses:
SELECT
T0.CardCode,
T0.CardName,
T0.Phone1,
T0.E_Mail,
-- Default Billing Address
B.Street AS BillStreet,
B.City AS BillCity,
B.State AS BillState,
B.ZipCode AS BillZip,
B.Country AS BillCountry,
-- Default Shipping Address
S.Street AS ShipStreet,
S.City AS ShipCity,
S.State AS ShipState,
S.ZipCode AS ShipZip,
S.Country AS ShipCountry
FROM OCRD T0
LEFT JOIN CRD1 B ON T0.CardCode = B.CardCode
AND B.AdresType = 'B'
AND B.Address = T0.MailAddres
LEFT JOIN CRD1 S ON T0.CardCode = S.CardCode
AND S.AdresType = 'S'
AND S.Address = T0.ShipToDef
WHERE T0.CardType = 'C'
AND T0.CardCode = 'C10000'| CardCode | CardName | Phone1 | BillCity | BillState | ShipCity | ShipState |
|---|---|---|---|---|---|---|
| C10000 | Acme Corp | 555-0100 | New York | NY | Chicago | IL |
Note: MailAddres and ShipToDef store the address name (like "Main Office"), not the address itself. We join on that name to get the default.
All Addresses for a Customer
List all billing and shipping addresses, flagging the defaults:
SELECT
T1.Address AS AddressName,
CASE T1.AdresType WHEN 'B' THEN 'Billing' ELSE 'Shipping' END AS Type,
CASE
WHEN T1.AdresType = 'B' AND T1.Address = T0.MailAddres THEN 'Yes'
WHEN T1.AdresType = 'S' AND T1.Address = T0.ShipToDef THEN 'Yes'
ELSE 'No'
END AS IsDefault,
T1.Street,
T1.City,
T1.State,
T1.ZipCode,
T1.Country
FROM OCRD T0
INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
WHERE T0.CardCode = 'C10000'
ORDER BY T1.AdresType, T1.Address| AddressName | Type | IsDefault | Street | City | State |
|---|---|---|---|---|---|
| Main Office | Billing | Yes | 123 Main St | New York | NY |