Editing an Oracle WHERE clause
Posted: Mon Sep 13, 2010 4:36 am
I'm trying to edit a complex expression that I've built up using the Oracle 10g Query Builder, is there any way that I can do this without causing an "Unhandled exception" error? Once the constructed WHERE clause has been added, it looks like the only way that I can change it is to manually delete it from the Selection edit and then build it up again from scratch using the very slow and annoying Expression Editor. Any hints?
*Edit*: Looks like it's not just the editing that is causing the problem, the built expression also causes the exception. Here is what it looks like...[/size]
Is it objecting to "TO_CHAR( SYSDATE , 'YYYY' ) - 2", subtracting a number from a string? I'm sure I've done that before iin Oracle.
*Edit2*: Also, it won't allow TO_NUMBER with a single parameter!
*Edit3*: I've given up and done the calculation in a Sequence Job.
*Edit*: Looks like it's not just the editing that is causing the problem, the built expression also causes the exception. Here is what it looks like...
Code: Select all
TABLE.SDATE >= TO_TIMESTAMP('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TABLE.SDATE >= TO_TIMESTAMP( CONCAT( ( TO_CHAR( SYSDATE , 'YYYY' ) - 2 ) , '-01-01 00:00:00' ) , 'YYYY-MM-DD HH24:MI:SS' )
Is it objecting to "TO_CHAR( SYSDATE , 'YYYY' ) - 2", subtracting a number from a string? I'm sure I've done that before iin Oracle.
*Edit2*: Also, it won't allow TO_NUMBER with a single parameter!
*Edit3*: I've given up and done the calculation in a Sequence Job.