[Solved] Query - Adding two equations for third column

Discuss the database features

[Solved] Query - Adding two equations for third column

Postby StormWolf » Tue Jan 26, 2021 7:59 am

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.
Last edited by StormWolf on Sat Jan 30, 2021 6:09 am, edited 3 times in total.
Open Office 4.1.3 Using Windows 10
StormWolf
 
Posts: 11
Joined: Sun Jul 08, 2018 12:46 pm

Re: Query - Adding two equations together for third column

Postby StormWolf » Sat Jan 30, 2021 1:02 am

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.
Open Office 4.1.3 Using Windows 10
StormWolf
 
Posts: 11
Joined: Sun Jul 08, 2018 12:46 pm

Re: Query - Adding two equations together for third column

Postby StormWolf » Sat Jan 30, 2021 1:22 am

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.
Open Office 4.1.3 Using Windows 10
StormWolf
 
Posts: 11
Joined: Sun Jul 08, 2018 12:46 pm

Re: Query - Adding two equations together for third column

Postby StormWolf » Sat Jan 30, 2021 1:54 am

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.
Open Office 4.1.3 Using Windows 10
StormWolf
 
Posts: 11
Joined: Sun Jul 08, 2018 12:46 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests