Page 1 of 1

[Solved] Query - Adding two equations for third column

PostPosted: Tue Jan 26, 2021 7:59 am
by StormWolf
Hi All.

This seems an easy fix, but alas. . .

I'm setting up a db with OO Base 4.1.8 and windows 10. Going great until I had the bright idea of adding Ratios. Two columns, one for Bat, one for Bowl. The problem is trying to add the two together in a third column in the query. The two ratios are added manually for each entry as they will be different on each occasion they are used.

For Bat column
( CASE WHEN "BatPos" > 6 THEN 0 ELSE 6 - "BatPos" END ) * "Multiplier" * "BatRat"

For Bowl Column
( CASE WHEN "BowlPos" > 6 THEN 0 ELSE 6 - "BowlPos" END ) * "Multiplier" * "BowlRat"

The Problem is trying to add both together. The result is either the total from only ONE column if only BatRat or BowlRat is used. Using both results in column full of 0. No error message though.

Without the BatRat or BowlRat the following works perfectly.
( CASE WHEN "BatPos" > 6 THEN 0 ELSE 6 - "BatPos" END ) + ( CASE WHEN "BowlPos" > 6 THEN 0 ELSE 6 - "BowlPos" END ) * "Multiplier"

Adding BatRat
( CASE WHEN "BatPos" > 6 THEN 0 ELSE 6 - "BatPos" END *"BatRat") + ( CASE WHEN "BowlPos" > 6 THEN 0 ELSE 6 - "BowlPos" END ) * "Multiplier"
and BowlRat
( CASE WHEN "BatPos" > 6 THEN 0 ELSE 6 - "BatPos" END ) + ( CASE WHEN "BowlPos" > 6 THEN 0 ELSE 6 - "BowlPos" END *"BowlRat") * "Multiplier"

The result of the two above in the third column "TtlPts" is the same as the original equations at the start. It doesnt add the two columns.

I've also tried it without the "Multiplier" but doesn't fix the problem

I've tried entering "BatRat" and "BowlRat" in different patterns but all bring up 0.
Am I meant to use a join here somewhere?

Help me Open Office, you're my only hope.

Re: Query - Adding two equations together for third column

PostPosted: Sat Jan 30, 2021 1:02 am
by StormWolf
Solved it.

The equation is two parts and i guess it didn't want to meld together. I ended up keeping the two separate and "SUM" each side.

(Sum(Case when "BatPos" >6 then 0 else 6 - "BatPos" end)*"Multiplier"*"BatRat")+(Sum(Case when "BowlPos" > 6 then 0 else 6 - "BowlPos" end)*"Multiplier"*"BowlRat")
then function box is not required and is now empty. Works.

Re: Query - Adding two equations together for third column

PostPosted: Sat Jan 30, 2021 1:22 am
by StormWolf
Not solved yet apparently. Now the new equation is getting there, but will only add the two sides if data is in both sides. If one side is 0 then the total is still 0. If Bat and Bowl column have a number greater than 0 each then it correctly adds both. With only one number in the two columns it enters 0 as total.

Re: Query - Adding two equations together for third column

PostPosted: Sat Jan 30, 2021 1:54 am
by StormWolf
Update time.
The 0 effect was because the default of BatRat and BowlRat is empty. I'm thinking the numbers crunched are multiplied by 0 and therefore end up as zero as the result.

BatRat and BowlRat default is now 1. Seems to have now resolved the problem. Damn default status.