web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Microsoft Dynamics AX (Archived)

Left join "WHERE <COLUMN> NOT NULL" in AOT

(0) ShareShare
ReportReport
Posted on by

Hi,

  So I want to do this in the AOT (AX 7):

SELECT IVT.ITEMID, IVBC.ITEMBARCODE

FROM INVENTITEM IVT

LEFT OUTER JOIN INVENTITEMBARCODE IVBC

ON IVT.ITEMID = IVBC.ITEMID

WHERE

IVBC.ITEMID IS NOT NULL

OR IVT.ITEMID = N'1234'

So what I want is to filter out the values in INVENTITEMBARCODE when they don't exist... except in certain cases.

I have read several blogs about Joins and Join types. I'm thinking that there is some way to use Exist Joins to do this but I cannot find any good examples.

Thanks in Advance.

*This post is locked for comments

I have the same question (0)
  • Mea_ Profile Picture
    60,284 on at
    RE: Left join "WHERE <COLUMN> NOT NULL" in AOT

    Hi Michael Morin,

    You can use QueryFilter for this msdn.microsoft.com/.../gg881181.aspx but it works only for x++ query, i'm not sure what do you mean under "AOT".

  • Verified answer
    Martin Dráb Profile Picture
    236,394 Most Valuable Professional on at
    RE: Left join "WHERE <COLUMN> NOT NULL" in AOT

    An alternative solution is using an union query consisting of two parts:

    • inner join, excluding ItemId = '1234'
    • outer join, only for ItemId = '1234'
  • Michael Morin Profile Picture
    on at
    RE: Left join "WHERE <COLUMN> NOT NULL" in AOT

    I am building some entities. I believe Martin's solution will work.

  • Michael Morin Profile Picture
    on at
    RE: Left join "WHERE <COLUMN> NOT NULL" in AOT

    Thanks Martin. That would work.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans