| Procedure giving Wrong results........... [message #4725] |
Fri, 10 January 2003 04:04  |
BABU SRSB
Messages: 42
Registered: June 2002
|
Member |
|
|
Hi All,
i was very new to PL/SQL.
I created one procedure, which is giving quite BAD result to me. Please suggest me, where i was wrong.
The Procedure is :
-----------------------------------------------------
CREATE OR REPLACE procedure pr_va
(emp_code IN VARCHAR2) IS
tHour number(2);
tCount number(5);
tDate Date;
begin
dbms_output.put_line('The empcode is '|| emp_code);
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH')) INTO tHour FROM DUAL;
dbms_output.put_line('The Time is '|| tHour);
SELECT TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YY'),'DD-MON-YY') INTO tDate from dual;
dbms_output.put_line('The Date is '|| tDate);
SELECT COUNT(EMP_CODE) into tCount FROM TB_DROP_ACTUAL WHERE RDATE =tDate AND ROUTE_NO NOT LIKE 'D%' AND EMP_CODE=trim(emp_code);
dbms_output.put_line('The count is '|| tCount);
end;
SQL> exec pr_va('D00002');
The empcode is D00002
The Time is 5
The Date is 10-DEC-02
The count is 4829
PL/SQL procedure successfully completed.
---------------------------
But If i am executing same thing in SQL,
It is giving different result, Which is correct actually.
SQL> SELECT COUNT(EMP_CODE) FROM TB_DROP_ACTUAL
2 WHERE RDATE ='10-DEC-02'
3 AND ROUTE_NO NOT LIKE 'D%'
4 AND EMP_CODE=trim('D00002');
COUNT(EMP_CODE)
---------------
170
------------------------------------------------------
So, please guide me, where i was wrong.
Thanks in advance.
Yours,
Babu.
|
|
|
|
| Re: Procedure giving Wrong results........... [message #4726 is a reply to message #4725] |
Fri, 10 January 2003 04:36  |
mani
Messages: 105
Registered: September 1999
|
Senior Member |
|
|
Hi,
I can suggest few things ..it might help you..but not sure..just try it out n lemme know....
SELECT COUNT(EMP_CODE) into tCount FROM TB_DROP_ACTUAL WHERE to_date(RDATE,'DD-MON-YY') = (SELECT TO_DATE(SYSDATE,'DD-MON-YY') FROM DUAL) AND ROUTE_NO NOT LIKE 'D%' AND EMP_CODE=trim(emp_code);
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
