Usable Layout

Came across an oracle ORA-01722 error when trying to run a query that (I thought) should have worked.

When trying to run a query select * from purchase where status='sold' and to_number(price) > 100; on a table where the price column is a varchar and contains more than just numbers, will produce an ORE-01722 error. However, for records where status='sold', the price column will only contain numbers so we should be able to run this query.

The reason for this is that to_number is being evaluated on the price column of all records.

By forcing the oracle database optimizer to preserve the order of predicate evaluation with the ORDERED_PREDICATES hint, we can run this query.

select /*+ ORDERED_PREDICATES */ * from purchase where status='sold' and to_number(price) > 100;

This forces oracle to first reduce the record set down to just records where status='sold' and then executes to_number of the price column of the remaining records.

To run this query on an update use the syntax:
update /*+ ORDERED_PREDICATES */ purchase set big_ticket_item = '1' where status = 'sold' and to_number(price) > 100;

However if there are still non-numeric characters left in the price column after evaluating status='sold' then you will still get the ORA-01722 invalid number error.

However, this table should have been better architected originally. The better approach would be to have two columns on this table, asking_price and sold_price, with asking_price being a varchar and the purchase_price column being a number.