Microsoft Dynamics AX (Archived)

Exists Join in Select statement

(0) Share

Report

Posted on by Rohin

4,624

Hi,

I am trying to run below code :

static void Job15(Args _args)
{
    salesTable   salesTable;
    SalesLine    salesLine;
    ;
    
    while select * from salesTable 
        exists join salesLine where salesTable.SalesId == salesLine.SalesId
    {
        info(strFmt("%1",salesLine.ItemId));
    }
    

}

But it giving me blank result and when i remove exists keyword , expected result is came.

Please comment on this why it doesn't work for exists

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (4)

Verified answer

Vilmos Kintera 46,149 on at

Like (1)

Report

RE: Exists Join in Select statement

Inner join (just having join) is fetching record values for both tables. Exists join just filters the parent as if it would be an inner join, but does not return buffer values for the child table.

Was this reply helpful? Yes No
Rohin 4,624 on at

Like (0)

Report

RE: Exists Join in Select statement

but why it works for inner Join (if i removed exists join)...?

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Exists Join in Select statement

I am not sure what do you mean. If you have sales lines for sales order headers, in one scenario it would info out sales ids, in the other scenario it would not (since exists does not do a fetch for the values). Should enter into the loop for both cases.

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report
RE: Exists Join in Select statement

This works for me perfectly fine.

SalesTable salesTable; SalesLine salesLine; while select firstOnly10 SalesId from salesTable exists join SalesId from salesLine where salesTable.SalesId == salesLine.SalesId { info(salesTable.SalesId); } while select firstOnly10 SalesId from salesTable group by salesTable.SalesId
join SalesId from salesLine where salesTable.SalesId == salesLine.SalesId { info(salesTable.SalesId); } }

I am grouping by SalesId in the second script, so it does not return repeated SalesId information due to the inner join.

You should actually specify what exactly are you trying to do. Making the SalesLine an exists join and expecting a return value of ItemId does not make sense, since the SalesLine buffer would be empty...

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Exists Join in Select statement

Or if you want to return sales lines in both cases, why don't you make that as the parent, and inner/exists join the SalesTable then?

Was this reply helpful? Yes No
Rohin 4,624 on at

Like (0)

Report

RE: Exists Join in Select statement

Perfect Vilmos!! so what i got from all of above your suggestion is that : "Exist join only filter/fetch from Parent Table not joined/child table ...right? please confirm

Was this reply helpful? Yes No
Suggested answer

Mahmoud Hakim 17,887 on at

Like (0)

Report

RE: Exists Join in Select statement

you can use inner join to avoid this issue , and make relation with custaccount and dataareaid also

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: Exists Join in Select statement

Yes, Visvash, it's called "exists join" because it only checks if a related record exists; it doesn't fetch any data.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Exists Join in Select statement

Please mark the helpful answers to resolve the thread.

Was this reply helpful? Yes No
Suggested answer

Kanagaraj A 1,254 on at

Like (0)

Report

RE: Exists Join in Select statement

Yes, Exists Join fetch records from parent table only.

Was this reply helpful? Yes No