How can I enter a conditional calculation in LO Base/Query

I would like to create a new column in my query that does a conditional calculation across several variables in the table used for the query. If it is easier or more direct, I could also do this in the table within the database.

A simple example of what I am trying to do would have a TestTable with the entries of:
TableID, Primary Key, AutoValue
Value1, Value2, and Value 3 all Integer (Long)
Within the test table, there are 7 entries, with Value1, Value2 and Value3 ranging from 1 to 9 and if no entry was made, it shows up as a 0.

I would like to calculate the number of non-zero entries across the variables Value1, Value2 and Value3 for every case and enter that in a new column.

I tried to add a formula to the table, but haven’t discovered how to do that, so I turned to Query and find that if I go in GUI I can add my test table and in Field I can simply type in Value1/Value1 and that becomes a new column with a value of 1 if the entry is something other than 0, or 0, if it was a 0. This seemed promising, for I could then create 2 more columns Value2/Value2 and Value3/Value3. Then I could Write in an Alias for each one and create 4th column Alias1+Alias2 + Alias3 and if all 3 values were non-zero, then I would get a 3, but if any one was a 0, I would get a 0, so this didn’t give me the values I wanted.

In investigating SQL options, it looked like the CASE option could do what I wanted. Something like
SELECT TableID, Value1, Value2, Value3,
N1 = CASE
WHEN Value1>0 THEN 1 ELSE 0
END
FROM TestTable;

This would have to be repeated for each value and then, if the 0’s created this way were really 0’s that could be part of another calculation, then N1+N2+N3 would yield the value I want. But I can’t even get past a single SQL entry this way without the same Syntax error.

Any Idea on how to do this simple calculation?

Larry

What about Alias1 * Alias2 * Alias3 in 4th column?

Thanks for you suggestion. When I try this idea, if Value1 is 5, Value2 is 4 and Value3 is 0 (meaning not entered), then if Alias1 is 1, Alias2 is 1 and Alias3 is 0 and the result of multiplying them together is 0. What I want is number of valid entries, which would be 2 in this example. Any other ideas?

Hi,
Here is a HSQL statement that might do for you…!

I am attaching a PDF to show you output of this HSQL … Hope it helps.

SELECT “ID”, “Val_1”, “Val_2”, “Val_3”,
“Val_1” + “Val_2” + “Val_3” AS “RESULT”,
CASEWHEN( ( “Val_1” + “Val_2” + “Val_3” ) > 0, ‘Yes’, ‘No’ ) AS “Logic_YesOrNo”
FROM “TableName”

SORRY DUE TO LOW KARMA, I COULDN’T UPLOAD FILE…SORRY!

Thanks for this idea. When I enter this HSQL statement, I get the error “Undefined function CASEWHEN in expression.” I tried CASE WHEN and =CASE WHEN and =CASEWHEN and they get syntax errors. The Value of “Val_1”+“Val_2”+“Val_3” yields a correct sum, but when these values are 4,3 and 0, the result is 7. What I want is to count the number of valid entries where 4 and 3 are valid entries and 0 is not, so the result I want is the number 2. Any other ideas?

Try this function - NVL

NVL( "Value1" / "Value1", 0 ) + NVL( "Value2" / "Value2", 0 ) + NVL( "Value3" / "Value3", 0 )

Thanks for this idea. It also results in the error "Undefined function NVL in expression. I can see where you are going with this - would there be a way that when the calculation of “Value1”/“Value1” is done that it does redefine the null value as a Zero instead of a Null Value? I’ll do some more research in that direction - again thanks for your idea.

Yes, that is what I had in mind. Any arithmetic operation with a NULL value will give the result NULL of the entire expression. In other databases function NVL can be called IFNULL or ISNULL

If I use IFNULL, I get the error that it is an undefined function. If I use ISNULL, I get an error "Wrong number of arguments used with function in query expression ISNULL(Value1_01,0), where Value1_01 is the Alias I assigned to the division of Value1 by itself. I can use ISNULL with the original division and assignment of Alias, e.g. ISNULL(Value1/Value1, 0) AS Value1_01` and I get the same error. It looks like ISNULL is a valid function which is great. Also went to http://technet.microsoft.com/en-us/library/ms184325.aspx and it looks like the way I entered it is right. What other arguments might be needed? Any thoughts?

I have discovered the answer to this question and it is in the use of IIF statement. As noted above, I have a table called TestFormula withe TestID, Value1, Value2 and Value3 as the fields. Creating the table in Query I write:

SELECT TestID, Value1, Value2, Value3, IIF(Value1 > 0, 1, 0) + IIF(Value2 > 0, 1, 0) + IIF(Value3 > 0, 1, 0) AS SUM FROM TestFormula;

The new field with the alias of SUM counts across the fields of Value1, Value2 and Value3 and yields the number of non-zero fields from 0 to 3.

This solution could also work if I tried to count non-null fields if I wrote IIF(Value1 IS NULL, 0, 1) etc. but I don’t have to go through the division I was originally doing which yielded the null values in the first place.

Thanks to those who provided suggestions, they ultimately led me to what feels like a pretty simple answer.
Larry