?
Solved

ora-1861

Posted on 2004-10-15
9
Medium Priority
?
4,968 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 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

621 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