| pls take a look? urgent need help. [message #86795] |
Tue, 02 November 2004 16:54  |
eboy
Messages: 17
Registered: August 2004
|
Junior Member |
|
|
HAI.
let say i got a table call KELUAR_MASUK
TABLE KELUAR_MASUK
- ICNO -
- CHECK_IN -
- CHECK_OUT -
- TARIKH -
- -
- -
- -
- -
- -
- -
- -
- -
ICNO = VARCHAR2(10), CHECK_IN/OUT = VARCHAR2(10), TARIKH DATE
i put below statement on a button call LOGIN
BEGIN
select login_id, name into :keluar_masuk.icno, :keluar_masuk.nama
from jhd.staff
where login_id = :login2.ic;
GO_BLOCK('KELUAR_MASUK');
:KELUAR_MASUK.tarikh := to_char(SYSDATE,'DD-MON-YYYY');
END;
DECLARE
STAFF_CHECK_IN exception;
BEGIN
select CHECK_IN, CHECK_OUT into :keluar_masuk.CHECK_IN, :keluar_masuk.CHECK_OUT
from keluar_masuk
where icno = :keluar_masuk.icno AND TARIKH = TO_CHAR(SYSDATE, 'dd-MON-yyyy');
if :keluar_masuk.CHECK_IN is null then
raise STAFF_CHECK_IN;
end if;
-----if data not exist in the database, below statement will automatically generated----------
exception
when no_data_found then
select to_char(sysdate, 'hh24:mi:ss') into :keluar_masuk.check_in from sys.dual;
commit;
RAISE Form_Trigger_Failure;
when STAFF_CHECK_IN then
select to_char(sysdate, 'hh24:mi:ss') into :keluar_masuk.check_out
from sys.dual;
delete from keluar_masuk where icno = :keluar_masuk.icno;
commit;
RAISE Form_Trigger_Failure;
end;
LET SAY, there is no record on table KELUAR_MASUK
when i run my program with above statement, it will automatically stored the 1st record on the database like example below
- ICNO -
- CHECK_IN -
- CHECK_OUT -
- TARIKH -
- 1234 -
- 07:45:30 -
- 11:10:20 -
- 02-nov-2004 -
- -
- -
- -
- -
my question is, how to insert another record with the same icno = 1234 and with the same TARIKH = 02-nov-2004 but the time for check_in/out is not the same like example below
- ICNO -
- CHECK_IN -
- CHECK_OUT -
- TARIKH -
- 1234 -
- 07:45:30 -
- 11:10:20 -
- 02-nov-2004 -
- 1234 -
- 13:10:20 -
- 15:30:10 -
- 02-nov-2004 -
can anyone modify my statement above or show me another way of doing it. thanks in advance for solve my problem
regards
eboy
thanks once again n may god bless you
|
|
|
|
| help needed? [message #86820 is a reply to message #86795] |
Sat, 06 November 2004 15:00  |
eboy
Messages: 17
Registered: August 2004
|
Junior Member |
|
|
HAI.
let say i got a table call KELUAR_MASUK
TABLE KELUAR_MASUK
ICNO CHECK_IN CHECK_OUT TARIKH
ICNO = VARCHAR2(10), CHECK_IN/OUT = VARCHAR2(10), TARIKH DATE
i put below statement on a button call LOGIN
BEGIN
select login_id, name into :keluar_masuk.icno, :keluar_masuk.nama
from jhd.staff
where login_id = :login2.ic;
GO_BLOCK('KELUAR_MASUK');
:KELUAR_MASUK.tarikh := to_char(SYSDATE,'DD-MON-YYYY');
END;
DECLARE
STAFF_CHECK_IN exception;
BEGIN
select CHECK_IN, CHECK_OUT into :keluar_masuk.CHECK_IN, :keluar_masuk.CHECK_OUT
from keluar_masuk
where icno = :keluar_masuk.icno AND TARIKH = TO_CHAR(SYSDATE, 'dd-MON-yyyy');
if :keluar_masuk.CHECK_IN is null then
raise STAFF_CHECK_IN;
end if;
-----if data not exist in the database, below statement will automatically generated----------
exception
when no_data_found then
select to_char(sysdate, 'hh24:mi:ss') into :keluar_masuk.check_in from sys.dual;
commit;
RAISE Form_Trigger_Failure;
when STAFF_CHECK_IN then
select to_char(sysdate, 'hh24:mi:ss') into :keluar_masuk.check_out
from sys.dual;
delete from keluar_masuk where icno = :keluar_masuk.icno;
commit;
RAISE Form_Trigger_Failure;
end;
LET SAY, there is no record on table KELUAR_MASUK
when i run my program with above statement, it will automatically stored the 1st record on the database like example below
ICNO CHECK_IN CHECK_OUT TARIKH
1234 07:45:30 11:10:20 02-nov-2004
my question is, how to insert another record with the same icno = 1234 and with the same TARIKH = 02-nov-2004 but the time for check_in/out is not the same like example below
ICNO CHECK_IN CHECK_OUT TARIKH
1234 07:45:30 11:10:20 02-nov-2004
1234 13:10:20 15:30:10 02-nov-2004
can anyone modify my statement above or show me another way of doing it. thanks in advance for solve my problem
regards
eboy
thanks once again n may god bless you
|
|
|
|
| Re: help needed? [message #86829 is a reply to message #86820] |
Mon, 08 November 2004 04:29 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Though your question is not very clear,but I feel that you are running into Primary key constraint violation error.
In order to achive this add a Seq no in your table and make it a part of PK.
HTH
Regards
Himanshu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
