Scenarios for Business Objects and Relations

Identifying All Articles Using a Price Per Item Limit

The following query outputs all articles, for example, whose price per item is over 10 euros and under 100 euros.

SELECT [articles]
FROM [prices]
WHERE [priceperitem] > 10 AND [priceperitem] < 100

Identifying All Articles Without a Purchase Price

In the following example, all articles for which no value is entered in the purchaseprice element type are queried.

SELECT [articles]
FROM [prices]
WHERE [purchaseprice] IS NULL

Identifying All Orders With Price Restrictions

The query lists all orders for which the price element type contains one of the specified values (100, 200, or 300).

SELECT *
FROM [order]
WHERE [price] IN (100,200,300)

Identifying All Paid Invoices With a Comment

In this example query, all paid invoices are listed that contain a comment in which the letter 'a' appears at any point.

SELECT *
FROM [invoice]
WHERE [paid] = true AND [comment] LIKE '%a%'

Identifying All Warranties With Restrictions

In this example query, all warranties are listed that do not have Supplier as the type and are valid from 08/17/2012.

SELECT [w]
FROM [dms]:[warranty] AS [w]
WHERE NOT ([typeOfWarranty] = 'Supplier' OR [validFrom] >= datetime '2012-08-17')

Identifying All Customers Whose Last Name Does Not Contain an 'M'

This query lists all customers whose last name does not contain the letter 'm.'

SELECT *
FROM [customer]
WHERE not [last name] like "m%"

Identifying All Invoices that Have Been Paid

In this example query, all invoices are listed that have been marked as paid.

SELECT *
FROM [invoice]
WHERE [paid] = true

Identifying All Invoices in a Specified Time Period

The following query outputs all invoices in the period from 1/1/2000 to 1/1/2010.

SELECT *
FROM [invoice]
WHERE [date] > datetime'2000-01-01' AND [date] < datetime'2010-01-01'

Identifying All Invoices With Amount Restrictions

The following query identifies all invoices whose amount is either less than 100 or greater than 1,000,000,000, and which have not been paid.

SELECT *
FROM [invoice]
WHERE ([amount] < 100 OR [amount] > 1000000000) AND [paid] = false

Identifying a Warranty Using the Date

With this example, you can identify a warranty using the date specified.

SELECT [w]
FROM [dms]:[warranty] AS [w]
WHERE [validFrom] >= datetime '2012-08-17'

Identifying a Person Using the Specified ID

With this example, you can identify a person using the specified sysId.

SELECT [p]
FROM [dms]:[person] AS [p]
WHERE [sysId] = id'ABCDEF12345678901234567890123456'

Identifying a Person Using the Specified Country

With this example, you can identify a person using the specified country.

SELECT [p]
FROM [dms]:[person] AS [p]
WHERE [country] = 'Germany'

Identifying a Business Partner With the Name ACME Corp.

The following statement returns the business partner with the name 'ACME Corp.'

SELECT [dms]:[businesspartner]
FROM [dms]:[businesspartner]
WHERE [dms]:[businesspartner].[sysname] = 'ACME Corp.'

The same example with aliases:

SELECT [bp]
FROM [dms]:[businesspartner] AS [bp]
WHERE [bp].[sysName] = 'ACME Corp.'

Identifying Elements of a Business Object

With the following eSQL statement, you select some of the elements of the business object dms:person. The statement returns a table with five columns. You can select even more elements in this way.

SELECT [firstname], [lastname], [country], [city], [street]
FROM [person] AS [p]