157
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
The usual approach is using relations, defined either on tables or inside AOT queries. When using the Query framework in X++, you can utilize table relations and if none exists, you can use addLink().
Please look into AX documentation to learn more about table relations; they're very important (other functionality, such as automatically generated lookup forms, depend on relations too).
AX doesn't support allow you to set names in SQL code, but it's not a problem, because:
Okei , but how to make AS operator
Please tell me:
Share
Report
All responses (
Answers (
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));
}
}
these are two identical things, but how to wrap the query in brackets and then make the ON statment
Thank you.
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.
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))
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
As others have suggested, you have to go into the AOT or use X++ to define your view, relationships, etc.
you must redevelop this view in X++, there is no tool available to convert SQL view to X++ view
I'm not sure what you want to hear...
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')
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,432
Most Valuable Professional
#3
nmaenpaa
101,156
