Link to home
Start Free TrialLog in
Avatar of frinpd
frinpd

asked on

ora-1861

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
ASKER CERTIFIED SOLUTION
Avatar of dnarramore
dnarramore

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of frinpd
frinpd

ASKER

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
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.
You could try:
   where character3 = '2004-08'  

if you are sure that character3's values will be in this format.
Avatar of frinpd

ASKER

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'
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.  
Avatar of frinpd

ASKER

nope even first one also has plan_id = 108;

allright how can i convert 'AUG-2004' to 2004-08 ?
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')


Avatar of frinpd

ASKER

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