Solved

ora-1861

Posted on 2004-10-15
9
4,906 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 7

Expert Comment

by:bvanderveen
Comment Utility
You could try:
   where character3 = '2004-08'  

if you are sure that character3's values will be in this format.
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.

 

Author Comment

by:frinpd
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

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.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now