Struct

static void Collection_Struct (Args _args)
{
    //Create a struct with two fields
    struct myCar = new struct ("int ModelYear; str Carbrand");
    int i;
    ;
    // Set values to the fields
    mycar.value ("ModelYear", 2000);
    mycar.value ("Carbrand", "BMW");

    // Add a new field and give it a value
    myCar.add ("Model", "316");

    //Loop through the fields of the struct
    for ( i = 1; i <= myCar.fields(); i++)
    {
         info ( strfmt ( "FieldType: %1, FieldName: %2, Value: %3",
                          myCar.fieldType ( i ), myCar.fieldName ( i ), myCar.Value ( myCar.fieldName ( i ))));
    }
}

Set

static void  Collection_Set (Args _args)
{
    // Create a new set of type String
    Set cars = new Set (Types :: String);
    SetEnumerator setE;
    ;

    // Add elements to the Set
    cars.add ("Toyota");
    cars.add ("Ford");
    cars.add ("Mazda");
   
    // Check to see if an element exist in the set
    if (cars.in ("Toyota"))
    into ("Toyota is part of the set");
   
    //Display the content of the set
    info (cars.toString());

    // Get the enumerator of the set to loop through it
    setE = cars.getEnumerator();
    while (setE.moveNext())
    {
        info(setE.current());
    }
}

Map

static void Collection_Map (Args _args)
{
    // Create a new map with a key and value type
    Map cars = newMap (Types :: Integer, Type :: String);
    MapEnumerator mapE;

    ;
    // Insert values to the map
    cars.insert (1, "Volvo");
    cars.insert (2, "BMW");
    cars.insert (3, "Chrysler");
    //Display the content of the map
    info (cars.toString()) ;
    // Get the enumerator to loop
    // through the elements of the map
    mapE = cars.getEnumerator();
    while (mapE.moveNext())
    {
        info (strfmt ("Car %1: %2, mapE.currentKey(), mapE.currentKey());
    }
}

List

static void Collection_List(Args _args)
{
    // Create a new list of type string
    List names = new List (Types :: String);
    ListEnumerator listE;

    ;
    // Add elements to the list
    names.addEnd ("Lucas");
    names.addEnd ("Jennifer");
    names.addEnd ("Peter");

    // Display the content of the list
    info (names.toString());
    // Get the enumerator of the list
    // to loop through it
    listE = names.getEnumerator();
    while (listE.moveNext())
  {
        info ( strfmt ("Name: %1", listE.current()));
  }
}

While select full statement

[while] select [reverse] [firstfast] [firstonly]
[forupdate] [nofetch] [crosscompany]
[forcelitterals | forceplaceholders] [forcenestedloop]
[forceselectorder]
[ * | <fieldlist> from] <tablebuffer>
[ index [hint] <indexname> ]
[ group by {<field>} ]
[ order by {<field> [asc][desc]} ]
[ where <expression> ]
[ [ outer | exists | notexists ] join [reverse]
[ * | <fieldlist> from] <tablebuffer>
[ index <indexname> ]
[ aggregate [sum] [avg] [minof] [maxof] [count]]
[ group by {<field>} ]
[ order by {<field> [asc][desc]} ]
[ where <expression> ]
]
<fieldlist> ::= <field> | <fieldlist> , <field>
<field> ::= fieldname | <function>(<field>)
<function> ::= sum | avg | minof | maxof | count

Data Manipulation - Insert, Update, Delete

// INSERT

custTable custTable;
;
custTable.accountNum ="1234";
custTable.name ="John Customer";
custTable.insert();
_________________________________________________________________________________

//UPDATE

SalesTable salesTable;
;
ttsbegin;
while select forupdate salesTable
where salesTable.CustAccount =="2001"
{
salesTable.SalesName ="New Enterprises";
salesTable.update();
}
ttscommit;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


InventTable InventTable;
;
ttsbegin;
while select forupdate InventTable
where InventTable.ItemGroupId =="Television"
{
InventTable.ItemPriceToleranceGroupId ="2%";
InventTable.update();
}
ttscommit;
Microsoft

_________________________________________________________________________________

//DELETE

CustTable custTable;
;
ttsbegin;
Select forUpdate custTable
where custTable.accountnum =="2032";
custTable.delete();
ttscommit;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
//DELETE_from

CustTable custTable;
;
delete_from custTable
where custTable.AccountNum =="4018";

While Select

CustTable custTable;
while select accountNum, name, address from custTable
{
print custTable.AccountNum, " ", custTable.Name, " ",
custTable.Address;
pause;
}
_________________________________________________________________________________

CustTable custTable;
;
while select custTable
where custTable.AccountNum > "4005"
&& custTable.AccountNum < "4017"
{
print custTable.AccountNum , " ",custTable.Name;
}
pause;
_________________________________________________________________________________
//Sorting Obtion index


CustTable custTable;
;
while select custTable index AccountIdx
{
print custTable.AccountNum, " ", custTable.Name;
}
pause;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 //ORDER BY


CustTable custTable;
;
while select custTable order by AccountNum desc
{
print custTable.AccountNum, " ", custTable.Name;
}
pause;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

//GROUP BY
SalesTable salesTable;
;
while select count(salesid) from salesTable group by
CustGroup
{
print salesTable.CustGroup," ",salesTable.salesid;
}
pause;

_________________________________________________________________________________

//Aggregate
sum -->Returns the sum of the values in a field.

avg -->Returns the average of the values in a field.

maxof -->Returns the maximum of the values in a field.

minof -->Returns the minimum of the values in a field.

count -->Returns the number of records that satisfy the statement. Count can only be used on numerical fields, since the result will be returned in that field.

select sum(amountMST) from ledgerTrans;
sumAmountMST = ledgerTrans.amountMST;
select count(recId) from ledgerTrans;
countLedgerTrans = ledgerTrans.recId;





_________________________________________________________________________________