using SQL Query in X++

 If you need to execute sql query inside your X++ code then below example can help you. To pass value to SQL query we need to use strfmt() function. 

       str sql;

       Connection conn;

       SqlStatementExecutePermission permission;

       ;

       sql= strFmt("update ProdBOM set InventDimId = '%1' where RecId = %2",                                                                   inventDim.inventDimId,ProdBOM.RecId);

       permission = new SqlStatementExecutePermission(sql);

       conn = new Connection();

       permission = new SqlStatementExecutePermission(sql);

       permission.assert();

       conn.createStatement().executeUpdate(sql);

      

       CodeAccessPermission::revertAssert();

Get Product Master Size,Color,Style, ConfigID in D365FO using SQL

If you want to get Product Master Size,Color,Style, ConfigID and thier names then below three SQL queries can help you get data quickly from D365FO compared to X++ development. Normally the volume of data is high when we try to export these types of data.

1) Product Master Size

SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecoressize.NAME                 [PRODUCTSIZEID],

       isnull(ecoresproducttranslation.NAME,'')   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [SIZENAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.sizeproductmaster

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecoressize ecoressize

               ON ecoressize.recid = ecoresproductmasterdimensionvalue.size_

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'

2) Product Master Color

SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecorescolor.NAME                [PRODUCTCOLORID],

       isnull(ecoresproducttranslation.NAME,'')   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [COLORNAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.colorproductmaster

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecorescolor ecoResColor

               ON ecorescolor.recid = ecoresproductmasterdimensionvalue.color

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'

3) Product Master Style

SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecoresstyle.NAME                [PRODUCTSTYLEID],

       ecoresproducttranslation.NAME   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [STYLENAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.styleproductmaster

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecoresstyle ecoresstyle

               ON ecoresstyle.recid = ecoresproductmasterdimensionvalue.STYLE

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'

4) Product Master Config

SELECT ecoresproduct.recid             [PRODUCTMASTERNUMBER],

       ecoresconfiguration.NAME        [PRODUCTCONFIGURATIONID],

       ISNULL(ecoresproducttranslation.NAME,'')   [PRODUCTNAME],

       inventitemgroupitem.itemgroupid [ITEMGROUP],

       ''''+ inventtable.itemid              [ITEMID],

   isnull(translate.NAME,'')   [CONFIGNAME]

FROM   ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue

       INNER JOIN ecoresproduct ecoResProduct

               ON ecoresproduct.recid =

                  ecoresproductmasterdimensionvalue.CONFIGPRODUCTMASTER

       LEFT OUTER JOIN ecoresproducttranslation

                    ON ecoresproducttranslation.product = ecoresproduct.recid

       LEFT JOIN inventtable

              ON inventtable.product = ecoresproduct.recid

       LEFT JOIN inventitemgroupitem

              ON inventitemgroupitem.itemid = inventtable.itemid

                 AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid

       INNER JOIN ecoresconfiguration ecoresconfiguration

               ON ecoresconfiguration.recid = ecoresproductmasterdimensionvalue.configuration

       LEFT JOIN ecoresproductmasterdimvaluetranslation translate

              ON translate.productmasterdimensionvalue =

                 ecoresproductmasterdimensionvalue.recid

WHERE  inventtable.DataAreaId ='@DataAreaID'

Get released product variant in AX 2012 R3

 If you want get released product variants of AX 2012 using SQL, then SQL script can help you. Here I am fetching Item Number, Product Variant Number, Style,Color, Size, Product Name. 

Please notice the join condition of InventDimCombination with EcoResProduct table. I wrote this query while doing data migration from AX 2012 R3 to D365F&O.

SELECT inventdimcombination.itemid               [ITEMNUMBER],
       ecoresproduct.displayproductnumber        [PRODUCTVARIANTNUMBER],
       inventdim.inventstyleid                   [PRODUCTSTYLEID],
       inventdim.inventcolorid                   [PRODUCTCOLORID],
       inventdim.inventsizeid                    [PRODUCTSIZEID],
       inventdim.configid                        [PRODUCTCONFIGURATIONID],
       Isnull(ecoresproducttranslation.NAME, '') [PRODUCTNAME],
       inventitemgroupitem.itemgroupid           [ITEMGROUP]
FROM   inventdimcombination
       INNER JOIN inventdim
               ON inventdim.inventdimid = inventdimcombination.inventdimid
       INNER JOIN inventtable
               ON inventtable.itemid = inventdimcombination.itemid
       INNER JOIN ecoresproduct
               ON ecoresproduct.recid =
                  inventdimcombination.distinctproductvariant
       INNER JOIN ecoresproducttranslation
               ON ecoresproducttranslation.product = ecoresproduct.recid
       INNER JOIN inventitemgroupitem
               ON inventitemgroupitem.itemid = inventtable.itemid
                  AND inventitemgroupitem.itemdataareaid =
                      inventtable.dataareaid
WHERE  inventtable.dataareaid = '@DataAreaID'