APEX

Hello all!

Back in November of last year, I received some assistance from user "jarola" regarding changing the color of a column item in a query based on the resulting value. This discussion yielded;

declare 
   l_query varchar2(4000) := '';
begin 
   l_query := '
     select i.INVENTORY_ID, 
            io.ORDER_QUANTITY,
            CASE
               WHEN i.QUANTITY_AVAILABLE=0
               THEN ''<span style="color:red;"> ''|| i.QUANTITY_AVAILABLE ||''</span>''
               ELSE to_char(i.QUANTITY_AVAILABLE)
            END,
            i.QUANTITY_AVAILABLE qty_avail_hold,
            i.STRAIN_CODE,
            i.STRAIN_NAME,
            i.GENOTYPE,
            i.AGE,
            i.***,
            (lower(substr(i.ROOM_NUMBER,1,instr(i.ROOM_NUMBER,''-'',1,1)-1))) ROOM_NUMBER
     from   SM_INVENTORY i,
            SM_INVENTORY_ORDER io
     where  io.ORDER_ID (+)= :F111_MODIFYING_ORDER
     and    io.INVENTORY_ID (+)= i.INVENTORY_ID';
return l_query;
end;

Problem is, sorting (report attributes for column set to "sort") on column i.QUANTITY_AVAILABLE is broken as even though the data in the table is number(12,0), the CASE statement returns it as alpha. I'm having difficulty getting this CASE statement to return a number as these modifications cause generic compilation errors.

ELSE i.QUANTITY_AVAILABLE
ELSE to_number(i.QUANTITY_AVAILABLE)

Google/sqlplus docs have yielded little. Might anyone offer advice?

Thanks!!!

Paul