Hello,
When I execute following statement in APEX (SQL Workshop > SQL Commands):
SELECT REGION, AUT, SUBDOMAIN, JAN2013, TO_DATE('01/01/2013', 'MM/DD/YYYY') FROM VCOUNT
I get my results (as to be expected)
REGION AUT SUBDOMAIN JAN2013 TO_DATE('01/01/2013','MM/DD/YYYY')
APAC A Accounting 4 01/01/2013
APAC A CMS 4 01/01/2013
APAC A CRM 1 01/01/2013
However if I execute this statement within Oracle SQL/Developer, I get this:
Error starting at line : 1 in command -
SELECT REGION, AUT, SUBDOMAIN, JAN2013, TO_DATE('01/01/2013', 'MM/DD/YYYY') FROM VCOUNT
Error report -
SQL Error: ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:
"Why bother to execute this in SQL/Developer?" you might ask.
Well this "select" is part of an insert statement into a table that works perfectly when executed using (SQL Workshop > SQL Commands)
but fails when executed from within an Apex application
VCOUNT is defined as a view
CREATE OR REPLACE FORCE VIEW "VCOUNT" ("REGION", "AUT", "SUBDOMAIN", "TELLER", "JAN2013") AS
SELECT t.region, t.aut, t.subdomain, t.teller, V20130101.TELLER JAN2013
FROM VTOTALS T, V20130101
WHERE
T.REGION = V20130101.REGION(+) AND
T.AUT = V20130101.AUT(+) AND
T.SUBDOMAIN = V20130101.SUBDOMAIN(+);
Both VTOTALS and V20130101 are views themselves.
Any ideas someone?