It seems that using a derived column in a SELECT statement, without a table alias can cause problems.
The following base statement executes error free:
SELECT *FROM MyTable
This statement errors with ORA-00936: missing expression:
SELECT TO_DATE(B, 'DD-MON-YYYY') - TO_DATE(A, 'DD-MON-YYYY'), *FROM MyTable
This slightly modified statement will execute as expected:
SELECT TO_DATE(B, 'DD-MON-YYYY') - TO_DATE(a, 'DD-MON-YYYY'), mt.*FROM MyTable mt
Initially, the idea of changing the order of the fields came to mind; it didn’t help. After adding in the alias and checking with a colleague – it worked. I don’t know why the derived column causes problems, however I thought it interesting none the less.
