AppGini Community Forums

Hello
I am looking to build this complex calculation, please any help.
Thank you

DEPOSIT
• IDDEPOSIT
• AMOUNT
• NET TO PAY
In NET TO PAY I want this calculation
• if amount is from 1 to 500? Add 6
• if amount is from 501 to 1000 add 7.50
• if amount is from 1001 to 2000 add 15

As a starting point check out...
https://www.w3schools.com/sql/sql_case.asp

"add 6" to which value?

Hi
Finaly got help from AppGini WhatsApp group.

SELECT

CASE
WHEN `deposit`.`amount` BETWEEN 1 AND 500 then (`amount` + 6 )
WHEN `deposit`.`amount` BETWEEN 501 AND 1000 then (`amount` + 7.5 )
WHEN `deposit`.`amount` BETWEEN 1000 AND 2000 then (`amount` + 15 )
ELSE 'check' end

FROM `deposit`

WHERE `deposit`.`id` = '%ID%'

Thank you

(1) It seems the target field is non-numeric because you put 'check' inside.

From my experience you should avoid mixing up numeric values with strings in the same column at any cost. A column should be numeric (for calculations) or non-numeric. Of course it is possible to do so from technical point of view.

For marking deposit records as invalid, perhaps it would be better to add a status column (which can also be calculated) for indicating invalid rows.

If the column was numeric, the following SQL statement would be shorter AND would return numeric values only. As a result you'd be able to use the results directly for follow up calculations without casting strings to numbers, first. This should be more efficient and result in more consistent data:
(2) I still cannot see the field name of this calculated field. I guess you don't, but just in case you try to update `deposit`.`amount` field itself by using a calulation evaluating `deposit`.`amount`, obviously the value will be increased repeatedly whenever you open a record in table view or in detail view unless you append another condition to the SQL statement like 'AND NOT is_calculated'

target field is decimal and read only, I use AppGini calculation

Just a note for saving you extra time and trouble: combination of your model and the SQL code you are using will return unexpected results for the following scenarios:

• > 2000

Depending on the type of montant_envoye the following scenarios will also bring unexpected results:

• < 1
  for example 0, 0.5, 0.99, -10, ...
• Between 500 and 501
  for example for 500.25 (500.0000001 to 500.9999999)

Problems I see:

• Your case statement does not cover every possible value. There may be gaps when using signed type or decimal/float/double types, because then there may be negative values (for example by invalid user input) or decimal values like 500.5
• your case ">2000" returns string although this is a decimal field

I suggest you should check your formula by testing against the following values:

• -1
• 0
• 499.99
• 500
• 500.5
• 501
• 999.99
• 1000
• 1999.99
• 2000
• 2000.01
• 2001

Testing against values near the upper and lower borders and nearby the thresholds is recommended. You can be sure your business logic works, as soon as your formula returns results matching your expectations.

Hi sir, thank you for your help and advices

Best regards,