| procedure [message #36522] |
Mon, 03 December 2001 03:12  |
Frederik
Messages: 8
Registered: December 2001
|
Junior Member |
|
|
Iīm working on a procedure, but I keep getting this error msg: SQL Error: ORACLE - ORA-20100: ORA-01403: no data found.
I believe the error comes from the following:
--> Variables
wEmpId NUMBER;
wFecha DATE;
wDiaEnt VARCHAR2(10);
wDiaSal VARCHAR2(10);
wEnt NUMBER;
wSal NUMBER;
--> Cursor
CURSOR Cursor1 IS
SELECT empid, fecha
FROM MY_TABLE_1
WHERE SessionId = wSessionId;
BEGIN
OPEN Cursor1;
FETCH Cursor1 INTO wEmpId, wFecha;
WHILE (Cursor1%FOUND) LOOP
SELECT TO_CHAR(ent,'Day'),TO_CHAR(sal,'Day'),(60*TO_CHAR(ent,'HH24')+TO_CHAR(ent,'MI')),(60*TO_CHAR(sal,'HH24')+TO_CHAR(sal,'MI'))
INTO wDiaEnt, wDiaSal, wEnt, wSal
FROM MY_TABLE_2
WHERE empid = wEmpId AND
TO_DATE(ent,'DD/MM/YY') = wFecha; --<< is the error here??
Any help would be apprecitated.
TIA,
Fred.-
----------------------------------------------------------------------
|
|
|
|
| Re: procedure [message #36527 is a reply to message #36522] |
Mon, 03 December 2001 04:44  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
ent is date or character datatype?. because one place you are using to_Char and other place to_date. i think it is date.
if so, try this in where clause
1) both are date fields, so you can simply write like
ent=wfecha
2)if you have time part in date, you have to change where clause
trunc(ent)=trunc(wfecha)
3) alternative to method 2 is
to_char(ent,'mm/dd/yyyy')=to_char(wfecha,'mm/dd/yyyy')
----------------------------------------------------------------------
|
|
|
|
| Re: procedure [message #36532 is a reply to message #36527] |
Mon, 03 December 2001 05:31 |
Frederik
Messages: 8
Registered: December 2001
|
Junior Member |
|
|
Thx for the reply.
Yep, ent is date with a time part ('DD/MM/YY HH:MM:SS').
I tried method 2 & 3, but it didnīt work...
ent is a Field from MY_TABLE_2 and wFecha is a variable I fetch from MY_TABLE_1 (itīs only date; format 'DD/MM/YY').
----------------------------------------------------------------------
|
|
|
|
| Re: procedure [message #36536 is a reply to message #36527] |
Mon, 03 December 2001 08:25 |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
i think some of records in my_table1 are not there in my_table2
look at following code and set serveroutput on before executing procedure.
OPEN Cursor1;
FETCH Cursor1 INTO wEmpId, wFecha;
WHILE (Cursor1%FOUND) LOOP
BEGIN
SELECT TO_CHAR(ent,'Day'),TO_CHAR(sal,'Day'),(60*TO_CHAR(ent,'HH24')+TO_CHAR(ent,'MI')),(60*TO_CHAR(sal,'HH24')+TO_CHAR(sal,'MI'))
INTO wDiaEnt, wDiaSal, wEnt, wSal
FROM MY_TABLE_2
WHERE empid = wEmpId AND
TO_DATE(ent,'DD/MM/YY') = wFecha;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Empid : '||wempid ||' wfecha : '||wfecha||' : record not found in my_table2');
end;
end loop
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
