Tricks with X++ embedded SQL

There are some tricks that you can do in X++ SQL. They’re not really well known because they’re not suited for normal use and there are not many clues in the editor that they exist. But they do come in handy every now and then.

Getting data without declaring a table variable

It’s possible to get data from the database without declaring any table variables. It goes something like this:

static void Job3(Args _args)
{
    // No variables!
    ;
 
    print (select CustTable).AccountNum;
    pause;
}

Note the parentheses around the select statement. This tells the compiler to create a temporary, anonymous buffer to hold the record. The select statement uses the exact table name and is immediately followed by a field name to get a value from a field.

If you use this there’s no Intellisense to help you look up the field name because the editor doesn’t know the type of the anonymous buffer.

This technique is often used in exist() methods on tables.

Getting a value from a field if you only know the field ID

Sometimes you don’t know the exact field name but you do know a field ID. Even in cases like this it’s possible to write an X++ SQL statement without knowing any field names.

Take a look at this example.

static void Job4(Args _args)
{
    CustTable   custTable;
    FieldId     fieldId;
    ;
 
    fieldId = fieldNum(CustTable, AccountNum); // This could be passed as a method parameter
 
    select custTable; // Get first record
 
    print custTable.(fieldId); // Print account number
 
    select custTable
        where custTable.(fieldId) == '1101';  // Where clause based on field ID
 
    print custTable.Name;
    pause;
}

It’s possible to use field IDs to retrieve values as well as use them in a where clause. Again parentheses are the key. Instead of writing a regular field name, wrap the ID in parentheses and you’re done. This makes it possible create generic code to handle any table and field without knowing the types at compile time. If you dig around in the classes for importing and exporting data you’ll find some examples.