Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01848 error on view query
ORA-01848 error on view query [message #304554] Wed, 05 March 2008 11:26 Go to next message
aglore
Messages: 6
Registered: March 2008
Junior Member
I have created a view that includes the union of two inline views.
When I perform the 2 queries (inline views) individually, everything works great. When I perform the query on the union, I get "ORA-01848: day of year must be between 1 and 365 (366 for leap year)".

Is there something documented about ORA-01848 and views using union or union all? Why am I getting this message?

I've included the definition of a sample table, sample data, and the view definition:
create table sample1 (doc_num varchar2(20), loc_cd varchar(20), rcd_typ varchar2(20), proc_typ varchar2(20), ser_num varchar2(20), filler1 varchar2(20), trans_dt DATE, trans_tm VARCHAR2(10));

insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm) values ('doc1', '1', 'OUT', '2I', '1', 'ACK52284WC0123', to_date('08-JAN-2007', 'DD-MON-YYYY'), '091954');
insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm) values ('doc1', '1', 'IN', '1B', '2', ']  000RACK  4Z', to_date('09-JAN-2007', 'DD-MON-YYYY'), '103220');
insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm) values ('doc2', '2', 'OUT', '4Y', '1', '070071312', to_date('08-JAN-2007', 'DD-MON-YYYY'), '131115');
insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm) values ('doc2', '2', 'IN', '1B', '2', '000RZZZ  0M', to_date('16-JAN-2007', 'DD-MON-YYYY'), '160958');

CREATE OR REPLACE VIEW V_SAMPLE1 AS
select doc_num, sending_loc, sending_rcd_typ, sending_proc_typ, receiving_loc, receiving_rcd_typ, receiving_proc_typ, ser_num, strt_dt, stp_dt, velocity_in_hrs from (
select trans_strt.doc_num, 
       sending_loc, 
       trans_strt.rcd_typ sending_rcd_typ, 
       trans_strt.proc_typ sending_proc_typ, 
       receiving_loc, 
       trans_stp.rcd_typ receiving_rcd_typ, 
       trans_stp.proc_typ receiving_proc_typ, 
       trans_strt.ser_num, 
       strt_dt, 
       stp_dt,
       to_number(to_char((stp_dt-strt_dt)*24, '99999999.99')) velocity_in_hrs
  from 
(select doc_num, rcd_typ, proc_typ, to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
  from sample1
 where (rcd_typ = 'OUT' and proc_typ = '4Y')
) trans_strt,
(select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') stp_dt, loc_cd receiving_loc, ser_num ser_num
   from sample1
  where (rcd_typ = 'IN' and proc_typ = '1B')
) trans_stp
where trans_strt.doc_num = trans_stp.doc_num
  and strt_dt < stp_dt
union all
select trans_strt.doc_num, 
       sending_loc, 
       trans_strt.rcd_typ sending_rcd_typ, 
       trans_strt.proc_typ sending_proc_typ, 
       receiving_loc, 
       trans_stp.rcd_typ receiving_rcd_typ, 
       trans_stp.proc_typ receiving_proc_typ, 
       trans_strt.ser_num, 
       strt_dt, 
       stp_dt,
       to_number(to_char((stp_dt-strt_dt)*24, '99999999.99')) velocity_in_hrs
  from 
(select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
  from sample1
 where (rcd_typ = 'OUT') 
) trans_strt,
(select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') stp_dt, loc_cd receiving_loc, ser_num ser_num
   from sample1
  where (rcd_typ = 'IN' and proc_typ = '1B')
    and doc_num in (select doc_num from sample1 where (rcd_typ = 'IN' and proc_typ = '1B') minus select doc_num from sample1 where rcd_typ = 'OUT' and proc_typ = '4Y')
) trans_stp
where trans_strt.doc_num = trans_stp.doc_num
  and strt_dt < stp_dt);


When I query the view:
select count(*) from v_sample1 where ser_num = 1;

COUNT(*)
----------
2

When I query the view for date:
SQL> select count(*) from v_sample1 where strt_dt > '01-JAN-2007';
select count(*) from v_sample1 where strt_dt > '01-JAN-2007'
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Re: ORA-01848 error on view query [message #304557 is a reply to message #304554] Wed, 05 March 2008 11:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You should use the TO_DATE function to convert your strings to dates, otherwise if your nls_date_format does not happen to match your string format, the attempted implicit conversion may produce various errors like the one you got. So use:

select count(*) from v_sample1 where strt_dt > TO_DATE ('01-JAN-2007', 'DD-MON-YYYY');
Re: ORA-01848 error on view query [message #304558 is a reply to message #304557] Wed, 05 March 2008 11:45 Go to previous messageGo to next message
aglore
Messages: 6
Registered: March 2008
Junior Member
not the problem:
SQL> select count(*) from v_sample1 where strt_dt > TO_DATE ('01-JAN-2007', 'DD-MON-YYYY');
select count(*) from v_sample1 where strt_dt > TO_DATE ('01-JAN-2007', 'DD-MON-YYYY')
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Re: ORA-01848 error on view query [message #304560 is a reply to message #304558] Wed, 05 March 2008 11:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
That's odd. The following is what I get:

SCOTT@orcl_11g> select count(*) from v_sample1 where ser_num = 1
  2  /

  COUNT(*)
----------
         2

SCOTT@orcl_11g> select count(*) from v_sample1 where strt_dt > '01-JAN-2007'
  2  /
select count(*) from v_sample1 where strt_dt > '01-JAN-2007'
                                               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SCOTT@orcl_11g> select count(*) from v_sample1 where strt_dt > TO_DATE ('01-JAN-2007', 'DD-MON-YYYY')
  2  /

  COUNT(*)
----------
         2

SCOTT@orcl_11g> 


What does the following return on your system?

SELECT SYSDATE FROM DUAL;
Re: ORA-01848 error on view query [message #304562 is a reply to message #304560] Wed, 05 March 2008 11:54 Go to previous messageGo to next message
aglore
Messages: 6
Registered: March 2008
Junior Member
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
05-MAR-08

SQL> select count(*) from v_sample1 where strt_dt > TO_DATE ('01-JAN-2007', 'DD-MON-YYYY');
select count(*) from v_sample1 where strt_dt > TO_DATE ('01-JAN-2007', 'DD-MON-YYYY')
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Re: ORA-01848 error on view query [message #304563 is a reply to message #304562] Wed, 05 March 2008 11:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
What does the following return?

select strt_dt from v_sample1;
Re: ORA-01848 error on view query [message #304564 is a reply to message #304563] Wed, 05 March 2008 11:59 Go to previous messageGo to next message
aglore
Messages: 6
Registered: March 2008
Junior Member
SQL> select strt_dt from v_sample1;

STRT_DT
---------
07-JAN-07
08-JAN-07

SQL> select to_char(strt_dt, 'DD-MON-YYYY HH24:MI:SS') from v_sample1;

TO_CHAR(STRT_DT,'DD-
--------------------
07-JAN-2007 13:12:00
08-JAN-2007 09:19:54
Re: ORA-01848 error on view query [message #304565 is a reply to message #304564] Wed, 05 March 2008 12:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Your problem is here:

to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') strt_dt,

Even though those rows are filtered out of the result set, the optimizer may choose to validate them before the filteirng. You should see the problem by:

SCOTT@orcl_11g> select to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') from sample1
2 /
select to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') from sample1
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SCOTT@orcl_11g>
Re: ORA-01848 error on view query [message #304566 is a reply to message #304565] Wed, 05 March 2008 12:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
To further clarify, this is what it is trying to derive a date from:

SCOTT@orcl_11g> select substr(filler1, 1, 5)||' '||substr(filler1, 6, 4)  from sample1
  2  /

SUBSTR(FIL
----------
ACK52 284W
]  00 0RAC
07007 1312
000RZ ZZ

SCOTT@orcl_11g> 



[Updated on: Wed, 05 March 2008 12:17]

Report message to a moderator

Re: ORA-01848 error on view query [message #304567 is a reply to message #304566] Wed, 05 March 2008 12:21 Go to previous messageGo to next message
aglore
Messages: 6
Registered: March 2008
Junior Member
But, then shouldn't a view without the union (using only the first select) fail as well?
CREATE OR REPLACE VIEW V_SAMPLE2 AS
select trans_strt.doc_num, 
       sending_loc, 
       trans_strt.rcd_typ sending_rcd_typ, 
       trans_strt.proc_typ sending_proc_typ, 
       receiving_loc, 
       trans_stp.rcd_typ receiving_rcd_typ, 
       trans_stp.proc_typ receiving_proc_typ, 
       trans_strt.ser_num, 
       strt_dt, 
       stp_dt,
       to_number(to_char((stp_dt-strt_dt)*24, '99999999.99')) velocity_in_hrs
  from 
(select doc_num, rcd_typ, proc_typ, to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
  from sample1
 where (rcd_typ = 'OUT' and proc_typ = '4Y')
) trans_strt,
(select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') stp_dt, loc_cd receiving_loc, ser_num ser_num
   from sample1
  where (rcd_typ = 'IN' and proc_typ = '1B')
) trans_stp
where trans_strt.doc_num = trans_stp.doc_num
  and strt_dt < stp_dt;


SQL> select count(*) from v_sample2 where ser_num = 1;

COUNT(*)
----------
1

SQL> select count(*) from v_sample2 where strt_dt > to_date('01-JAN-2007', 'DD-MON-YYYY');

COUNT(*)
----------
1


Re: ORA-01848 error on view query [message #304568 is a reply to message #304567] Wed, 05 March 2008 12:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Not necessarily. Changing the query may change the execution plan that the optimizer chooses. If it happens to filter the rows first, then validate the dates, the query works. If it tries to validate the dates before filtering the rows, then it does not work.
Re: ORA-01848 error on view query [message #304569 is a reply to message #304567] Wed, 05 March 2008 12:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
-- If it filters the rows first (using the where clause) there is no problem:
SCOTT@orcl_11g> select doc_num, rcd_typ, proc_typ, to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
  2    from sample1
  3   where (rcd_typ = 'OUT' and proc_typ = '4Y')
  4  /

DOC_NUM              RCD_TYP              PROC_TYP             STRT_DT
-------------------- -------------------- -------------------- ---------
STRT_TRAN SENDING_LOC          SER_NUM
--------- -------------------- --------------------
doc2                 OUT                  4Y                   07-JAN-07
08-JAN-07 2                    1


-- If you test by eliminating the where clause so that no row filtering occurs, then you see the problem:
SCOTT@orcl_11g> select doc_num, rcd_typ, proc_typ, to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
  2    from sample1
  3  /
select doc_num, rcd_typ, proc_typ, to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
                                                                      *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SCOTT@orcl_11g> 


Re: ORA-01848 error on view query [message #304570 is a reply to message #304569] Wed, 05 March 2008 12:39 Go to previous messageGo to next message
aglore
Messages: 6
Registered: March 2008
Junior Member
I see that.

Is there a better workaround than using a CASE in the SELECT:
CREATE OR REPLACE VIEW V_SAMPLE1 AS
select doc_num, sending_loc, sending_rcd_typ, sending_proc_typ, receiving_loc, receiving_rcd_typ, receiving_proc_typ, ser_num, strt_dt, stp_dt, velocity_in_hrs from (
select trans_strt.doc_num, 
       sending_loc, 
       trans_strt.rcd_typ sending_rcd_typ, 
       trans_strt.proc_typ sending_proc_typ, 
       receiving_loc, 
       trans_stp.rcd_typ receiving_rcd_typ, 
       trans_stp.proc_typ receiving_proc_typ, 
       trans_strt.ser_num, 
       strt_dt, 
       stp_dt,
       to_number(to_char((stp_dt-strt_dt)*24, '99999999.99')) velocity_in_hrs
  from 
(select doc_num, rcd_typ, proc_typ, 
        (CASE WHEN rcd_typ='OUT' and proc_typ='4Y'
              THEN to_date(substr(filler1, 1, 5)||' '||substr(filler1, 6, 4), 'YYDDD HH24MI')
         ELSE null
         END) strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
  from sample1
 where (rcd_typ = 'OUT' and proc_typ = '4Y')
) trans_strt,
(select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') stp_dt, loc_cd receiving_loc, ser_num ser_num
   from sample1
  where (rcd_typ = 'IN' and proc_typ = '1B')
) trans_stp
where trans_strt.doc_num = trans_stp.doc_num
  and strt_dt < stp_dt
union all
select trans_strt.doc_num, 
       sending_loc, 
       trans_strt.rcd_typ sending_rcd_typ, 
       trans_strt.proc_typ sending_proc_typ, 
       receiving_loc, 
       trans_stp.rcd_typ receiving_rcd_typ, 
       trans_stp.proc_typ receiving_proc_typ, 
       trans_strt.ser_num, 
       strt_dt, 
       stp_dt,
       to_number(to_char((stp_dt-strt_dt)*24, '99999999.99')) velocity_in_hrs
  from 
(select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
  from sample1
 where (rcd_typ = 'OUT') 
) trans_strt,
(select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') stp_dt, loc_cd receiving_loc, ser_num ser_num
   from sample1
  where (rcd_typ = 'IN' and proc_typ = '1B')
    and doc_num in (select doc_num from sample1 where (rcd_typ = 'IN' and proc_typ = '1B') minus select doc_num from sample1 where rcd_typ = 'OUT' and proc_typ = '4Y')
) trans_stp
where trans_strt.doc_num = trans_stp.doc_num
  and strt_dt < stp_dt);


SQL> select count(*) from v_sample1;

COUNT(*)
----------
2

SQL> select count(*) from v_sample1 where ser_num = 1;

COUNT(*)
----------
2

SQL> select count(*) from v_sample1 where strt_dt > to_date('01-JAN-2007', 'DD-MON-YYYY');

COUNT(*)
----------
2

Re: ORA-01848 error on view query [message #304571 is a reply to message #304567] Wed, 05 March 2008 12:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
I suggest that you create a user-defined function to validate your date and return a null date in place of any invalid date, and use that function in your code, as demonstrated below.


SCOTT@orcl_11g> create table sample1 (doc_num varchar2(20), loc_cd varchar(20), rcd_typ varchar2(20), proc_typ varchar2(20), ser_num varchar2(20), filler1 varchar2(20), trans_dt DATE, trans_tm VARCHAR2(10));

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm)
  2  values ('doc1', '1', 'OUT', '2I', '1', 'ACK52284WC0123', to_date('08-JAN-2007', 'DD-MON-YYYY'), '091954');

1 row created.

SCOTT@orcl_11g> insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm)
  2  values ('doc1', '1', 'IN', '1B', '2', ']  000RACK	4Z', to_date('09-JAN-2007', 'DD-MON-YYYY'), '103220');

1 row created.

SCOTT@orcl_11g> insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm)
  2  values ('doc2', '2', 'OUT', '4Y', '1', '070071312', to_date('08-JAN-2007', 'DD-MON-YYYY'), '131115');

1 row created.

SCOTT@orcl_11g> insert into sample1 (doc_num, loc_cd, rcd_typ, proc_typ, ser_num, filler1, trans_dt, trans_tm)
  2  values ('doc2', '2', 'IN', '1B', '2', '000RZZZ  0M', to_date('16-JAN-2007', 'DD-MON-YYYY'), '160958');

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> ------------------------------------------------------------------
SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION valid_date
  2    (p_string IN VARCHAR2)
  3    RETURN DATE
  4  AS
  5  BEGIN
  6    RETURN TO_DATE (p_string, 'YYDDD HH24MI');
  7  EXCEPTION
  8    WHEN OTHERS THEN RETURN TO_DATE (NULL);
  9  END valid_date;
 10  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> ---------------------------------------------------------------------------
SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE VIEW V_SAMPLE1 AS
  2  select doc_num, sending_loc, sending_rcd_typ, sending_proc_typ, receiving_loc, receiving_rcd_typ, receiving_proc_typ, ser_num, strt_dt, stp_dt, velocity_in_hrs from (
  3  select trans_strt.doc_num,
  4  	    sending_loc,
  5  	    trans_strt.rcd_typ sending_rcd_typ,
  6  	    trans_strt.proc_typ sending_proc_typ,
  7  	    receiving_loc,
  8  	    trans_stp.rcd_typ receiving_rcd_typ,
  9  	    trans_stp.proc_typ receiving_proc_typ,
 10  	    trans_strt.ser_num,
 11  	    strt_dt,
 12  	    stp_dt,
 13  	    to_number(to_char((stp_dt-strt_dt)*24, '99999999.99')) velocity_in_hrs
 14    from
 15  (select doc_num, rcd_typ, proc_typ, VALID_DATE (substr(filler1, 1, 5)||' '||substr(filler1, 6, 4)) strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
 16    from sample1
 17   where (rcd_typ = 'OUT' and proc_typ = '4Y')
 18  ) trans_strt,
 19  (select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') stp_dt, loc_cd receiving_loc, ser_num ser_num
 20  	from sample1
 21    where (rcd_typ = 'IN' and proc_typ = '1B')
 22  ) trans_stp
 23  where trans_strt.doc_num = trans_stp.doc_num
 24    and strt_dt < stp_dt
 25  union all
 26  select trans_strt.doc_num,
 27  	    sending_loc,
 28  	    trans_strt.rcd_typ sending_rcd_typ,
 29  	    trans_strt.proc_typ sending_proc_typ,
 30  	    receiving_loc,
 31  	    trans_stp.rcd_typ receiving_rcd_typ,
 32  	    trans_stp.proc_typ receiving_proc_typ,
 33  	    trans_strt.ser_num,
 34  	    strt_dt,
 35  	    stp_dt,
 36  	    to_number(to_char((stp_dt-strt_dt)*24, '99999999.99')) velocity_in_hrs
 37    from
 38  (select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_dt, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') strt_trans_dt, loc_cd sending_loc, ser_num ser_num
 39    from sample1
 40   where (rcd_typ = 'OUT')
 41  ) trans_strt,
 42  (select doc_num, rcd_typ, proc_typ, to_date(to_char(trans_dt, 'YYYY-MM-DD')||' '||trans_tm, 'YYYY-MM-DD HH24MISS') stp_dt, loc_cd receiving_loc, ser_num ser_num
 43  	from sample1
 44    where (rcd_typ = 'IN' and proc_typ = '1B')
 45  	 and doc_num in (select doc_num from sample1 where (rcd_typ = 'IN' and proc_typ = '1B') minus select doc_num from sample1 where rcd_typ = 'OUT' and proc_typ = '4Y')
 46  ) trans_stp
 47  where trans_strt.doc_num = trans_stp.doc_num
 48    and strt_dt < stp_dt)
 49  /

View created.


SCOTT@orcl_11g> select strt_dt from v_sample1
  2  /

STRT_DT
---------
07-JAN-07
08-JAN-07

SCOTT@orcl_11g> select count(*) from v_sample1 where ser_num = 1
  2  /

  COUNT(*)
----------
         2

SCOTT@orcl_11g> select count(*) from v_sample1 where strt_dt > TO_DATE ('01-JAN-2007', 'DD-MON-YYYY')
  2  /

  COUNT(*)
----------
         2

SCOTT@orcl_11g> SPOOL OFF


Re: ORA-01848 error on view query [message #304572 is a reply to message #304570] Wed, 05 March 2008 12:44 Go to previous message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
I posted my valid_date function prior to seeing your case statement. I think I like your case statement better.
Previous Topic: merged again: 2000+ selects, fill rates
Next Topic: the ZERO didn't appear ..... why ?
Goto Forum:
  


Current Time: Sat Dec 03 12:18:06 CST 2016

Total time taken to generate the page: 0.08554 seconds