time_expired problem [message #176838] |
Sat, 10 June 2006 00:21 |
oracleproblem
Messages: 47 Registered: May 2006 Location: Dhaka
|
Member |
|
|
sir,
i have a table,The table has three fields(car_number,car_regdate,car_expdate).
suppose car_regdate means car registration date and car_expdate
means when date is over,
i want before 7days a warning message"the car registration date over
suppose 9/6/2006 and car registration number:6532478" until the date is not over this message display
please give me example,please urgent..........
masud............
|
|
|
Re: time_expired problem [message #176892 is a reply to message #176838] |
Sun, 11 June 2006 06:50 |
|
saadatahmad
Messages: 452 Registered: March 2005 Location: Germany/Paderborn
|
Senior Member |
|
|
Try this code in the When-New-Form-Instance trigger.
DECLARE
v_car_no NUMBER;
BEGIN
SELECT count(*)
INTO v_car_no
FROM you_table_name
WHERE ROUND(TRUNC(car_expdate)) - TRUNC(sysdate) <= 7
IF v_car_no > 0 THEN
Msg_Alert('Display your Alert here');
END IF;
END;
regards
[Updated on: Sun, 11 June 2006 06:50] Report message to a moderator
|
|
|
Re: time_expired problem [message #177339 is a reply to message #176892] |
Wed, 14 June 2006 04:24 |
oracleproblem
Messages: 47 Registered: May 2006 Location: Dhaka
|
Member |
|
|
sir,
your solution is not clear for me (what is count,i want to display car_registration no defore 7days,one day passing add new car_registration no untill 7 days,
alert is not clear where i called this alert i use show alert
or setprorerty.(your tiger is not work)
please give me example
Example
alert message
"car regno 771458,
time expitr date 21-6-2206.
car regno 587456
time expire date 22-6-2006."
|
|
|
|
|
Re: time_expired problem [message #178059 is a reply to message #178044] |
Mon, 19 June 2006 05:03 |
|
saadatahmad
Messages: 452 Registered: March 2005 Location: Germany/Paderborn
|
Senior Member |
|
|
I don't see anything in the code which you can't understand. Anyway, I'm giving you the test table data and the form and your required functionality is in the form. Just run the script and then run the form. You'll get the expiry notification by message and the registration numbers also which are going to be expired.
You can modify it according to your requirements. e.g: change the message to an alert.
SQL> CREATE TABLE test
2 (car_no NUMBER,
3 car_regdate DATE,
4 exp_date DATE);
Table created.
SQL> INSERT INTO test
2 VALUES(1, '12-JAN-2006', '25-JUN-2006');
1 row created.
SQL> INSERT INTO test
2 VALUES(2,'12-JAN-2006', '25-SEP-2006');
1 row created.
SQL> INSERT INTO test
2 VALUES(3, '12-JAN-2006', '20-JUN-2006');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM test;
CAR_NO CAR_REGDATE EXP_DATE
---------- ------------------ ------------------
1 12/JAN/06 25/JUN/06
2 12/JAN/06 25/SEP/06
3 12/JAN/06 20/JUN/06
Code in When-New-Form-Instance Trigger:
DECLARE
v_car_no NUMBER;
CURSOR c1 IS
SELECT car_no
FROM test
WHERE ROUND(TRUNC(exp_date)) - TRUNC(sysdate) <= 7;
c2 c1%ROWTYPE;
BEGIN
SELECT count(*)
INTO v_car_no
FROM test
WHERE ROUND(TRUNC(exp_date)) - TRUNC(sysdate) <= 7;
IF v_car_no > 0 THEN
OPEN c1;
LOOP FETCH c1 INTO c2;
MESSAGE(c2.car_no||' is going to expire');
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END IF;
END;
I hope it is clear this time.
regards,
Saadat Ahmad
|
|
|
|
|