Query returning No Row

[Cédric Templie]

Hello,

I find out the following behaviour.
Cannot really say if it's a bug or not, but it is not that straight forward.

Here is the case.

A table with 3 columns Area / Activity / Status.
Status column contains a percentage "as text" or NA : 0%, 25%... NA

Out of this table I am trying to compute a percentage of completion per Area excluding the NA (some Activities are not relevant).

I am using the following query (probably not the most efficient one, but might be treated in another topic probably)

SELECT Area, (Sum(Status) / (100 * COUNT(Activity)))*100 AS 'Completed', (1 - Sum(Status) / (100 * COUNT(Activity)))*100 AS 'Remaining'
FROM T*
WHERE Status >= 0
GROUP BY Area

If the first non-empty row of my input table contains the NA value for the Activity, the query returns No Row.

Ex:

INPUT TABLE

AREA / Activity / Status
area1 / act1       /
area1 / act2      / NA
area1 / act3      / 10%
area1 / act4      / NA

Result : No row

Expected:

AREA / Completed / Remaining
area1 / 10 / 90

If, on the other hand the first non-empty row contains a value (in percentage), it works fine. Computing the completion percentage.

Ex:

INPUT TABLE

AREA / Activity / Status
area1 / act1       /
area1 / act2      / 100%
area1 / act3      / 10%
area1 / act4      / NA

Result:

AREA / Completed / Remaining
area1 / 55 / 45

Thank you for your help in advance

Regards

Cédric