GROUPED BY

Previous Next

GROUPED BY clauses in the USoft Rule Language are the equivalent of GROUP BY clauses in ordinary USoft constraint SQL, which in turn are the equivalent of GROUP BY clauses in conventional SQL.

The GROUPED BY clause may only appear immediately after an initial FOR clause or (in the case of multiple, joined tables) immediately after a FOR ... CONNECTED BY construct.

Syntax

GROUPED BY   single-string-object-name-plural   table-alias

where the required and case-insensitive single-string-object-name-plural is the same or similar to the Object Name Plural value for the table as defined in USoft Definer:

If the Object Name Plural value does NOT contain a space, it is equivalent to the Object Name Plural value, whether surrounded by optional double quotes or not;

If the Object Name Plural DOES contain one or more spaces, you must surround single-string-object-name-plural by double quotes.

For example, if you have a table BANK_ACCOUNT with Object Name Plural = Bank Accounts, the single-string-object-name-plural is:

"Bank Accounts"

and the following are legal variants:

"bank accounts"

"BANK ACCOUNTS"

Example

USoft Rule Language

USoft SQL Equivalent

Conventional SQL Equivalent

FOR                customers cust
,                  car       car
CONNECTED AS       cust "USES" car
GROUPED BY         customers cust
,                  car       car
DO NOT ALLOW THAT  COUNT(*) > 1

SELECT    cust.type

,         car.type
FROM      customer cust
,         car      car
RELATE    cust "USES" car
GROUP BY  cust.type
,         car.type
HAVING    count(*) > 1

SELECT    cust.type

,         car.type
FROM      customer cust
,         car      car
WHERE     cust.id = car.cust_id
GROUP BY  cust.type
,         car.type
HAVING    count(*) > 1