When querying against a view, a filtering clause in the view's definition is being ignored

Question

I have a table SCHEDULES with columns LDATE and SCHTYPEID. If SCHTYPEID = 1, then LDATE contains dates in a numeric format (e.g., 20170918 for today's date). If SCHTYPEID = 2, then LDATE contains 0.

Frequently when writing queries I will convert LDATE to an actual date and will filter out the zeroes like so:

SELECT TO_DATE(LDATE, 'YYYYMMDD') LDATE
FROM SCHEDULES
WHERE SCHTYPEID = 1;

However, when I put this into a view

CREATE VIEW FOO (THE_DATE)
AS SELECT TO_DATE(LDATE, 'YYYYMMDD')
  FROM SCHEDULES
  WHERE SCHTYPEID = 1;

and query against it like so:

SELECT *
FROM FOO
WHERE THE_DATE = TO_DATE(20170918, 'YYYYMMDD');

I receive the error "ORA-01840: input value no long enough for date format". Querying it like this:

SELECT * FROM FOO;

works just fine. Whenever I try to filter it, though, Oracle seems to ignore the filter on SCHTYPEID in the view and it includes the records with SCHTYPEID = 2, causing the error.

Is there anything I can do to ensure that I will only ever query against rows with a populated LDATE column?

I am curious if you have the same problem running SELECT /*+NO_MERGE(FOO)*/ * FROM FOO WHERE THE_DATE = TO_DATE(20170918, 'YYYYMMDD'); — EdmCoff, Sep 18 '17 at 20:04
It would not hurt to remove the implicit casting (`to_date` does not take `number`). It sounds like you have some records where `schtypeid = 1`, yet `LDATE` does not have a format corresponding to your format model, `'YYYYMMDD'`. — Patrick Bacon, Sep 18 '17 at 22:42
@PatrickBacon - so in that case how would the rewrite in the accepted answer not encounter the same problem? — Martin Smith, Sep 19 '17 at 06:25
@MartinSmith I posted an answer with my examination of the problem. I am on 12c. I think a result set is coming back with the case statement approach, yet there are exceptions which are being thrown but not noticed by op. — Patrick Bacon, Sep 21 '17 at 19:20

score 2 · Accepted Answer · answered Sep 18 '17 at 20:05

2

You can make your view handle both cases (even though the WHERE clause is restricting the data to SCHTYPEID = 1), like this:

CREATE VIEW FOO(THE_DATE) AS
  SELECT CASE WHEN SCHTYPEID = 1 THEN TO_DATE(LDATE, 'YYYYMMDD') ELSE NULL END
    FROM SCHEDULES
   WHERE SCHTYPEID = 1;

answered Sep 18 '17 at 20:05

GriffeyDog

8,186
3
22
34

Yeap also good solution, but you are missing SCHTYPEID = 2 rows! ;) – Ted at ORCL.Pro Sep 18 '17 at 20:07
This solution does seem to work, though I am still at a loss as to why. Thank you though! – dunnza Sep 18 '17 at 20:11
3

@TedFilippidis the original view filters those out. The problem presumably is that the view gets inlined and the expression in the select from the view gets evaluated on rows before the filter inside the view removes them. – Martin Smith Sep 18 '17 at 20:12
@MartinSmith then I guess some of SCHTYPEID = 1 data of COLUMN LDATE is not of format YYYYMMDD. What you think? – Ted at ORCL.Pro Sep 18 '17 at 20:17
1

Then why would the fix in this answer work? Looks similar to this issue in SQL Server https://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors but I don't use Oracle and don't know if this is an expected possible behaviour with that product. – Martin Smith Sep 18 '17 at 20:18
I am more familiar Oracle, but I was trying to fill in some black spots in the questions, maybe I was guessing wrong :D ;) – Ted at ORCL.Pro Sep 18 '17 at 20:22
I'm still not convinced of the behaviour and fix. Even if the where condition gets inlined, the evaluation of the 'TO_DATE' on the column should not happen unless a record is qualified against the overall 'WHERE' condition! – BA. Sep 19 '17 at 05:10
1

Ignore my comment, I'm wrong, it can evaluate the TO_DATE before, especially if it is using an index on LDATE – BA. Sep 19 '17 at 05:31

Ted at ORCL.Pro · Answer 2 · 2017-09-18T20:09:57.270

1

CREATE VIEW FOO (THE_DATE)
AS SELECT TO_DATE(LDATE, 'YYYYMMDD')
  FROM SCHEDULES
  WHERE SCHTYPEID = 1
UNION ALL
SELECT TO_DATE('19000101', 'YYYYMMDD')
  FROM SCHEDULES
  WHERE SCHTYPEID = 2

edited Sep 18 '17 at 20:09

answered Sep 18 '17 at 20:04

Ted at ORCL.Pro

1,602
1
7
10

1

Thank you for your answer but this results in the same behavior. When I simply run a "select *", it works, but when I try filtering for a specific date or range of dates, I get the input value error. – dunnza Sep 18 '17 at 20:07
then you neeed to decide on a date that means zero e.g. replace null with TO_DATE('1-1-1900','dd-mm-yyyy) – Ted at ORCL.Pro Sep 18 '17 at 20:09
@dunnza still not working for you after the above change? I am very currious – Ted at ORCL.Pro Sep 18 '17 at 21:03
I tried your solution but still go the same input error! Thanks again for giving it a shot. – dunnza Sep 20 '17 at 17:47

Patrick Bacon · Answer 3 · 2017-09-21T22:05:34.233

It would not hurt to put dates into a DATE column

Even with the view definition using a CASE statement to conditionally cast the ldate column, the sql statement results in errors.

You could put a date into a DATE column. This is the best approach.
Suppose you can not do the above, you could have a constraint on this column (perhaps a conditional constraint). This would probably be the next best solution.
Suppose you can not add a constraint to the table, some applications have functionality around value sets assigned to various fields (e.g. Oracle Apps descriptive flexfields fall into this category).
Suppose you might not want/can add a value set, you could have a custom utility package checking the data after the fact. This would be less than ideal.

Here is my examination of this problem:

`SCOTT@dev>CREATE TABLE schedules
  2      AS
  3          ( SELECT
  4              hiredate ldate,
  5              to_number(TO_CHAR(hiredate,'YYYYMMDD') ) ldate_number,
  6              1 schtypeid
  7          FROM
  8              emp
  9          UNION ALL
 10          SELECT
 11              TO_DATE(NULL),
 12              to_number(NULL),
 13              2
 14          FROM
 15              dual
 16          UNION ALL
 17          SELECT
 18              hiredate,
 19              to_number(substr(
 20                  TO_CHAR(hiredate,'YYYYMMDD'),
 21                  1,
 22                  4
 23              ) ),
 24              1
 25          FROM
 26              emp
 27          );

Table SCHEDULES created.

 SCOTT@dev>SELECT
      2      COUNT(1)
      3  FROM
      4      schedules;
    COUNT(1)  
    29

Create a utility package to examine/see these casting exceptions. I borrowed the logic that a number of people have used (see these links):

Justin Cave's solution: How to handle to_date exceptions in a SELECT statment to ignore those rows?

Nicholas Krasnov's solution: What exact exception to be caugth while calling TO_DATE in pl/sql code

    SCOTT@dev>CREATE OR REPLACE PACKAGE util AS
      2      FUNCTION to_date_exception (
      3          p_char_literal   IN VARCHAR2,
      4          p_date_format    IN VARCHAR2
      5      ) RETURN VARCHAR2;
      6  
      7      FUNCTION my_to_date (
      8          p_char_literal   IN VARCHAR2,
      9          p_date_format    IN VARCHAR2
     10      ) RETURN DATE;
     11  
     12  END;
     13  /

    Package UTIL compiled

    SCOTT@dev>CREATE OR REPLACE PACKAGE BODY util AS
      2  
      3      FUNCTION to_date_exception (
      4          p_char_literal   IN VARCHAR2,
      5          p_date_format    IN VARCHAR2
      6      ) RETURN VARCHAR2 IS
      7          l_check_date      DATE;
      8          l_error_code      VARCHAR(20);
      9          l_error_message   VARCHAR2(200);
     10      BEGIN
     11          l_check_date := TO_DATE(p_char_literal,p_date_format);
     12           -- NULL will be returned when cast works
     13          RETURN NULL;
     14      EXCEPTION
     15          WHEN OTHERS THEN
     16              l_error_code := 'ORA' || TO_CHAR(sqlcode);
     17              l_error_message := sqlerrm;
     18              RETURN l_error_code;
     19      END;
     20  
     21      FUNCTION my_to_date (
     22          p_char_literal  IN VARCHAR2,
     23          p_date_format IN VARCHAR2
     24      ) RETURN DATE IS
     25          l_date   DATE;
     26      BEGIN
     27          l_date := TO_DATE(p_char_literal,p_date_format);
     28          RETURN l_date;
     29      EXCEPTION
     30          WHEN OTHERS THEN
     31              RETURN TO_DATE(NULL);
     32      END;
     33  
     34  END;
     35  /

    Package Body UTIL compiled

SCOTT@dev>SELECT
  2      util.to_date_exception(ldate_number,'YYYYMMDD') excptn,
  3      util.my_to_date(ldate_number,'YYYYMMDD') the_date_all,
  4      ldate
  5  FROM
  6      schedules
  7  WHERE
  8      schtypeid = 1;
EXCPTN    THE_DATE_ALL             LDATE                    
          17-DEC-1980 12:00:00 AM  17-DEC-1980 12:00:00 AM  
          20-FEB-1981 12:00:00 AM  20-FEB-1981 12:00:00 AM  
          22-FEB-1981 12:00:00 AM  22-FEB-1981 12:00:00 AM  
          02-APR-1981 12:00:00 AM  02-APR-1981 12:00:00 AM  
          28-SEP-1981 12:00:00 AM  28-SEP-1981 12:00:00 AM  
          01-MAY-1981 12:00:00 AM  01-MAY-1981 12:00:00 AM  
          09-JUN-1981 12:00:00 AM  09-JUN-1981 12:00:00 AM  
          09-DEC-1982 12:00:00 AM  09-DEC-1982 12:00:00 AM  
          17-NOV-1981 12:00:00 AM  17-NOV-1981 12:00:00 AM  
          08-SEP-1981 12:00:00 AM  08-SEP-1981 12:00:00 AM  
          12-JAN-1983 12:00:00 AM  12-JAN-1983 12:00:00 AM  
          03-DEC-1981 12:00:00 AM  03-DEC-1981 12:00:00 AM  
          03-DEC-1981 12:00:00 AM  03-DEC-1981 12:00:00 AM  
          23-JAN-1982 12:00:00 AM  23-JAN-1982 12:00:00 AM  
ORA-1840                           17-DEC-1980 12:00:00 AM  
ORA-1840                           20-FEB-1981 12:00:00 AM  
ORA-1840                           22-FEB-1981 12:00:00 AM  
ORA-1840                           02-APR-1981 12:00:00 AM  
ORA-1840                           28-SEP-1981 12:00:00 AM  
ORA-1840                           01-MAY-1981 12:00:00 AM  
ORA-1840                           09-JUN-1981 12:00:00 AM  
ORA-1840                           09-DEC-1982 12:00:00 AM  
ORA-1840                           17-NOV-1981 12:00:00 AM  
ORA-1840                           08-SEP-1981 12:00:00 AM  
ORA-1840                           12-JAN-1983 12:00:00 AM  
ORA-1840                           03-DEC-1981 12:00:00 AM  
ORA-1840                           03-DEC-1981 12:00:00 AM  
ORA-1840                           23-JAN-1982 12:00:00 AM  


28 rows selected.

Original view definition:

SCOTT@dev>CREATE OR REPLACE VIEW FOO
  2  AS SELECT TO_DATE(ldate_number, 'FXYYYYMMDD') the_date,
  3      ldate
  4    FROM SCHEDULES
  5    WHERE SCHTYPEID = 1;

View FOO created.

SCOTT@dev>SELECT
  2      COUNT(1)
  3  FROM
  4      foo;
COUNT(1)  
28        


SCOTT@dev>SELECT
  2      *
  3  FROM
  4      foo
  5  WHERE
  6      the_date = TO_DATE(20170918,'YYYYMMDD');

Error starting at line : 1 in command -
SELECT
    *
FROM
    foo
WHERE
    the_date = TO_DATE(20170918,'YYYYMMDD')
Error report -
ORA-01840: input value not long enough for date format

**************************************

Proposed view definition using the CASE statement:

SCOTT@dev>CREATE OR REPLACE VIEW FOO(THE_DATE) AS
  2    SELECT CASE WHEN SCHTYPEID = 1 THEN TO_DATE(ldate_number, 'YYYYMMDD') ELSE NULL END
  3      FROM SCHEDULES
  4     WHERE SCHTYPEID = 1;

View FOO created.

SCOTT@dev>SELECT
  2      COUNT(1)
  3  FROM
  4      foo;
COUNT(1)
28


    SCOTT@dev>list
  1  SELECT
  2     the_date 
  3  FROM
  4      foo
  5  WHERE
  6*     the_date = TO_DATE(20170918,'YYYYMMDD')
SCOTT@dev>/

Error starting at line : 1 in command -
SELECT
   the_date 
FROM
    foo
WHERE
    the_date = TO_DATE(20170918,'YYYYMMDD')
Error report -
ORA-01840: input value not long enough for date format

As can be seen here (using Oracle 12c), I think the CASE statement approach has issues and the underlying problem should be solved by making sure bad data does not get in to this column.

Thank you for your thoughts on the matter. First, I must say that the data I am dealing with is from a vendor's application. I cannot change the schema in any way, shape, or form. — dunnza, Sep 22 '17 at 18:39
Second, I think you are trying to find a problem that does not exist. The reason your view with the case statement fails is because you have purposely inserted bad data. You have inserted several records into the `schedules` table that is just the year. Of course that will still fail to be formatted by 'YYYYMMDD'. In my original post, I said that if the `schtypeid` = 1, then the column has data in the form of 20170922. if the `schtypeid` = 2, then it is 0. That is how my data is and as far as I have ever seen it does not have errors in the way you suggest. — dunnza, Sep 22 '17 at 18:44

When querying against a view, a filtering clause in the view's definition is being ignored

3 Answers3

Linked