Skip to main content
Dynamics 365 Community / Forums / Finance forum / Variable declaration f...
Finance forum
Suggested answer

Variable declaration for select query X++

editSubscribe (1) ShareShare
ReportReport
Posted on by 898
Hello everyone,
 
If I want to run few lines of select query in X++, but the table that I'm using is the same table. For illustration is something like this :
 
1. Run /select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == open order/
2. Run /select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == delivered/
3. Run /select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == invoiced/
 
At each step, I saved the amount result to my custom table's record. Do I need to declare 3 variables salesline and 3 variables salestable ? or 1 variable each table is enough ?
Btw, those 3 will be inside a while statement, which the /where clause/ actually needed to add some more from that where loop.
 
 
Thanks.
  • Layan Jwei Profile Picture
    Layan Jwei 2,789 Super User on at
    Variable declaration for select query X++
    Hi Community member,
     
    Is your issue solved? If yes please verify the answers that helped.
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,409 Super User on at
    Variable declaration for select query X++
    For code looks perspective you can add a method with parameter the sales id and type and simply called that which will return required value.
     
    In other cases, as all these status are from header and no 2 status can be there for one sales order you can simply create a SalesLine buffer inside while and based on condition like a switch case just write the code.
  • rajborad Profile Picture
    rajborad 94 on at
    Variable declaration for select query X++
    Hi,
     
    As per my knowledge, You can use one variable for all the query like,
     
    select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == open order/
    yourtable.insert();
     
    select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == delivered/
    yourtable.insert();
     
    select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == invoiced/
    yourtable.insert();
     
    OR
     
    If you want to use and it suitable your requirement, you can change the query and use the group by on SalesStatus of Sales Table.
     
    OR
     
    You can also create a different method for find the SalesAmount  from SalesLine based on the SalesStatus of SalesTable.
     
    Thank you,
    Raj Borad
  • Martin Dráb Profile Picture
    Martin Dráb 222,585 Super User on at
    Variable declaration for select query X++
    A more efficient approach would be using a single query with group by SalesStatus.
  • Suggested answer
    Layan Jwei Profile Picture
    Layan Jwei 2,789 Super User on at
    Variable declaration for select query X++
    Hi Community member,
     
    If after the first select statement, you saved the value in your new table and u don't want anything more from this select statement, then declaring one variable is enough. However, if for example, you want use the first variable after calling the 2nd select statement then you will need more than one variable to not lose the value.
     
    It's better to share with us the whole code.
     
    Thanks
    Layan Jweihan
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
  • Suggested answer
    MuthukumaranAX Profile Picture
    MuthukumaranAX 2,893 on at
    Variable declaration for select query X++
    If you want to retrieve the sales amount based on the statutes then yes it is better you have 3 different variables.
     
    OR 
     
    you can write a separate method with a sales status parameter to return sales amount. you can call this method as needed and use it.

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,066 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,585 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans