CODEWITHSUNDEEP
axaptamicrosoft-dynamicsx++dynamics-ax-2009
Olaru Mircea
Olaru Mircea

Reputation: 2620

while select with join firstOnly

Is there any chance i could join two tables like this ? 

while select  SalesId from salesTable
    //group by SalesId
    where salesTable.SalesId == "xxx006932683"
    join firstOnly SalesPrice, ItemId, LineNum from salesLine
    //group by SalesId
    order by salesLine.LineDisc asc, salesLine.SalesPrice desc 
    where salesLine.SalesId == salesTable.SalesId
{
    info(strFmt("Sales id : %1 line %2 item %3 price %4", salesLine.SalesId, salesLine.LineNum, salesLine.ItemId, salesLine.SalesPrice));
}

So, for each line in SalesTable, join it with the only one line in SalesLine with the same SalesId and satisfying the order condition.

To be honest, i have tried a lot of groupings and orderings and maxOfs, minOfs with no success... so here i am asking for an idea.

Upvotes: 1

Views: 9731

Answers (2)

Olaru Mircea
Olaru Mircea

Reputation: 2620

I have to admit this is the most strangest query I have written so far : 

SalesQuotationTable salesQuotationTable;
SalesQuotationLine  salesQuotationLine;

CustQuotationSalesLink    custQuotationSalesLink;
CustQuotationJour         custQuotationJour;
;  

while select maxof(QuotationId), maxof(CurrencyCode) from salesQuotationTable
    group by QuotationId, CurrencyCode, RecId
         where salesQuotationTable.QuotationStatus    == SalesQuotationStatus::Sent
            && salesQuotationTable.QuotationType      == QuotationType::Sales
          //&& salesQuotationTable.QuotationId        == '00015683_042' just for testing

join maxof(lineNum), minof(lineAmount), maxof(QuotationId) from salesQuotationLine
    group by lineNum, lineAmount, QuotationId, RecId
         where salesQuotationLine.QuotationId         == salesQuotationTable.QuotationId
            && salesQuotationLine.QuotationStatus     == SalesQuotationStatus::Sent
            && salesQuotationLine.QuotationType       == QuotationType::Sales
            && salesQuotationLine.SalesQty            > 0

//duplicate values were coming from here, grouping was the way to go
join maxof(QuotationDate), maxof(QuotationId) from custQuotationSalesLink
    group by OrigQuotationId
         where custQuotationSalesLink.OrigQuotationId == salesQuotationTable.QuotationId

join maxof(QuotationDate) from custQuotationJour
         order by custQuotationJour.QuotationId
             where custQuotationJour.QuotationId      == custQuotationSalesLink.QuotationId
                && custQuotationJour.QuotationDate    == custQuotationSalesLink.QuotationDate

A few notes:

1. Instead of 

select firstonly custQuotationSalesLink
     order by QuotationDate desc, QuotationId desc
        where custQuotationSalesLink.OrigQuotationId == this.QuotationId

I have used 

join maxof(QuotationDate), maxof(QuotationId) from custQuotationSalesLink
     group by OrigQuotationId
         where custQuotationSalesLink.OrigQuotationId == salesQuotationTable.QuotationId

The party starts here, from what i have seen, once using a group by, all the fields from the other tables seem to be empty. So the solution is to add group by everywhere.

You see i am adding the RecId into groupings to be sure i am not really grouping anything :)

In order to get fields with values you have to add an aggregate function in the select clause. Ok , cool, why not, as long as i am not grouping for real.

2. But the catch for me was at the last part: 

join maxof(QuotationDate) from custQuotationJour
    order by custQuotationJour.QuotationId
          where custQuotationJour.QuotationId == custQuotationSalesLink.QuotationId
                 && custQuotationJour.QuotationDate == custQuotationSalesLink.QuotationDate

That order by did the trick. I don t know why. If i switch it with a group by which seems normal to me, i get duplicated values. So all the groupings added before are just losing their relevance. I guess sometimes a bit of luck has to join the game too. I mean, why thinking at order by there. Maybe because it s Wednesday, i don t know.

I had to use some aggregation on the last join because otherwise i wouldn t have got a value for the QuotationDate field which in fact is the whole goal of this work.

This link helped me a lot :

http://axatluegisdorf.blogspot.ca/2010/07/select-group-by-and-join-order-by.html

Upvotes: 0

Anže Krpič
Anže Krpič

Reputation: 265

You can not do that in one select statement. First you can create a view on sales line with grouping on SalesId and maxOf, minOf, ... on fields you need. This view should return only one record for each SalesId. Than you can join this view to sales table.

If you only want to get first line of order then you have to do nested selects. Best way is to create a temp table with fields you need and fill it with data.

while select SalesId from salesTable
{
   select firstOnly SalesPrice, ItemId, LineNum from salesLine
      order by salesLine.LineDisc asc, salesLine.SalesPrice desc 
      where salesLine.SalesId == salesTable.SalesId
   ;

   //insert into temp table

   info(strFmt("Sales id : %1 line %2 item %3 price %4", salesLine.SalesId, salesLine.LineNum, salesLine.ItemId, salesLine.SalesPrice));
}

But in your case (because you have where statement on SalesId <- unique) this will work fine

select firstOnly SalesPrice, ItemId, LineNum from salesLine
   order by salesLine.LineDisc asc, salesLine.SalesPrice desc 
   where salesLine.SalesId == "xxx006932683";

Upvotes: 1

Related Questions