SBX - Search With Button

SBX - Forum Post Title

selecting record from a table that does not exist in other two tables(notexists join)

Microsoft Dynamics AX Forum

AX Dev asked a question on 13 Jun 2018 1:04 PM

Question Status

Suggested Answer

Hi All,

I have  2 tables header and line each having a field x now I have to compare it with table t having field x , if the header has line we need to compare header.x   == t.x else line.x== t.x. I need to filter records where the value is present in only header I.e it has no line records and no record in table t (header.recid == line.refrecid). I was trying two not exists join but that does not work in ax

while select header 

notexists join line where line.refrecid == header.recid

notexists join t where t.x == header.x

{}

Can someone suggest an alternative?

Thanks,

Reply
Crispin John Augustine responded on 14 Jun 2018 4:55 AM
My Badges
Suggested Answer

try separating them into two blocks:

while select header 

notexists join line where line.refrecid == header.recid

{

select firstOnly t where t.x == header.x

if (!t.RecId)

{

//Your Code here

}

}

Reply
Shah_ responded on 14 Jun 2018 7:48 AM
My Badges
Suggested Answer

It needs to be written in two seperate blocks.

Reply
Crispin John Augustine responded on 14 Jun 2018 4:55 AM
My Badges
Suggested Answer

try separating them into two blocks:

while select header 

notexists join line where line.refrecid == header.recid

{

select firstOnly t where t.x == header.x

if (!t.RecId)

{

//Your Code here

}

}

Reply
Shah_ responded on 14 Jun 2018 7:48 AM
My Badges
Suggested Answer

It needs to be written in two seperate blocks.

Reply

SBX - Two Col Forum

SBX - Migrated JS