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.
So good!
Interesting example for X++ SQL. I’m getting starter in DAX, and did like of your post.
see you folks!
Thanks. Now I understand better what’s happening in the code.