Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ora-1861

Posted on 2004-10-15
9
Medium Priority
?
4,953 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

604 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