Select Statement Examples

All of the following examples use the CustTable.

To illustrate how the select statements work, assume that the customer table has only five records with the following contents.

AccountNum

NameRef

Balance

Blocked

100

Coho Vineyard

$10,000

No

200

Coho Winery

$20,000

No

300

Coho Vineyard & Winery

$30,000

No

4000

Fabrikam, Inc.

$5,000

Invoice

5000

Fourth Coffee

$1,000,000

No

The following X++ method shows several small examples of how you can use the select statement.

void selectRecordExamples()
{
    CustTable custTable;
    ;
    // A customer is found and returned in custTable
    select * from custTable;

    // A customer with account number > "100" is found
    select * from custTable
        where custTable.AccountNum > "100";

    // Customer with the lowest account number > "100" found:
    // Coho Winery with number 200.
    select * from custTable order by accountNum
        where custTable.AccountNum > "100";

    // The next customer is read (Coho Vineyard & Winery)
    next custTable;

    // Customer with higest account number
    // (greater than 100) found: Fourth Coffee
    select * from custTable order by accountNum desc
        where custTable.accountNum > "100";

    // The next record is read (DESC): Fabrikam, Inc.
    next custTable; 

    // Customer with highest account number found: Fourth Coffee
    select reverse custTable order by accountNum;

    // Customer with "lowest" name and account number
    // in the interval 100 to 1000 is found. This is Coho Winery.
    select * from custTable order by name
        where custTable.accountNum > "100"
        && custTable.accountNum < "1000";

    // The count select returns the number of customer
    // account numbers (5)
    select count(AccountNum) from custTable;
    // Prints the result of the count
    print custTable.accountNum; 

    // Returns the sum of balances for non-blocked customers.
    // The result is: SUM: $1,060,000, stored in the Balance 
    // field 
    select sum(balance) from custTable
        where custTable.blocked == DebCreBlocked::No;
}

This X++ code sample shows how an inner join can be performed as part of an SQL select statement.

The sample also shows an order by clause that has each field qualified by a table name. This enables you to control how the retrieved records are sorted by using only one order by clause.

static void SelectJoin2Job(Args _args)
{
    Address addressTable2;
    AddressState addressStateTable3;
    struct sut4;
    ;
    sut4 = new struct("str StateName; str StateId; str Phone");

    while select *
        from addressTable2
            order by addressStateTable3 .Name
                , addressTable2 .Phone
        join addressStateTable3
            where addressTable2 .State ==
                addressStateTable3 .StateId
                && addressTable2 .State LIKE "N*"
    {
        sut4.value("StateName", addressStateTable3 .Name );
        sut4.value("StateId", addressStateTable3 .StateId );
        sut4.value("Phone", addressTable2 .Phone );

        info(sut4.toString());
    }
/*********  Actual Infolog output
Message (02:55:58 pm)
(StateName:"New Jersey"; StateId:"NJ"; Phone:"(218) 636-5327")
(StateName:"New South Wales"; StateId:"NSW"; Phone:"(07) 3245 2200")
(StateName:"New South Wales"; StateId:"NSW"; Phone:"(07) 5621 0357")
(StateName:"New South Wales"; StateId:"NSW"; Phone:"+61 (8) 9332 9446")
(StateName:"New York"; StateId:"NY"; Phone:"(619) 281-0544")
(StateName:"New York"; StateId:"NY"; Phone:"(701) 484-5719")
(StateName:"North Dakota"; StateId:"ND"; Phone:"(312) 558-1010")
(StateName:"North Dakota"; StateId:"ND"; Phone:"(312) 558-1010")
(StateName:"North Dakota"; StateId:"ND"; Phone:"(701) 773-3597")
*********/
}

This X++ code sample shows that the fields in the group by clause can be qualified with a table name. There can be multiple group by clauses instead of just one. The fields can be qualified by table name in only one group by clause. Use of table name qualifiers is recommended.

The order by clause follows the same syntax patterns that group by follows. If provided, both clauses must appear after the join (or from) clause, and both must appear before the where clause that might exist on the same join. It is recommended that all group by and order by and where clauses appear immediately after the last join clause.

static void SelectGroupBy6Job(Args _args)
{
    Address tabAddress;
    AddressState tabAddressState;
    ;
    info("Start of job.");

    WHILE SELECT
        count(RecId)
        from tabAddress
        join tabAddressState
            GROUP BY
                tabAddress .State
                ,tabAddressState .Name
            ORDER BY tabAddress .State desc
            where
                tabAddress .State like "*N*"
                && tabAddressState .StateId == tabAddress .State
    {
        info(strFmt
            ("%1 = Count , StateId = %2 , StateName = %3"
            ,tabAddress .RecId ,tabAddress .State
            ,tabAddressState .Name
            ));
    }
    info("End of job.");
/***********  Actual Infolog output
Message (04:03:16 pm)
Start of job.
1 = Count , StateId = TN , StateName = Tennesee
3 = Count , StateId = ON , StateName = Ontario
2 = Count , StateId = NY , StateName = New York
3 = Count , StateId = NSW , StateName = New South Wales
1 = Count , StateId = NJ , StateName = New Jersey
3 = Count , StateId = ND , StateName = North Dakota
2 = Count , StateId = MN , StateName = Minnesota
End of job.
***********/
}

Community Additions

ADD
Show: