Finance | Project Operations, Human Resources, ...

Answered

AOT query doesn't support full outer join

(0) Share

Report

Posted on by Bhagyashree Dave

133

Hi all,

I was working on a form which has AOT query as a datasource. Query has some data sources like,

PurchReqTable

PurchReqLine

PurchLine

PurchTable

VendPackingSlipTrans

VendPackingSlipJour

all tables are joined with outer join. As per my knowledge, this outer join is works as left outer join(correct me if I'm wrong).

So the requirement is, I have to show all the data in all these tables(that's why I used outer join). But the data from purchTable are not coming properly- that means POs that are not created from Purchase requisitions(created separately), are not available in the report. I wrote same query in SQL using left outer join, and it shows same data as the report. But when I use full outer join in SQL, it shows the data that I want in my report.

So what should I change/add in my AOT query to make it work like full outer join?

I have the same question (0)

All responses (7)

Answers (1)

Nya 29,060 on at

Like (0)

Report

Hi,

Would you please specify which application you're using in Dynamics?

I'll move it to the proper forum for more help.

Was this reply helpful? Yes No
Bhagyashree Dave 133 on at

Like (0)

Report

it's dynamics d365 f&o

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

Like (0)

Report

Hi Bhagyashree,

You can't do that in one query, you could try to write two while loops for these two different conditions.

And you can use notexists join to get the data not from PR.

while select PO

notexists join PR

where PO.PurchId== PR.PurchId

Was this reply helpful? Yes No
Bhagyashree Dave 133 on at

Like (0)

Report

Hi, thanks for your reply.

but there is no code in this report, just the query.

So I can't use while select.

Also I need data of all PR, all PO and all the Product Receipts. So I can't use not exists join on PR also.

hope you understand what I'm saying.

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

Like (1)

Report

Hi Bhagyashree,

I think you should develop a new reports for that requirement instead of modifying the original report. You could create a query by code. How about using Union query?

Was this reply helpful? Yes No
Bhagyashree Dave 133 on at

Like (0)

Report

Thanks for your quick reply.

So, you mean to say I can't do anything with this existing query, I have to write x++ code for this requirement. Won't it make the report slower?

Was this reply helpful? Yes No
Verified answer

WillWU 22,361 on at

Like (1)

Report

Hi Bhagyashree,

As far as I know, yes, and you can try to create a new union query based on the existing query.

Was this reply helpful? Yes No

Join the conversation

Community site session details

AOT query doesn't support full outer join

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

AOT query doesn't support full outer join

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates