I need help with outer join

(0) Share

Report

Posted on by source258147

1,025

Hello expert,

I need to make a relation between two tables HCMEMPLOYMENT and DefaultDimensionView

when I run this query in SQL server its return the exact result I want

select * from HCMEMPLOYMENT 
left join DefaultDimensionView on (DefaultDimensionView.DEFAULTDIMENSION = HCMEMPLOYMENT.DEFAULTDIMENSION)
where DefaultDimensionView.NAME = 'Department'
and DefaultDimensionView.DISPLAYVALUE >= '022'
and DefaultDimensionView.DISPLAYVALUE <= '022'
order by 1

but with this query below it returns all the data in the tables. I need to edit this query to bring the employees in the department 022

query = this.parmQuery();

qbdsEmployment = query.dataSourceTable(tableNum(HcmEmployment));

qbdsDimensions      = qbdsEmployment.addDataSource(tableNum(DefaultDimensionView));
qbdsDimensions.firstOnly(true);
qbdsDimensions.joinMode(JoinMode::OuterJoin);
qbdsDimensions.relations(false);
//qbdsDimensions.fetchMode(QueryFetchMode::One2One);
//qbdsDimensions.addLink(fieldNum(HcmEmployment,DefaultDimension),fieldNum(DefaultDimensionView,DefaultDimension));
qbdsDimensions.addLink(fieldNum(DefaultDimensionView,DefaultDimension),fieldNum(HcmEmployment,DefaultDimension));
qbdsDimensions.addRange(fieldNum(DefaultDimensionView,Name)).value(queryValue("Department"));
qbdsDimensions.addRange(fieldNum(DefaultDimensionView,DisplayValue))
              .value(queryRange(queryValue("022")
                     ,queryValue("022")));

I don't know why the filter is not working.

if I change joinmode from outer to exists the filter works and return employees in department 022

if i clear the filter it's return employees only have dimensions

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
An obvious problem is that you have the wrong order of arguments of addLink(). The first argument should be the field from the parent, therefore remove your current addLink() and uncomment the correct one.

qbdsDimensions.addLink(fieldNum(HcmEmployment,DefaultDimension), fieldNum(DefaultDimensionView, DefaultDimension));

A useful technique is checking what query you've generated (e.g. by using the debugger, or calling qbdsEmployment.toString()). Observing the results tells you whether it works or not, but not where your X code failed to generate what you intended.

By the way, firstOnly(true) doesn't work for joins.

Was this reply helpful? Yes No
WillWU 22,361 on at

Like (0)

Report

Hi source258147,

Please try to use class QueryFilter to filter the data.

And have a look at this blog:

https://community.dynamics.com/365/financeandoperations/b/daxmusings/posts/query-and-new-related-objects-in-ax-2012

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi,

What AX version do you operate?

Best regards,

Ludwig

Was this reply helpful? Yes No
source258147 1,025 on at

Like (0)

Report
Hello Martin

i update my query as you mention but I get the same result

i did debug on my code before i create the post and i don't figure out what is the problem with my code

this is the result for qbdsEmployment.toString())

SELECT * FROM HcmEmployment(HcmEmployment) OUTER JOIN * FROM DefaultDimensionView(DefaultDimensionView_1) ON HcmEmployment.DefaultDimension = DefaultDimensionView.DefaultDimension AND ((Name = N'Department')) AND ((DisplayValue>=N'022' AND DisplayValue<=N'022'))} Dynamics.AX.Application.QueryBuildDataSource

Was this reply helpful? Yes No
source258147 1,025 on at

Like (0)

Report

Hello Ludwig

this is my version details

Installed product version : Microsoft Dynamics 365 for Finance and Operations (10)

Installed platform version : Update24 (7.0.5179.35392)

Was this reply helpful? Yes No
source258147 1,025 on at

Like (0)

Report

Hello Will

i try it but same result

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Why do you want use an outer join instead of an exists join?

If you want to return HcmEmployment records that has the given dimension value, an exists join is exactly what you need and the outer join is not.

Was this reply helpful? Yes No
source258147 1,025 on at

Like (0)

Report

there are two secnario

one --> if the user pass the value to the parameter and want to filter data depend on the parameter

two --> if the user want to get all the employees with out any filter

you mean

if(parameter)

exist join dimensionTable

else

just use hcmemployment

is that what you mean ?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (1)

Report
I didn't meant anything like that, because you never mentioned such a requirement.

But the solution should be easy - simply disable the data source if you don't want the filter:

qbdsDimensions.enabled(parameter);

Was this reply helpful? Yes No
source258147 1,025 on at

Like (0)

Report

i solve the issue by adding my query after check parameter

if(parameter)

{

do join

filter data
}

Was this reply helpful? Yes No