Microsoft Dynamics AX (Archived)

convert SQL query to AX query

(0) Share

Report

Posted on by maram fraij

I need to convert the SQL query below into AX query

select * from PURCHRFQLINE a inner join PURCHRFQTABLE b on

a.RFQID=b.RFQID

inner join PURCHRFQREPLYLINE c

on c.RFQLINERECID=a.RECID

full outer join PURCHLINE d on d.Itemid=a.Itemid AND a.PURCHID = d.PURCHID

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Denis Macchinetti 16,444 on at

Like (0)

Report
Copy link

Link copied!

Hi Maram

AX query mean AOT query or using query objects like the examples on the link below ?

msdn.microsoft.com/.../aa638454.aspx

Regards

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report
Copy link

Link copied!

I need to create it firstly by code similar to the first example in the link for testing purposes then I will convert it to AOT query using the wizard

Was this reply helpful? Yes No
Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report
Copy link

Link copied!

Hi Maram

Could be something like this :

PURCHRFQLINE PURCHRFQLINE;

PURCHRFQTABLE PURCHRFQTABLE;

PURCHRFQREPLYLINE PURCHRFQREPLYLINE;

PURCHLINE PURCHLINE;

;

while select PURCHRFQLINE

join PURCHRFQTABLE

where PURCHRFQLINE.RFQId == PURCHRFQTABLE.RFQId

join PURCHRFQREPLYLINE

Where PURCHRFQREPLYLINE.RFQLINERECID == PURCHRFQLINE.RECID

outer join PURCHLINE

Where PURCHLINE.Itemid == PURCHRFQLINE.Itemid

&& PURCHRFQLINE.PURCHID == PURCHLINE.PURCHID

info (PURCHRFQLINE.RFQId);

Regards

Was this reply helpful? Yes No
Suggested answer

Dick Wenning 8,705 Moderator on at

Like (0)

Report
Copy link

Link copied!

please check

bmdax.blogspot.nl/.../joins-inner-outer-combined.html

there are some issues

Was this reply helpful? Yes No
refa on at

Like (0)

Report
Copy link

Link copied!

try to find an alternative query for FULL OUTER JOIN, combination of outer joins, unions

but i suppose it will not work in AX...

Was this reply helpful? Yes No
Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report
Copy link

Link copied!

Hi Guys

From my side, the above query work well.

I have also tested on my environment.

In this case we don't have a combination of inner join and outer join on the same source as write at the link bmdax.blogspot.nl/.../joins-inner-outer-combined.html

Regards

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report
Copy link

Link copied!

I need to build a query

Was this reply helpful? Yes No