SysDictCoder: Tricks with X++ embedded SQL

[Blog bot]

Источник: http://sysdictcoder.com/blog/tricks-...-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.

Источник: http://sysdictcoder.com/blog/tricks-...-embedded-sql/