web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Check out the Community Spotlight Video Series
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Sql View to X++ View
Microsoft Dynamics AX (Archived)

Sql View to X++ View

(0) ShareShare
ReportReport
Posted on by Vladislav 159

Hi everyone,
is it possible to deploy existing sql view in Ax 2012 R3 like a view. The sql query(view) is very big and if I have to do it manually it will take a lot of time. Is there another way?

Thank you.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 240,538 Most Valuable Professional on at

    No, it's not possible to import a SQL view to AOT.

  • Vladislav Profile Picture
    Vladislav 159 on at

    Thank you for your replay. Which is the best way to implement the view in Ax . If my SQL Query(view) is like this:

    CREATE view [dbo].[CC_ITEMS] as
SELECT
inv.RECID AS ItemRecID,
inv.ITEMID,
eco.NAME,
'' as PRODUCTDIVISIONID_HV,
'' as PRODUCTLINEID_HV,
   '' as PRODUCTFAMILYID_HV,
    '' asPRODUCTGROUPID_HV,
      '' as PRODUCTTYPEID_HV,
        '' as PRODUCTID_HV, 
       inv.PRODUCT,
          '170' as IS_RELEASEITEMDATAAREAID,
            itms.IS_SALESUNITID,
            itmi.IS_INVENTUNITID,
              '1' as ACTIVEINGREDIENTUNIT_HV,
                '1' as CONVERTFACTORKGA_HV,
                  t.CATEGORY,
                    t.RecID_1,
                     t.Name_Level1, 
                t.RecID_2,
                        t.Name_Level2,
                          t.RecID_3,
                           t.Name_Level3,
                            t.RecID_4,
                             t.Name_Level4,
                               t.RecID_5,
                                t.Name_Level5,
                                 '1' AS Concentration,
                                   'kg' AS Packaging,
                                     t1.ItemOwnerID_1, 
                           t1.ItemOwner_Level1,
                                       t1.ItemOwnerID_2,
                                         t1.ItemOwner_Level2

FROM            dbo.INVENTTABLE AS inv
LEFT OUTER JOIN
dbo.ECORESPRODUCT AS prod ON inv.PRODUCT = prod.RECID
LEFT OUTER JOIN
dbo.ECORESPRODUCTTRANSLATION AS eco ON inv.PRODUCT = eco.PRODUCT and eco.LANGUAGEID='it'
inner JOIN
                             (SELECT        cat.PRODUCT, cat.CATEGORY, e0.RECID AS RecID_1, e0.NAME AS Name_Level1, e1.RECID AS RecID_2, e1.NAME AS Name_Level2, e2.RECID AS RecID_3, e2.NAME AS Name_Level3, 
                                                         e3.RECID AS RecID_4, e3.NAME AS Name_Level4, e4.RECID AS RecID_5, e4.NAME AS Name_Level5
                               FROM            dbo.ECORESCATEGORY AS e0 LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e1 ON e0.RECID = e1.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e2 ON e1.RECID = e2.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e3 ON e2.RECID = e3.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e4 ON e3.RECID = e4.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESPRODUCTCATEGORY AS cat ON e4.RECID = cat.CATEGORY
                               WHERE        (e0.RECID = 5637146077)) AS t ON t.PRODUCT = prod.RECID
left outer JOIN
                             (SELECT        ec.PRODUCT, e0.RECID AS ItemOwnerID_1, e0.NAME AS ItemOwner_Level1, e1.RECID AS ItemOwnerID_2, e1.NAME AS ItemOwner_Level2
                               FROM            dbo.ECORESCATEGORY AS e0 LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e1 ON e0.RECID = e1.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESPRODUCTCATEGORY AS ec ON e1.RECID = ec.CATEGORY
                               WHERE        (e0.RECID = 5637146077)) AS t1 ON t1.PRODUCT = prod.RECID
Left outer join 
(select unitid as IS_salesunitid, ITEMID from INVENTTABLEMODULE where MODULETYPE = '002') as ITMS
on itms.ITEMID = inv.ITEMID 

Left outer join 
(select unitid as IS_inventunitid, ITEMID from INVENTTABLEMODULE where MODULETYPE = '002') as ITMI
on itmi.ITEMID = inv.ITEMID 

WHERE        (inv.DATAAREAID = '170') 
--AND (eco.LANGUAGEID = 'en-us')


  • Martin Dráb Profile Picture
    Martin Dráb 240,538 Most Valuable Professional on at

    I'm not sure what you want to hear...

  • Suggested answer
    Mahmoud Hakim Profile Picture
    Mahmoud Hakim 17,887 on at

    you must redevelop this view in X++, there is no tool available to convert SQL view to X++ view

  • Suggested answer
    Catalyst619 Profile Picture
    Catalyst619 355 on at

    As others have suggested, you have to go into the AOT or use X++ to define your view, relationships, etc.

  • Vladislav Profile Picture
    Vladislav 159 on at

    I decided to do Job who execute query, but I can not implement it correctly. 

    (SELECT        cat.PRODUCT, cat.CATEGORY, e0.RECID AS RecID_1, e0.NAME AS Name_Level1, e1.RECID AS RecID_2, e1.NAME AS Name_Level2, e2.RECID AS RecID_3, e2.NAME AS Name_Level3, 
                                                         e3.RECID AS RecID_4, e3.NAME AS Name_Level4, e4.RECID AS RecID_5, e4.NAME AS Name_Level5
                               FROM              dbo.ECORESCATEGORY AS e0 LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e1 ON e0.RECID = e1.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e2 ON e1.RECID = e2.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e3 ON e2.RECID = e3.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESCATEGORY AS e4 ON e3.RECID = e4.PARENTCATEGORY LEFT OUTER JOIN
                                                         dbo.ECORESPRODUCTCATEGORY AS cat ON e4.RECID = cat.CATEGORY
                               WHERE        (e0.RECID = 5637146077))


    This is small piece of code above. And this is my job:

    static void CC_Items(Args _args)
{
    Query       query;
    QueryRun    queryrun;
    QueryBuildDataSource    qbds1;
    QueryBuildDataSource    qbds2;
    QueryBuildDataSource    qbds3;
    
    QueryBuildRange         qbr1;
    QueryBuildRange         qbr2;
    
    EcoResCategory ecoResCategory;
    
    ;
    query   = new query();
    qbds1   =   query.addDataSource(tablenum(EcoResCategory));
    //qbds3 = qbds1.addDataSource(tableNum(EcoResCategory));
    //qbds3.relations(false);
    //qbds3.joinMode(JoinMode::OuterJoin);
    //qbds3.addLink(fieldNum(ecoResCategory,RecId),fieldNum(ecoResCategory,ParentCategory));
    
    
    qbds2   = qbds1.addDataSource(tablenum(EcoResProductCategory));
    qbds2.relations(false);
    qbds2.joinMode(joinmode::OuterJoin);
    qbds2.addLink(fieldnum(EcoResCategory,RecId),fieldnum(EcoResProductCategory,Category));
    qbr2 = qbds2.addRange(fieldNum(EcoResCategory,RecId));    
    qbr2.value(queryValue('5637146077'));
    
    
    queryrun    = new queryrun(query);   
    
    
}

    When I executed it the result is not the same.This is result:(NAME

    SELECT * FROM EcoResProductCategory(EcoResProductCategory_1) WHERE EcoResCategory.RecId = EcoResProductCategory.Category AND ((RecId = 5637146077))

    _4C0439044B043A04350438044904_.jpg

    Can any one tell me how to select some field not all(with *) and "AS" . example (e0.RECID AS RecID_1);

    And how can make this , because it points to the same table: 

    dbo.ECORESCATEGORY AS e0 LEFT OUTER JOIN
dbo.ECORESCATEGORY AS e1 ON e0.RECID = e1.PARENTCATEGORY LEFT OUTER JOIN
dbo.ECORESCATEGORY AS e2 ON e1.RECID = e2.PARENTCATEGORY LEFT OUTER JOIN
dbo.ECORESCATEGORY AS e3 ON e2.RECID = e3.PARENTCATEGORY LEFT OUTER JOIN
dbo.ECORESCATEGORY AS e4 ON e3.RECID = e4.PARENTCATEGORY LEFT OUTER JOIN
  • Martin Dráb Profile Picture
    Martin Dráb 240,538 Most Valuable Professional on at

    Use addSelectionField() method to select fields.

    Regarding the other question, simply add several data sources with the same table.

    By the way, use more descriptive names than qbds1, qbr2 and so on, otherwise you'll quickly get lost in your own code.

  • Vladislav Profile Picture
    Vladislav 159 on at

    Okei , but how to make AS operator

    AXSecond.JPG

    Please tell me:

    static void test(Args _args)
{  
        
    
    Query       query;
    QueryRun    queryrun;
    QueryBuildDataSource    qbdsEcoResCategory0;
    QueryBuildDataSource    qbdsEcoResCategory1;
    QueryBuildDataSource    qbdsEcoResCategory2;
    QueryBuildDataSource    qbdsEcoResCategory3;
    QueryBuildDataSource    qbdsEcoResCategory4;
    QueryBuildDataSource    qbdsEcoResProductCategory;
    
    
    QueryBuildRange         qbr1;
    QueryBuildRange         qbr2;
    EcoResCategory ecoResCategory;
    int elementsCount = 0;
    
    
    ;
    query   = new query();
    
    
    qbdsEcoResCategory0   =   query.addDataSource(tablenum(EcoResCategory));
    //EcoResCategory0 select statment
    qbdsEcoResCategory0.addSelectionField(fieldNum(EcoResCategory,RecId));
    qbdsEcoResCategory0.addSelectionField(fieldNum(ecoResCategory,Name));
    qbdsEcoResCategory0.addRange(fieldNum(EcoResCategory,RecId)).value(strFmt("5637146077"));
    
    
    //EcoResCategory1
    qbdsEcoResCategory1   = qbdsEcoResCategory0.addDataSource(tablenum(EcoResCategory));
    qbdsEcoResCategory1.relations(false);
    qbdsEcoResCategory1.JoinMode(JoinMode::OuterJoin);    
    qbdsEcoResCategory1.addLink(fieldNum(EcoResCategory,RecId),fieldNum(EcoResCategory,ParentCategory));
    qbdsEcoResCategory1.addSelectionField(fieldNum(EcoResCategory,RecId));
    qbdsEcoResCategory1.addSelectionField(fieldNum(EcoResCategory,Name));
    
     //EcoResCategory2
    qbdsEcoResCategory2   = qbdsEcoResCategory1.addDataSource(tablenum(EcoResCategory));
    qbdsEcoResCategory2.relations(false);
    qbdsEcoResCategory2.JoinMode(JoinMode::OuterJoin);    
    qbdsEcoResCategory2.addLink(fieldNum(EcoResCategory,RecId),fieldNum(EcoResCategory,ParentCategory));
    qbdsEcoResCategory2.addSelectionField(fieldNum(EcoResCategory,RecId));
    qbdsEcoResCategory2.addSelectionField(fieldNum(EcoResCategory,Name));
    
     //EcoResCategory3
    qbdsEcoResCategory3   = qbdsEcoResCategory2.addDataSource(tablenum(EcoResCategory));
    qbdsEcoResCategory3.relations(false);
    qbdsEcoResCategory3.JoinMode(JoinMode::OuterJoin);    
    qbdsEcoResCategory3.addLink(fieldNum(EcoResCategory,RecId),fieldNum(EcoResCategory,ParentCategory));
    qbdsEcoResCategory3.addSelectionField(fieldNum(EcoResCategory,RecId));
    qbdsEcoResCategory3.addSelectionField(fieldNum(EcoResCategory,Name));
    
     //EcoResCategory4
    qbdsEcoResCategory4   = qbdsEcoResCategory3.addDataSource(tablenum(EcoResCategory));
    qbdsEcoResCategory4.relations(false);
    qbdsEcoResCategory4.JoinMode(JoinMode::OuterJoin);    
    qbdsEcoResCategory4.addLink(fieldNum(EcoResCategory,RecId),fieldNum(EcoResCategory,ParentCategory));
    qbdsEcoResCategory4.addSelectionField(fieldNum(EcoResCategory,RecId));
    qbdsEcoResCategory4.addSelectionField(fieldNum(EcoResCategory,Name));
    
    qbdsEcoResProductCategory   = qbdsEcoResCategory4.addDataSource(tablenum(EcoResProductCategory));
    qbdsEcoResProductCategory.relations(false);
    qbdsEcoResProductCategory.JoinMode(JoinMode::OuterJoin);    
    qbdsEcoResProductCategory.addLink(fieldNum(EcoResCategory,RecId),fieldNum(EcoResProductCategory,Category));
    qbdsEcoResProductCategory.addSelectionField(fieldNum(EcoResProductCategory,Product));
    qbdsEcoResProductCategory.addSelectionField(fieldNum(EcoResProductCategory,Category));
    
   
    
    queryrun    = new queryrun(query);
    
    while (queryrun.next())
    {
        elementsCount++;
       
        // Showing results.
        info( strFmt ('%1' , elementsCount));
    }
}

    AXSecond.JPG

    these are two identical things, but how to wrap the query in brackets and then make the  ON statment

    Thank you.

  • Martin Dráb Profile Picture
    Martin Dráb 240,538 Most Valuable Professional on at

    AX doesn't support allow you to set names in SQL code, but it's not a problem, because:

    1. It has no impact on functionality
    2. You don't edit SQL code, so you don't really care about which names AX uses in the generated SQL code under the hood. You work with names in AOT and/or X++.
  • Vladislav Profile Picture
    Vladislav 159 on at

    Well I edited post, what do you think about wrapping? See the SQL query above and give me some advice on how to make wrapping, because this was a small piece of the SQL query

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the Community Spotlight Video Series

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans