Finance | Project Operations, Human Resources, ...

Create Range by condation

(0) Share

Report

Posted on by XPRESS

how Create Range by Condation

exp

select * from Table where A<> (B+ C)

how add range by SysQuery::findOrCreateRange(dataSource,fieldNum(Table ,A))?

thank you

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Martin Dráb 239,040 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

You'll need to use expressions in query ranges.

Was this reply helpful? Yes No
XPRESS 58 on at

Like (0)

Report
Copy link

Link copied!

thank you
Martin Dráb

How can a condition be made between two fields like this query?

where A <> (B+ C)

Thanks

Was this reply helpful? Yes No
André Arnaud de Cal... 303,730 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi XPress,

I do assume you need to achieve this with x++ coding. Is this about a decimal value? You can first put the sum of the two values in a new variable. Then you can use x++ statements like the next example.

Amount sumBC = B + C; select * from Table where A != sumBC;

Was this reply helpful? Yes No
XPRESS 58 on at

Like (0)

Report
Copy link

Link copied!

hi dir Andre Arnaud de C...

q=SysQuery::findOrCreateRange(dataSource,fieldNum(Table ,A));

q.value('A != B+C');

Can the previous condition be fulfilled?

thank you

Was this reply helpful? Yes No
André Arnaud de Cal... 303,730 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hi XPress,

I think to start understanding that B and C are also fields in the same table. Online calculations in queries are not supported. Like Martin shared, you can use some expressions, but no calculations.

Was this reply helpful? Yes No
XPRESS 58 on at

Like (0)

Report
Copy link

Link copied!

thank you
Martin Dráb

My problem is that the operation of plural or subtracting two columns is not performed because it treats the two columns as a text value and not a numerical value

qbr1.addRange(fieldNum(CustTable, AccountNum)).value(
strFmt('((%1.%2 == (%3 + %2))',
qbr1.name(),
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, CreditMax)));

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

Like (1)

Report
Copy link

Link copied!

What SQL code does it generate?

Note that your code doesn't match your requirements. You wanted A != B+C, but you have A == B + A.

Try prefixing all field names with %1..

Another approach is creating a view with a computed column. You can do the whole calculation there and you're get a NoYes fields that is trivial to filter by.

Was this reply helpful? Yes No
XPRESS 58 on at

Like (0)

Report
Copy link

Link copied!

Thank you all

Martin Dráb

Andre Arnaud de C...

My understanding is that the math in filtering doesn't work

Was this reply helpful? Yes No