Pages

Friday, July 11, 2008

Sample union query from AX 2009

Queries build with the Query classes now supports unions, meaning that you can combine the result from several tables into one result set. The results you want to combine from the different tables must be structured the same way for all tables.

You could for example create a query combining CustTable and VendTable. This would be particularly useful if you need to present for example a lookup form showing both customers and vendors in the same grid. In earlier version you’d have to push customer and vendor data to a temporary table before being able to present the combined data in one grid.

Here is an example on how to build and use a union query from X++:

static void union(Args _args)
{
Query query;
QueryBuildDataSource qbdsCustTable;
QueryBuildDataSource qbdsVendTable;
QueryRun queryRun;
CustTable custVendTable;
Map mapTableBranches = new Map(types::Integer, typeId2Type(typeId(TableId)));
SysDictTable dictTable;
;



// The map is used to match the UnionBranchID with a table id
mapTableBranches.insert(1, tableNum(CustTable));
mapTableBranches.insert(2, tableNum(VendTable));


query = new Query();
query.queryType(QueryType::Union);


qbdsCustTable = query.addDataSource(tableNum(CustTable));
qbdsCustTable.unionType(UnionType::UnionAll); // Include duplicate records
qbdsCustTable.fields().dynamic(false);
qbdsCustTable.fields().clearFieldList();
qbdsCustTable.fields().addField(fieldNum(CustTable, AccountNum));
qbdsCustTable.fields().addField(fieldNum(CustTable, Name));


qbdsVendTable = query.addDataSource(tableNum(Vendtable));
qbdsVendTable.unionType(UnionType::UnionAll); // Include duplicate records
qbdsVendTable.fields().dynamic(false);
qbdsVendTable.fields().clearFieldList();
qbdsVendTable.fields().addField(fieldNum(VendTable, AccountNum));
qbdsVendTable.fields().addField(fieldNum(VendTable, Name));


queryRun = new QueryRun(query);
queryRun.prompt();

while (queryRun.next())
{
custVendTable = queryRun.getNo(1);
dictTable = SysDictTable::newTableId(mapTableBranches.lookup(custVendTable.unionAllBranchId));

info (strFmt("%1 %2 (%3)", custVendTable.AccountNum,
custVendTable.Name,
dictTable.name()));
}
}

7 comments:

Sørens said...

Thanks for the post - does the unionallbranchid field have any relation to this?

Palle Agermark said...

The UnionAllBranchId gives you the actual table id of the data behind the current record.

So if you have a union between CustTable and VendTable, you should look at the UnionAllBranchId field to figure out if the current record is a customer or a vendor.

Siju said...

When I used queryRun.promt() in AX 9 , In the query window , it is selected 'Previously Used Query' in Select Query field insted of 'Query used'. But in AX4 it coming 'Query Used' . How to make it in AX9 the same as AX4.

Sergio Sepúlveda Montealegre said...

Thanks a lot for the post.
I have a question.
I'm using your example with the salesTable, but I need use the same table twice in the same query using queryType setted in Union. Obviously both datasources with differents ranges filters.
When I use my query and call it from lookup control I receive the message that datasources have different quantity of columns...what could be happen?

Palle Agermark said...

Hmm. Are you sure you have the same fields in both datasources of the query?

marya yumi said...

Hai i have a question about the union query can u translate thi sql statement into AX union query.

I want to count empid from column
emplid + empl2 + empl3 + empl4 + emp5 in 1 transdate with no duplicate emp name.

For example if i do the sql statement it will like below statement :

SELECT count(EMPID)
FROM
(
Select EMPLID AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL2 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL3 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL4 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL5 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'
)A



can this been done in AX? Your feedback will be appreciate. Thanks

marya yumi said...

Hai i have a question about the union query can u translate thi sql statement into AX union query.

I want to count empid from column
emplid + empl2 + empl3 + empl4 + emp5 in 1 transdate with no duplicate emp name.

For example if i do the sql statement it will like below statement :

SELECT count(EMPID)
FROM
(
Select EMPLID AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL2 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL3 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL4 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'

UNION

Select EMPL5 AS EMPID
FROM MSB_JobCardView
where ((TRANSDATE BETWEEN '2011-06-01 00:00:00.000' AND '2011-06-01 00:00:00.000' AND FromTime >= 28800)
OR (TRANSDATE BETWEEN '2011-06-02 00:00:00.000' AND '2011-06-02 00:00:00.000' AND ToTime < 28800))
and DATAAreaid = 'NJM'
And MSB_SectionId = 'HP'
And MSB_Group = 1
And oprid <> 'DOWNTIME'
)A



can this been done in AX? Your feedback will be appreciate. Thanks