web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / writing SQL statement ...
Microsoft Dynamics AX (Archived)

writing SQL statement based on Ax Query 2012

(0) ShareShare
ReportReport
Posted on by jihane 1,264

Hello Communtiy, 

i want to convert the following x++ statement  to sql:

static void UpdateCustTransDescription(Args _args)

{
    CustInvoiceLine     custInvoiceLine;
    CustInvoiceTable    custInvoiceTable;
    CustInvoiceJour     custinvoiceJour;
    CustTrans           custTrans;
    ttsBegin;
    while select forUpdate custTrans
        where !custTrans.Txt && custTrans.RecId == 5637355326
        join  custInvoiceJour
            where   custinvoiceJour.InvoiceId == custTrans.Invoice
               &&  custinvoiceJour.InvoiceAccount == custTrans.AccountNum
               &&  custinvoiceJour.InvoiceDate == custTrans.TransDate
               &&  custinvoiceJour.LedgerVoucher == custTrans.Voucher
                  join custInvoiceTable
                     where custInvoiceTable.InvoiceId == custinvoiceJour.InvoiceId
                         join    custInvoiceLine
                           where   custInvoiceLine.ParentRecId == custInvoiceTable.RecId
    {
        custTrans.Txt   =   custInvoiceLine.Description;
        custTrans.doUpdate();
       
    }
    ttsCommit;
}

 

Might you please advising me how i can achieve this? how i should proceed?
Is there any best practices that i should be aware of in order to improve the performance of this statement?

P.S:

I don't wanna use "generateOnly forceLiterals" as the generated query looks little bit too complicated to execute.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    You already have the exact query that AX uses. If you don't like it, either refactor it or write another (functionally equivalent) query by hand, if you believe it's a good use of your time.

    My recommendation is not wasting much time with it. If you want to optimize queries, look at execution plans, not at whether you like the SQL code.

  • J Barry Profile Picture
    J Barry 10 on at

    If a free text invoice had more that one row in CustInvoiceLine, couldn't that retrieve the wrong CustInvoiceLine.Description value?  That's a problem I'm running into.  I'm actually linking to CustInvoiceTrans and I get the same value for both rows.  Are either the LineNum or SourceDocumentLine fields guaranteed to give me the correct result?

  • Verified answer
    Israel Gonzalez Profile Picture
    Israel Gonzalez 733 on at

    Hello jihane,

    This stored procedure will help you:

    USE [MicrosoftDynamicsAx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateCustTransDescription]
	 @CustTransRecId		BIGINT
AS
BEGIN	
	DECLARE @Description	NVARCHAR(300)
	DECLARE	@RecId		BIGINT	
	
	BEGIN TRY	

		DECLARE CursorCustTrans CURSOR LOCAL FOR	
						
		SELECT	 CIL.[DESCRIPTION]
			,CTR.RECID
		FROM	CUSTTRANS		AS CTR		
		INNER	JOIN CUSTINVOICEJOUR	AS CIJ
		ON	CIJ.INVOICEID		= CTR.INVOICE
		AND	CIJ.INVOICEACCOUNT	= CTR.ACCOUNTNUM
		AND	CIJ.INVOICEDATE		= CTR.TRANSDATE
		AND	CIJ.LEDGERVOUCHER	= CTR.VOUCHER
		INNER	JOIN CUSTINVOICETABLE	AS CIT
		ON	CIT.INVOICEID		= CIJ.INVOICEID
		INNER	JOIN CUSTINVOICELINE	AS CIL
		ON	CIL.PARENTRECID		= CIT.RECID
		WHERE	CTR.TXT				<> ''
		AND	(CTR.RECID		= @CustTransRecId OR @CustTransRecId = 0) --Warning: Send 0 for update all records of CustTrans		
				
		OPEN CursorCustTrans; 
		FETCH CursorCustTrans INTO @Description, @RecId
			
		WHILE @@FETCH_STATUS = 0
		BEGIN 	
			BEGIN TRAN	

			UPDATE	CUSTTRANS
			SET	TXT	= @Description						
			WHERE	RECID	= @RecId
				
			COMMIT TRAN	
			
			FETCH CursorCustTrans INTO @Description, @RecId
		END

		CLOSE CursorCustTrans;
		DEALLOCATE CursorCustTrans;
			
	END TRY
	BEGIN CATCH
		SELECT	 ERROR_NUMBER()		AS NumberError  
			,ERROR_SEVERITY()	AS SeverityError  
			,ERROR_STATE()		AS StateError  
			,ERROR_PROCEDURE()	AS ProcedureError  
			,ERROR_LINE()		AS LineError  
			,ERROR_MESSAGE()	AS MessageError
	END CATCH

END



  • Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    Please note that the question was asked three years ago...

  • Israel Gonzalez Profile Picture
    Israel Gonzalez 733 on at

    Yeah, you're right, I don´t know why forum show me this post on header

  • Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    It's because J Barry responded to it yesterday.

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

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans