Finance | Project Operations, Human Resources, ...

Variable declaration for select query X++

(1) Share

Report

Posted on by Voltes

199

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.

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

MuthukumaranAX 2,903 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

Layan Jwei 8,165 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

A more efficient approach would be using a single query with group by SalesStatus.

Was this reply helpful? Yes No
Raj Borad 1,524 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Layan Jwei 8,165 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Community member,

Is your issue solved? If yes please verify the answers that helped.

Was this reply helpful? Yes No