Solved

ora-1861

Posted on 2004-10-15
9
4,922 Views
Last Modified: 2012-08-13
select process_status,organization_code,plan_name,emp_cd,emp_nm,project_cd,job_cd,hours
from qa_results r1 , dump_tran_initial r2
where r1.character1 = r2.emp_cd and
r2.docomo_date = r1.character3 and
 to_char(r2.docomo_date,'MON-YYYY') = 'SEP-2004'  and plan_id = 108;

the above query gives me error

ORA-01861: literal does not match format string

The QA_RESULTs table have character3 field as Varchar2(25) but holds date
The Dump_tran_initial table docomo_date is date datatype
0
Comment
Question by:frinpd
  • 4
  • 4
9 Comments
 
LVL 3

Accepted Solution

by:
dnarramore earned 500 total points
ID: 12322496
I think maybe it's this part that is giving you trouble:
and r2.docomo_date = r1.character3


If you are trying to joing a date field to a character field, you need to convert one or the other so that the datatypes match.


if r1.character3 is in the MM/DD/YYYY format try this:
to_char(r2.docomo_date, 'MM/DD/YYYY')  = r1.character3
0
 

Author Comment

by:frinpd
ID: 12322643
All right that query i have solved it.. using this

select process_status,organization_code,plan_name,emp_cd,emp_nm,character3,project_cd,job_cd,hours
from qa_results r1 , dump_tran_initial r2
where r1.character1 = r2.emp_cd and
r2.docomo_date = to_date(r1.character3,'YYYY-MM-DD') and
to_char(r2.docomo_date,'MON-YYYY') = 'AUG-2004'  and plan_id = 108;


but now this is not working...

select process_status,organization_code,plan_name,emp_nm,docomo_date,project_cd,job_cd,hours
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select character1, character3
from qa_results  where to_char(character3,'yyyy-MM') = '2004-08'  and plan_id = 108);

it says invalid number ORA-01722: invalid number
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 12322764
That is probably caused by this:
   where to_char(character3,'yyyy-MM') = '2004-08'

You are converting a character (assuming VARCHAR2) to a character string (using to_char).  The formatting would be to convert a date field, so it is not making sense to Oracle.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:bvanderveen
ID: 12322771
You could try:
   where character3 = '2004-08'  

if you are sure that character3's values will be in this format.
0
 

Author Comment

by:frinpd
ID: 12322794
What is wrong with this one than

 select process_status,organization_code,plan_name,emp_nm,docomo_date,project_cd,job_cd,hours
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select character1, character3
from qa_results  where to_char(to_date(character3,'yyyy-mm-dd'),'MON-YYYY') = 'AUG-2004'  and plan_id = 108
);

It says ORA-01861: literal does not match format string

if i do
select to_char(to_date(character3,'yyyy-mm-dd'),'MON-YYYY') from qa_results where plan_id = 108;

It returns me 'AUG-2004'
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 12322829
Somewhere in character3 is data where the formatting is wrong and breaking your to_date conversion.  Your first SQL is converting all rows, your second, just those where plan_id = 108.  
0
 

Author Comment

by:frinpd
ID: 12322852
nope even first one also has plan_id = 108;

allright how can i convert 'AUG-2004' to 2004-08 ?
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 12322888
you would do it the way you are showing, nested to_date and to_char functions.

>> nope even first one also has plan_id = 108;

My experience is these conversions errors do sometimes return rows, and there isn't an error until you have moved your cursor (in PLSQL) or scrolled (in some query browsers) to a bad record.  You usually don't see the bad records - these tools usually only fetch X number of rows, and it fails on a batch fetch.  You need to go through all of the data in character3 to see if there is a problem.

One other possibility - NULL value in this column.  Try will NVL function

    to_char(to_date(NVL(character3,'1900-01-01'),'yyyy-mm-dd'),'MON-YYYY')


0
 

Author Comment

by:frinpd
ID: 12323509
cursor cursor_insert is
select process_status,organization_code,plan_name,emp_cd,emp_nm,docomo_date,project_cd,job_cd,hours
from qa_results r1 , dump_tran_initial r2
where r1.character1 = r2.emp_cd and
r2.docomo_date = to_date(r1.character3,'YYYY-MM-DD') and
to_char(r2.docomo_date,'MON-YYYY') = myear  and plan_id = 108;

What is this mean

PLS-00320: the declaration of the type of this expression is incomplete or malformed

it gives me error when i compile my procedure .. if i run as query it works fine
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question