frinpd
asked on
ora-1861
select process_status,organizatio n_code,pla n_name,emp _cd,emp_nm ,project_c d,job_cd,h ours
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,'MO N-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
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,'MO
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That is probably caused by this:
where to_char(character3,'yyyy-M M') = '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.
where to_char(character3,'yyyy-M
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.
where character3 = '2004-08'
if you are sure that character3's values will be in this format.
ASKER
What is wrong with this one than
select process_status,organizatio n_code,pla n_name,emp _nm,docomo _date,proj ect_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'
select process_status,organizatio
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select character1, character3
from qa_results where to_char(to_date(character3
);
It says ORA-01861: literal does not match format string
if i do
select to_char(to_date(character3
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.
ASKER
nope even first one also has plan_id = 108;
allright how can i convert 'AUG-2004' to 2004-08 ?
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(charac ter3,'1900 -01-01'),' yyyy-mm-dd '),'MON-YY YY')
>> 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(charac
ASKER
cursor cursor_insert is
select process_status,organizatio n_code,pla n_name,emp _cd,emp_nm ,docomo_da te,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,'YYY Y-MM-DD') and
to_char(r2.docomo_date,'MO N-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
select process_status,organizatio
from qa_results r1 , dump_tran_initial r2
where r1.character1 = r2.emp_cd and
r2.docomo_date = to_date(r1.character3,'YYY
to_char(r2.docomo_date,'MO
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
ASKER
select process_status,organizatio
from qa_results r1 , dump_tran_initial r2
where r1.character1 = r2.emp_cd and
r2.docomo_date = to_date(r1.character3,'YYY
to_char(r2.docomo_date,'MO
but now this is not working...
select process_status,organizatio
from dump_tran_initial
where ( emp_cd,docomo_date) NOT IN
(select character1, character3
from qa_results where to_char(character3,'yyyy-M
it says invalid number ORA-01722: invalid number