| same timing should show message [message #431259] |
Mon, 16 November 2009 06:27  |
laith
Messages: 41
Registered: December 2008
Location: U.A.E
|
Member |
|
|
i have master -detail database blocks, in the detail i have field (ID) and i have start date for training and for how many days fields in master block.so let say the client has id (1234) take training on 22-nov-2009 for 3 days ...mean finished on 26-nov-2009 between 10:00 till 12:00. if the same client register in an another training in same days like 24-nov-2009 for 5 days from 11:00 till 15:00 (same timing)...should the system give message.
i wrote this code in (when-validate-item trigger) but it is not working ...
when-validate-item
============
begin
SELECT count(*) into :global.dummy
FROM PYPSTSM,PYPSTSD
WHERE PYPSTSD_REF_NO = PYPSTSM_REF_NO
AND PYPSTSD_PARTICIPANT_PIDM = :PYPSTSD_PARTICIPANT_PIDM
AND :PYPSTSM_DATE BETWEEN PYPSTSM_DATE AND (((PYPSTSM_DATE+PYPSTSM_NO_OF_DAYS)-1))
AND (:PYPSTSM_START_TIME BETWEEN (PYPSTSM_START_TIME+(:PYPSTSM_START_TIME-PYPSTSM_START_TIME))
AND (PYPSTSM_START_TIME+(:PYPSTSM_START_TIME-PYPSTSM_END_TIME))
OR :PYPSTSM_END_TIME BETWEEN (PYPSTSM_END_TIME+(:PYPSTSM_END_TIME-PYPSTSM_START_TIME)) AND (PYPSTSM_END_TIME+(:PYPSTSM_END_TIME-PYPSTSM_END_TIME)))
AND PYPSTSD_STATUS_IND ='R';
if :global.dummy >0 then
message('*ERROR* this person has registered an another Training.');
message('*ERROR* this person has registered an another Training.');
RAISE FORM_TRIGGER_FAILURE;
end if;
end;
|
|
|
|
| Re: same timing should show message [message #431284 is a reply to message #431259] |
Mon, 16 November 2009 07:51  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Before doing anything else I suggest you simplify the current query, becuase this:
(PYPSTSM_START_TIME+(:PYPSTSM_START_TIME-PYPSTSM_START_TIME))
And this:
(PYPSTSM_END_TIME+(:PYPSTSM_END_TIME-PYPSTSM_END_TIME))
|
|
|
|
|
|
| Re: same timing should show message [message #431297 is a reply to message #431259] |
Mon, 16 November 2009 09:15 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'm sure it does. I asked you to simplify the code to make it easier to see what the problem was.
Take your query, replace :PYPSTSM_START_TIME, PYPSTSM_START_TIME, PYPSTSM_END_TIME and PYPSTSM_END_TIME with the actual values from the db and the datablock and run it in sqlplus. The problem should become obvious.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
