Using abs function in join statement in X++ does not work

Question

I want to get related BOMVersion of Prodtable records. There is a method in prod table named bomversion :

 public BOMVersion bomVersion()
    {
        BOMVersion bomVersion;

        // need to remove active from the query
        select firstonly bomVersion
            order by ItemId, Active, FromQty desc
            where bomVersion.ItemId         == this.ItemId          &&
                  bomVersion.bomId          == this.bomId           &&
                  bomVersion.FromQty        <= abs(this.QtySched)   &&
                  bomVersion.FromDate       <= this.bomDate         &&
                  (bomVersion.ToDate        >= this.bomDate         ||
                   ! bomVersion.ToDate
                  );

        return bomVersion;
    }

In other class, I use bom version method logic to know first prodtable record which has a BOMVersion. This is my code:

select firstonly * from prodTable
             join bomVersion
            order by ItemId, Active, FromQty desc
            where
                  bomVersion.ItemId       == prodTable.ItemId           &&
                  bomVersion.bomId          == prodTable.bomId          &&
                  bomVersion.FromQty        <= abs(prodTable.QtySched)  && // abs does not work
                  bomVersion.FromDate       <= prodTable.bomDate        &&
                  (bomVersion.ToDate        >= prodTable.bomDate
                    || !bomVersion.ToDate);         

As I debugged I understood that this code returns no RecId. After commenting one by one where conditions, I noticed that the issue is related to abs method. when I remove abs method form the statement (I mean replace the condition with bomVersion.FromQty <= prodTable.QtySched ), record is found. But when I put abs no record is found. I should mention that I try this query in SQL server and in the two cases i.e. using abs and without abs, It finds records. Actually I can't understand what is the reason. Because this abs method is used in select statement in Prodtable bomversion method. Why this problem is not exist there?

Answer 1

You can use X++ functions in select clauses but they are evaluated once before the trip to the SQL server. This is not what you want here.

In this case you can unroll the abs function:

select firstonly prodTable
    join bomVersion
    order by ItemId, Active, FromQty desc
    where bomVersion.ItemId         == prodTable.ItemId         &&
          bomVersion.bomId          == prodTable.bomId          &&
        ((bomVersion.FromQty        <= prodTable.QtySched       && prodTable.QtySched >= 0) ||
         (bomVersion.FromQty        <=-prodTable.QtySched       && prodTable.QtySched <  0))                  
          bomVersion.FromDate       <= prodTable.bomDate        &&
         (bomVersion.ToDate         >= prodTable.bomDate        || !bomVersion.ToDate);         

Do you ever plan a negative production?

Another way to do is to make a view with a computed field. In the compute field function you may apply any SQL function available.