Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / SQL query working in s...
Finance forum
Answered

SQL query working in sql but not getting records in X++

Posted on by 1,457
I have a sql query which is working as required in sql ,but if i write same query in d365 x++ class in while loop it doesnot gives any output , can anyone help me on this .below is my sql query 
select DTCustomerDetailsTable.SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DTCustomerDetailsTable.DATAAREAID from  DTCustomerDetailsTablejoin salestable on salestable.INTERCOMPANYCOMPANYID = DTCustomerDetailsTable.DATAAREAIDwhere  DTCustomerDetailsTable.SALESID = 'SO01-10001732'group by DTCustomerDetailsTable.SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DTCustomerDetailsTable.DATAAREAID
and here is my while query which is not getting inside loop , 
 while select SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,                   DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId from  dTCustomerDetailsTable                   group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,                   DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId                    join salestable                   where  dTCustomerDetailsTable.SALESID == salestable.InterCompanyOriginalSalesId                    &&    dTCustomerDetailsTable.DataAreaId == companyId
 
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    SQL query working in sql but not getting records in X++
    hi ,martin 
    Thanks for reply , 
    i have implemented the steps as you suggested , the code in under testing now , i will definately let you know if there will be any thing else .
    thanks for helping , me out.
     
     
    thanks ,
    Regards ,
    Dinesh.
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    SQL query working in sql but not getting records in X++
    Well, you throw a lot of things together, so there is a lot that can be said.
     
    It might sounds unimportant, but I think you should start with paying attention to things like variable scope, indentation and letter casing. It'll make your code much easier to read than when things like indented or named randomly. They you should split your code to smaller pieces. When code is split to methods with descriptive names, it's much easier to work with, because you can focus on smaller individual pieces. It'll also help you with variable scope.
     
    Regarding your actual code, one problem is already in the very first select statement. It won't work correctly for invoice that contain multiple sales orders. The correct relation is through CustInvoiceSalesLink table.
     
    I wonder if you really want CustInvoiceJour.InterCompanyPurchId. Again, there may be just a single value even if the invoice contains multiple orders. Maybe you should use SalesTable.InterCompanyPurchId instead.
     
    I see you use purchLine.CustomerRef, but you neither aggregate or group by this it, therefore it'll always be empty.
     
    I wonder why you must group by all the fields when selecting from DTCustomerDetailsTable. Isn't it a bug in code or in the data model?
     
    You should also get used to checking whether there isn't already logic you're going to implement. For example, notice how I used custInvoiceJour.custTable_OrderAccount().name() in the code below. There may be cases where you'll rather make your own query, but productivity is often more valuable.
     
    You should try to reduce the number of nested while selects and use joins instead. It makes a huge difference in performance.
     
    You seem to be missing a logic for the situation when the query fails to find any purchTable/purchLine.
     
    Here is an example of some refactoring to make the code easier to read:
    public class YourClass
    {
        public void processOperation()
        {
            CustInvoiceJour custInvoiceJour;
            CustInvoiceSalesLine
            SalesTable salesTable;
        
            while select custInvoiceJour
                where custInvoiceJour.SalesId == 'sa01SO01-10001732'
                join InterCompanyOriginalSalesId, InterCompanyCompanyId, WorkerSalesResponsible from salesTable
                    where salesTable.InterCompanyPurchId == custInvoiceJour.InterCompanyPurchId
            {
                DTPaymentTrackingTbl paymentTracking;
                paymentTracking.initFromCustInvoiceJour(custInvoiceJour);
                paymentTracking.DTWorkerName = HcmWorker::find(salesTable.WorkerSalesResponsible).name();
               
                DTCustomerDetailsTable dTCustomerDetailsTable;
    
                while select crosscompany dTCustomerDetailsTable
                    group by SalesId, PackingSlipId, InvoiceId, InvoiceAmount, AmountNotSettled,
                         DTCustInvoicePaymType, DTInvoiceStatus, DataAreaId
                    where dTCustomerDetailsTable.DataAreaId == salesTable.InterCompanyCompanyId
                       && dTCustomerDetailsTable.SalesId == salesTable.InterCompanyOriginalSalesId
                {
                    paymentTracking.initFromDTCustomerDetailsTable(dTCustomerDetailsTable);
                    paymentTracking.DTEntityName = salesTable.InterCompanyCompanyId;
    
                    Purchtable  purchTable;
                    Purchline   purchLine;
    
                    select firstOnly crosscompany CustomerRef, sum(LineAmount) from purchLine
                        group by purchTable.PurchId, purchTable.PurchStatus, purchTable.IntercompanyCompanyId, purchTable.CreatedDateTime
                            where purchLine.DataAreaId == salesTable.InterCompanyCompanyId
                            join purchTable
                                where purchTable.PurchId == purchLine.PurchId
                                   && purchTable.PurchId ==  intPruchId;
    
                    paymentTracking.InterCompanyPurchId = purchTable.PurchId;
                    paymentTracking.PurchStatus         = purchTable.PurchStatus;
                    paymentTracking.DTDateTime          = purchTable.CreatedDateTime;
                    paymentTracking.CustomerRef         = purchLine.CustomerRef;
                    paymentTracking.LineAmount          = purchLine.LineAmount;
    
                    if (DTPaymentTrackingTbl::exist(
                        paymentTracking.SalesId,
                        paymentTracking.InvoiceId,
                        paymentTracking.PackingSlipId)
                    {
                        this.updateInvoicesDetails(paymentTracking);
                    }
                    else
                    {
                        paymentTracking.insert();
                    }
                }
            }
        }
    }
    
    public class DTPaymentTrackingTbl extends common
    {
        public void initFromCustInvoiceJour(CustInvoiceJour _custInvoiceJour)
        {
            this.InvoiceAccount = _custInvoiceJour.InvoiceAccount;
            this.Name           = _custInvoiceJour.custTable_OrderAccount().name();
        }
        
        public void initFromDTCustomerDetailsTable(DTCustomerDetailsTable _custDetails)
        {
            this.SalesId                = _custDetails.SalesId;
            this.PackingSlipId          = _custDetails.PackingSlipId;
            this.InvoiceId              = _custDetails.InvoiceId;
            this.InvoiceAmount          = _custDetails.InvoiceAmount;
            this.AmountNotSettled       = _custDetails.AmountNotSettled;
            this.DTCustInvoicePaymtType = _custDetails.DTCustInvoicePaymtType;
            this.DTInvoiceStatus        = _custDetails.DTInvoiceStatus;
        }
        
        public static boolean exist(...)
        {
            ...
        }
    }
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    SQL query working in sql but not getting records in X++
    hi ,Martin ,
    thanks for reply ,
    i have used cross company in my query now i am getting desired results , below is my code , you can suggest any corrections if i need to do.
    public void processOperation()
        {
            CustInvoiceJour            custInvoiceJour,custInvoiceJourloc;
            SALESTABLE                 salestable;
            DTCustomerDetailsTable     dTCustomerDetailsTable;
            Purchtable                 purchTable;
            Purchline                  purchLine;
            Custtable                  custtable;
            DirPartyTable              dirPartyTable;
            HCMWORKER                  HcmWorker;
            DTPaymentTrackingTbl       dTPaymentTrackingTbl,dTPaymentTrackingTblUpdate;
            InterCompanyPurchId        intPruchId;
            CustInvoiceId              invoiceid;
            InterCompanyCompanyId      intercompanyId;
            //SalesId                  salesId;
            str                        salesId;
    
            while select INVOICEACCOUNT,SALESID,INTERCOMPANYPURCHID,InvoiceId,InterCompanyCompanyId from custInvoiceJour
                where custInvoiceJour.SalesId == 'sa01SO01-10001732'
           {
               intPruchId = custInvoiceJour.InterCompanyPurchId;
               invoiceid  = custInvoiceJour.InvoiceId;
    
               dTPaymentTrackingTbl.InvoiceAccount  = custInvoiceJour.InvoiceAccount;
    
               select firstonly name from dirPartyTable
                   join custtable
                          where custtable.PARTY == dirPartyTable.RecId
                   join custInvoiceJourloc
                          where custtable.AccountNum == custInvoiceJourloc.OrderAccount
                             && custInvoiceJourloc.InvoiceId == invoiceid;
                   
               dTPaymentTrackingTbl.Name = dirPartyTable.Name;
    
                while  select InterCompanyOriginalSalesId,WORKERSALESRESPONSIBLE,InterCompanyCompanyId from salestable
                   where salestable.INTERCOMPANYPURCHID == intPruchId
               {
                   intercompanyId = salestable.InterCompanyCompanyId;
                    salesId  = salestable.InterCompanyOriginalSalesId;
    
                   select Name from dirPartyTable
                            join hcmWorker
                                    where hcmWorker.Person == dirPartyTable.RecId
                            join salestable 
                                    where  salestable.WorkerSalesResponsible == hcmWorker.RecId
                             && salestable.InterCompanyPurchId == intPruchId;
    
                   dTPaymentTrackingTbl.DTWorkerName =  dirPartyTable.Name;
                    
                 while select crosscompany SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
                       DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId from dTCustomerDetailsTable
                       group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
                       DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
                        where dTCustomerDetailsTable.DataAreaId == intercompanyId
                        join salestable
                        where  dTCustomerDetailsTable.SALESID == salesId
                 {
                        
                        dTPaymentTrackingTbl.SalesId          = dTCustomerDetailsTable.SalesId;
                        dTPaymentTrackingTbl.PackingSlipId    = dTCustomerDetailsTable.PackingSlipId;
                        dTPaymentTrackingTbl.InvoiceId        = dTCustomerDetailsTable.InvoiceId;
                        dTPaymentTrackingTbl.InvoiceAmount    = dTCustomerDetailsTable.InvoiceAmount;
                        dTPaymentTrackingTbl.AmountNotSettled = dTCustomerDetailsTable.AmountNotSettled;
                        dTPaymentTrackingTbl.DTCustInvoicePaymtType = dTCustomerDetailsTable.DTCustInvoicePaymtType;
                        dTPaymentTrackingTbl.DTInvoiceStatus  = dTCustomerDetailsTable.DTInvoiceStatus;
                        dTPaymentTrackingTbl.DTEntityName       = intercompanyId;
    
                     select crosscompany CustomerRef,sum(LineAmount) from purchLine
                         where purchLine.DataAreaId == intercompanyId
                         join * from  purchTable
                         group by purchTable.PURCHID,purchTable.PURCHSTATUS,purchTable.INTERCOMPANYCOMPANYID,purchTable.CREATEDDATETIME
                         where purchLine.PURCHID == purchTable.PURCHID
                         && purchTable.PURCHID ==  intPruchId;
    
                        dTPaymentTrackingTbl.InterCompanyPurchId = purchTable.PurchId;
                        dTPaymentTrackingTbl.PurchStatus         = purchTable.PurchStatus;
                        dTPaymentTrackingTbl.DTDateTime          = purchTable.CREATEDDATETIME;
                        dTPaymentTrackingTbl.CustomerRef         = purchLine.CustomerRef;
                        dTPaymentTrackingTbl.LineAmount          = purchLine.LineAmount;
    
                        select firstonly dTPaymentTrackingTblUpdate
                    where dTPaymentTrackingTblUpdate.SalesId == dTPaymentTrackingTbl.SalesId
                    && dTPaymentTrackingTblUpdate.InvoiceId == dTPaymentTrackingTbl.InvoiceId
                    && dTPaymentTrackingTblUpdate.PackingSlipId == dTPaymentTrackingTbl.PackingSlipId;
                        if(dTPaymentTrackingTblUpdate.RecId != 0)
                        {
                            this.UpdateInvoicesDetails(dTPaymentTrackingTbl);      
                        }
                        else
                        {
                            dTPaymentTrackingTbl.insert();
                        
                        }
    
                      
                 }
    
    
               }
    
           }
    
        }
     
     
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    SQL query working in sql but not getting records in X++
    Yes, this query is guaranteed to return no data, because no record can have DataAreaId equal to both sa09 and sa01 at the same time. I pointed out to this bug already in my first reply.
     
    You need to use a crossCompany query (or wrap it in changeCompany block) to fetch data from another company.
     
    The crossCompany keyword is used in select statements, such as while select crossCompany...
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    SQL query working in sql but not getting records in X++
    ho martin i am getting this query in sql which is getting blank data 
    SELECT T1.SALESID,T1.PACKINGSLIPID,T1.INVOICEID,T1.INVOICEAMOUNT,T1.AMOUNTNOTSETTLED,T1.DTCUSTINVOICEPAYMTTYPE,T1.DTINVOICESTATUS 
    FROM DTCUSTOMERDETAILSTABLE T1 CROSS JOIN SALESTABLE T2 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'sa09')) AND (T1.DATAAREAID=N'sa01'))
    AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'sa09')) AND (T1.SALESID=N'SO01-10001732')) 
    GROUP BY T1.SALESID,T1.PACKINGSLIPID,T1.INVOICEID,T1.INVOICEAMOUNT,T1.AMOUNTNOTSETTLED,T1.DTCUSTINVOICEPAYMTTYPE,T1.DTINVOICESTATUS,T1.DATAAREAID
    ORDER BY T1.SALESID,T1.PACKINGSLIPID,T1.INVOICEID,T1.INVOICEAMOUNT,T1.AMOUNTNOTSETTLED,T1.DTCUSTINVOICEPAYMTTYPE,T1.DTINVOICESTATUS,T1.DATAAREAID
    when i check with my select query in x++
         
       select generateOnly forceLiterals  dTCustomerDetailsTable
                       group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
                       DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
                        where dTCustomerDetailsTable.DataAreaId == companyId
                        join salestable
                        where  dTCustomerDetailsTable.SALESID == salesId;
                  info(dTCustomerDetailsTable.getSQLStatement());
    how can i get the correct data i currently working on sa09 get the data from sa01, plz guide me on this where should is need to put cross company in code . 
     
    thanks,
    Regards,
    Dinesh
  • Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    SQL query working in sql but not getting records in X++
    If you want to run code in the context of another company, you can use changeCompany() block.
     
    If you want to run a query across company, you can use crossCompany keyword in your select statement. That gives you data for all companies by default, but you can restrict it to just selected companies if needed.
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    SQL query working in sql but not getting records in X++
    hi martin actually i was trying to create a batch job which brings the intercompany data , the code works fine for the sa09 entity , but when it gets the data using while query above from sa01 entity the data it exits from while loop i dont have the idea of fetching the data between two legal entities , can u plz help me on this , below is my code .
    class DTPaymentTrackingBatchService extends SysOperationServiceBase
    {
        public void processOperation()
        {
            CustInvoiceJour            custInvoiceJour,custInvoiceJourloc;
            SALESTABLE                 salestable;
            DTCustomerDetailsTable     dTCustomerDetailsTable;
            Purchtable                 purchTable;
            Purchline                  purchLine;
            Custtable                  custtable;
            DirPartyTable              dirPartyTable;
            HCMWORKER                  HcmWorker;
            DTPaymentTrackingTbl       dTPaymentTrackingTbl;
            InterCompanyPurchId        intPruchId;
            CustInvoiceId              invoiceid;
            InterCompanyCompanyId      companyId;
            SalesId                    salesId;
    
            while select INVOICEACCOUNT,SALESID,INTERCOMPANYPURCHID,InvoiceId,InterCompanyCompanyId from custInvoiceJour
                where custInvoiceJour.SalesId == 'sa01SO01-10001732'
           {
               intPruchId = custInvoiceJour.InterCompanyPurchId;
               invoiceid  = custInvoiceJour.InvoiceId;
    
               dTPaymentTrackingTbl.InvoiceAccount  = custInvoiceJour.InvoiceAccount;
    
               select firstonly name from dirPartyTable
                   join custtable
                          where custtable.PARTY == dirPartyTable.RecId
                   join custInvoiceJourloc
                          where custtable.AccountNum == custInvoiceJourloc.OrderAccount
                             && custInvoiceJourloc.InvoiceId == invoiceid;
                   
                       dTPaymentTrackingTbl.Name = dirPartyTable.Name;
    
                while  select InterCompanyOriginalSalesId,WORKERSALESRESPONSIBLE,InterCompanyCompanyId from salestable
                   where salestable.INTERCOMPANYPURCHID == 'PO01-10002188'
               {
                   companyId = salestable.InterCompanyCompanyId;
                   = salestable.InterCompanyOriginalSalesId;
    
                   select Name from dirPartyTable
                            join hcmWorker
                                    where hcmWorker.Person == dirPartyTable.RecId
                            join salestable 
                                    where  salestable.WorkerSalesResponsible == hcmWorker.RecId
                             && salestable.InterCompanyPurchId == 'PO01-10002188';
    
                   dTPaymentTrackingTbl.DTWorkerName =  dirPartyTable.Name;
                    
                    while select CrossCompany SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
                       DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId from  dTCustomerDetailsTable
                       group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,
                       DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
                        join salestable
                       where  dTCustomerDetailsTable.SALESID == salestable.InterCompanyOriginalSalesId
                        &&    dTCustomerDetailsTable.DataAreaId == companyId
                 {
    
                        dTPaymentTrackingTbl.SalesId          = dTCustomerDetailsTable.SalesId;
                        dTPaymentTrackingTbl.PackingSlipId    = dTCustomerDetailsTable.PackingSlipId;
                        dTPaymentTrackingTbl.InvoiceId        = dTCustomerDetailsTable.InvoiceId;
                        dTPaymentTrackingTbl.InvoiceAmount    = dTCustomerDetailsTable.InvoiceAmount;
                        dTPaymentTrackingTbl.AmountNotSettled = dTCustomerDetailsTable.AmountNotSettled;
                        dTPaymentTrackingTbl.DTCustInvoicePaymtType = dTCustomerDetailsTable.DTCustInvoicePaymtType;
                        dTPaymentTrackingTbl.DTInvoiceStatus  = dTCustomerDetailsTable.DTInvoiceStatus;
    
                        
                     select CustomerRef,sum(LineAmount) from purchLine
                         join * from  purchTable
                         group by purchTable.PURCHID,purchTable.PURCHSTATUS,purchTable.INTERCOMPANYCOMPANYID,purchTable.CREATEDDATETIME
                         where purchLine.PURCHID == purchTable.PURCHID
                         && purchTable.PURCHID == 'PO01-10002188';
    
                        dTPaymentTrackingTbl.InterCompanyPurchId = purchTable.PurchId;
                        dTPaymentTrackingTbl.PurchStatus         = purchTable.PurchStatus;
                        dTPaymentTrackingTbl.DTDateTime          = purchTable.CREATEDDATETIME;
                        dTPaymentTrackingTbl.CustomerRef         = purchLine.CustomerRef;
                        dTPaymentTrackingTbl.LineAmount          = purchLine.LineAmount;
                        dTPaymentTrackingTbl.insert();
    
                 }
    
               }
           }
        }
    
    }
    thanks ,
    Regards,
    Dinesh
  • Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    sql query working in sql but not getting records in d365 x++
    The queries are not the same at all. Different filters, different join conditions... It would be a surprise if they happen to return the same data.
     
    I also see you're filtering by DataAreaId (in X++), but you aren't using a cross-company query.
     
    Note that you can get SQL query generated from your X++ code, which you can then compare with your other SQL query:
    select generateOnly forceLiterals dTCustomerDetailsTable
        group by SALESID,PACKINGSLIPID,INVOICEID,INVOICEAMOUNT,AMOUNTNOTSETTLED,DTCUSTINVOICEPAYMTTYPE,DTINVOICESTATUS,DataAreaId
        where dTCustomerDetailsTable.DataAreaId == companyId
        join salesTable
            where salesTable.InterCompanyOriginalSalesId == dTCustomerDetailsTable.SalesId;
    
    info(dTCustomerDetailsTable.getSQLStatement());

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

News and Announcements

Give Back to the Community this Month

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,807 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,135 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans