Join expected in select statement on X++

Question

I'm going mad over a query I need for my project

    purchTable = this.purchTable;
    if (purchTable)
    {            
        str intentLetterId_IT = purchTable.IntentLetterId_IT;           
        select intentLetter.RemainAmountMST() from intentLetter
            where intentLetter.IntentLetterId == intentLetterId_IT
               && intentLetter.RecId == purchTable.RecId;    
        if (intentLetter)
        {
            Amount remainAmountMST = intentLetter.RemainAmountMST();
            info(strFmt("RemainAmountMST: %1", remainAmountMST));
        }
    }

I keep receveing the error

Join Expected on the select statement

I tried something but I couldn't find the right way.

Answer 1

Either you use a join

select intentLetter.RemainAmountMST()
    join purchTable
    from intentLetter
        where intentLetter.IntentLetterId == intentLetterId_IT
           && intentLetter.RecId          == purchTable.RecId;

or you use a RecId type parameter for that purchTable.RecId like you did for intentLetterId_IT.

Answer 2

Your syntax is incorrect as X++ does not allow function expressions in the select. That is why you get a compile error.

Correct syntax is:

if (purchTable)
{        
    select firstonly intentLetter
         where intentLetter.IntentLetterId == purchTable.IntentLetterId_IT;   
    if (intentLetter)
    {
        info(strFmt("RemainAmountMST: %1", intentLetter.remainAmountMST()));
    }
}

Use select firstonly intentLetter which select all fields like SQL select * from intentLetter. Do not select on RecId as they cannot be expected to match.

Or the easy way out:

info(strFmt("RemainAmountMST: %1", IntentLetter_IT::find(purchTable.IntentLetterId_IT).remainAmountMST()));

This delagates the problem of selecting to the provided function find.