This content has been marked as final. Show 3 replies
>> If I use "ABS(estimate - actual) AS variance" in my query, I certainly can get rid of it, but then sorting does not work properly, since negative numbers are treated as positive
Please try the following:
The trick is to add to the column a hidden prefix with the real value of the column, and then concatenate it with the visible value. As the sort order is determine by the beginning of the column – the hidden prefix in our case – you can enjoy both worlds – maintain a correct sorting order while displaying only positive colored numbers.
SELECT region, estimate, actual, '<input type="hidden" value="'||(estimate-actual)||'">'|| abs(estimate - actual) AS variance, (CASE WHEN (estimate - actual)>=0 THEN 'green' WHEN (estimate - actual)<0 THEN 'red' ELSE NULL END) AS variance_color FROM expenses
>> sorry for double "<<" and extra spaces - I could not figure out how to format text for this posting)
You should use the forum tags *[code]* and *[/code]* .
♦ Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.
♦ Forthcoming book about APEX: Oracle Application Express 3.2 – The Essentials and More
Include both variance and abs(variance) in the query:
And the HTML Expression for the "variance" column is:
SELECT region, estimate, actual, (estimate - actual) AS variance, ABS(estimate - actual) AS abs_variance, (CASE WHEN (estimate - actual)>=0 THEN 'green' WHEN (estimate - actual)<0 THEN 'red' ELSE NULL END) AS variance_color from expenses
Hide the #VARIANCE_COLOR# and #ABS_VARIANCE# columns.
<span style="color: #VARIANCE_COLOR#; font-weight: bold;">#ABS_VARIANCE#</span>
#ABS_VARIANCE# is the value shown in the column, but the sort is performed in the underlying SQL using the original variance value.
This is perfect! I was looking for a way to display one number, while having it sort by something else, but never could figure out how to do it...
Thanks a lot,