Problem with calculated fields

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
balfons
Veteran Member
Posts: 91
Joined: 2018-10-22 15:27

Problem with calculated fields

Post by balfons » 2020-04-13 18:47

hi,

I'm using a calculated field in order to show the number of children a record has.

I have 2 tables:

- Parent: f_cont_improv_nonconformities
- Child: f_cont_improv_remedial_action

I'm using this query:

SELECT COUNT(*) FROM `f_cont_improv_nonconformities`
LEFT JOIN `f_cont_improv_remedial_action` ON `f_cont_improv_remedial_action`.`nonconformity_id`=`f_cont_improv_nonconformities`.`ID`
WHERE `f_cont_improv_nonconformities`.`ID`='%ID%'

the result is that when the parent has one or more children the query drops the correct result but... when there is no children... the query drops always the result: 1.

Any idea?

balfons
Veteran Member
Posts: 91
Joined: 2018-10-22 15:27

Re: Problem with calculated fields

Post by balfons » 2020-04-15 10:18

Hi,

I've been investigating a llitle bit.

My query always return a joined record where the part coming from the child table includes the ID with the null value.

I'm not an SQL expert but I think this join shouldn't include these cases.

So, I've modified my quey including the prevention for null values

SELECT COUNT(*) FROM `f_cont_improv_nonconformities`
LEFT JOIN `f_cont_improv_remedial_action` ON `f_cont_improv_remedial_action`.`nonconformity_id`=`f_cont_improv_nonconformities`.`ID`
WHERE `f_cont_improv_nonconformities`.`ID`='%ID%'
AND `f_cont_improv_remedial_action`.`ID` IS NOT NULL

Now it works as expected.

Thanks

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Problem with calculated fields

Post by jsetzer » 2020-04-15 11:35

Maybe you should use an INNER JOIN instead of a Left Join?
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

balfons
Veteran Member
Posts: 91
Joined: 2018-10-22 15:27

Re: Problem with calculated fields

Post by balfons » 2020-04-15 18:01

I'll try!!

Thanks

balfons
Veteran Member
Posts: 91
Joined: 2018-10-22 15:27

Re: Problem with calculated fields

Post by balfons » 2020-04-16 17:56

It works perfectly

Thanks!!

Post Reply