SQL tutorial for X++ / Dynamic AX

Hi,

SQL tutorial for AX buds.

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

--------------------------------------
Consider this below table as example for upcoming scenarios.

Student --------------------------------------
studentID	Name	Gender	Native
Stu001	Fathima	Female	IND
Stu002	Faridth	Male	IND
Stu003	Faheed	Male	KSA
Stu004	Fakrudeen	Male	KSA
Stu005	Firdose	Female	IND
Stu006	Fahima	Female	KSA

---------------------------------------

All the SQL syntax is not applicable for X++;

SQL Tutorial
SQL Select	Basic select concern record	while SELECT field_Name FROM table_name
SQL Distinct	List unique records	while SELECT DISTINCT field_Name FROM table_name
SQL Where	Where condition	while SELECT field_Name FROM table_name where Table_Name.Field_Name ==/=< conditions
SQL And & Or	Show only IND female student	while SELECT StudentID FROM Student where  Student.Gender == Gender::Female && Student.Native == "IND"
	Show all female record, IND native also	while SELECT StudentID FROM Student where  Student.Gender == Gender::Female || Student.Native == "IND"
SQL Order By	Sort the record	while SELECT DISTINCT field_Name FROM table_name ORDER BY table_name.field_Name ASC/DESC
SQL Insert Into		MyTable  MyTable;  ttsBegin;  select MyTable ;  MyTable.AccountNum = '1101'; MyTable.Name = 'MyName';  MyTable.insert();  ttsCommit;
SQL Update		Student Student; ttsBegin;  select forUpdate Student where Student.StudentID= "Stu0002"; Student.StudentID = 'Stu0009'; Student.Name = "Rahima"; Student.update();  ttsCommit;
SQL Delete		static void DeleteMultiRow1bJob(Args _args)
		{
		MyWidgetTable tabWidget; // extends xRecord.
		;
		ttsBegin;
		while select
		forUpdate
		tabWidget
		where tabWidget .quantity <= 100
		{
		tabWidget .delete();
		}
		ttsCommit;
		}
SQL Select Top	Code fastup the search and once found it will search for remaining data	select FirstOnly PersonnelNunber where HcmWorker.PersonnelNumber == "0000567";
SQL Between	There is no between in X++ but	while select Table_Name where Table_Name.Field_Name >= RangeFromValue && Table_Name.Field_Name <= RangeToValue
SQL Wildcards		while SELECT DISTINCT field_Name FROM table_name
SQL Not Null		while select Table_Name where Table_Name.Field_Name != Null

Regards,

Product and product related information are stored in following tables Dynamic AX

Hi,

Table Name	Table Description
EcoResProduct	The EcoResProduct table stores products and is the base table in the products hierarchy.
EcoResProductMaster	The EcoResProductMaster table stores product masters.
EcoResProductIdentifier	The EcoResProductIdentifier table contains a product identification that is available for users.
EcoResDistinctProduct	The EcoResDistinctProduct table stores products.
EcoResDistinctProductVariant	The EcoResDistinctProductVariant table stores product variants.
EcoResProductDimensionGroup	The EcoResProductDimensionGroup table contains information about a dimension group.
EcoResProductDimensionGroupProduct	The EcoResProductDimensionGroupProduct table stores information about relationships between products and dimension groups.
EcoResColor	The EcoResColor table stores color names.
EcoResSize	The EcoResSize table stores size names.
EcoResConfiguration	The EcoResConfiguration table stores configuration names.
EcoResProductMasterColor	The EcoResProductMasterColor table stores information about colors assigned to product masters.
EcoResProductMasterSize	The EcoResProductMasterSize table stores information about sizes that are assigned to product masters.
EcoResProductMasterConfiguration	The EcoResProductMasterConfiguration table stores information about configurations assigned to product masters.
EcoResProductVariantColor	The EcoResProductVariantColor table stores information about the colors that are assigned to product variants.
EcoResProductVariantSize	The EcoResProductVariantSize table stores information about the sizes that are assigned to product variants.
EcoResProductVariantConfiguration	The EcoResProductVariantConfiguration table stores information about the configurations that are assigned to product variants.
EcoResProductMasterDimensionValue	The EcoResProductMasterDimensionValue table is the base table in the product model dimension hierarchy.
EcoResProductVariantDimensionValue	The EcoResProductVariantDimensionValue table is the base table in the product variant dimension hierarchy.
EcoResProductDimensionAttribute	The EcoResProductDimensionAttribute table contains definitions of product dimension attributes (categories).
EcoResInstanceValue	The EcoResInstanceValue table contains the definitions of the instances of the components or products.
EcoResProductInstanceValue	The EcoResProductInstanceValue table contains definitions of values for the instances of attributes of a product.
InventTable	The InventTable table contains information about items.
InventTableModule	The InventTableModule table contains information about purchase, sales, and inventory specific settings for items.
InventItemLocation	The InventItemLocation table contains information about items and the related warehouse and counting settings. The settings can be made specific based on the items configuration and vary from warehouse to warehouse.
InventItemSalesSetup	The InventItemSalesSetup table contains the default settings for items, such as site and warehouse. The values are related to sales settings.
InventItemInventSetup	The InventItemInventSetup table contains the default settings for items, such as site and warehouse. The values are related to inventory settings.
InventItemPurchSetup	The InventItemPurchSetup table contains the default settings for items, such as site and warehouse. The values are related to purchase settings.
InventItemSetupSupplyType	The InventItemSetupSupplyType table contains information about the sourcing of items.
InventDim	The InventDim table contains values for inventory dimensions.
InventDimCombination	The InventDimCombination table contains variants of items. The variants are created as product variants that are based on product dimensions such as size, color, and configuration. These variants are replicated to the legal entity.

Regards,