while select with join firstOnly

Question

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.

Answer 1

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