Re: Help: DES/2K, constrains

Hi Jurrian -

I suggest the following (see sections below):

InfoAd - Jurrian Beuker <infoad_at_xs4all.nl> wrote in article <5lp24g$ohs$1_at_news2.xs4all.nl>...
>
> I am Jurrian Beuker from holland. I develop software with Form V3.0
> and ReportWriter 1.1. (in Holland) At this moment i'm using designer
> for the first time, and i have two (small i think for you) questions :
>
> When i have 2 table with a 1 to N relation, and i'm bilding a Form
> where the first table gets 4 rows and de second (N) 10 rows and i
> place them om de samen page.
> When i navigate to the second (N) block, i can not see with master
> record belong to these details. If i put the N table on a sepetare
> page, I can include contect items from the master record.
>
> Is there a way to indicate the record in a block with more than 1
> master record with master record is actief if i have moved to the
> detail records ? (in de same page).

Possible solution:

I've had a similar situation as you with this. A possible answer is to have a non-base table (displayed) field in your first block that displays an identifier of the record in the first (master) block i.e. the primary key or some data that uniquely identifies that displayed row. Populate this field (for example) using a procedure called from (for example) a PRE-RECORD trigger. This field's location could be at the top of the detail block, thus indicating which master record is being referenced when you go to the second block.

>
> My second question is relatied to validation. in a table i have 3
> fields, ID, BEGIN_DATE, END_DATE. all field must be entered. the
> periode between BEGIN_DATE and END_DATE must be uniek.
>
> suppose i have the records :
> ID BEGIN_DATE END_DATE
> 1 1-jan-97 2-mai-97
> 1 1-jun-97 20-jun-97
> There record are valid.
> invalid is
> 1 1-mai-97 20-mai-97
> Becouse this record have 1 en 2 mai in comment with the first record.
> the period my not overlap with a oder period with the samen id.
>

A possible solution is to have a PL/SQL function that verifies the supplied parameters, that does something like the following. Call the function from where you want to validate the data:

e.g. IF check_dates('01-MAI-97','20-MAI-97',1) THEN

        message('ERROR: 0101 Clashing dates');
        END IF;

Suggested PL/SQL function:

DEFINE OR REPLACE FUNCTION check_dates
(

p_begin_date 	IN 	DATE;
p_end_date	IN	DATE; 
p_id		IN	NUMBER)  RETURN BOOLEAN AS

cursor c1 IS
SELECT begin_date,end_date
FROM date_table
WHERE id = p_id;

date_clashes BOOLEAN := FALSE;

BEGIN
FOR c1_row IN c1 LOOP

	IF    (p_begin_date BETWEEN
	         c1_row.begin_date AND c1_row.end_date)
	OR  (p_end_date BETWEEN
	         c1_row.begin_date AND c1_row.end_date)
	THEN
		date_clashes := TRUE;
		EXIT;  -- exit when TRUE found for any row for this id.
	ELSE	
		date_clashes := FALSE;
	END IF;

Hope this helps you out.         

• Martyn Cavett Senior ORACLE Analyst LGT Asset Management London, UK. home: cavett_at_globalnet.co.uk work: Martyn.Cavett_at_gtplc.com

These views are my own and not those of my employer. Normal conditions apply. Received on Mon May 26 1997 - 00:00:00 CEST