Re: Forms: RG property "Record Group Query Size" - what is it ?

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Wed, 25 Aug 2004 11:08:33 +0200
Message-ID: <2p33gaFg0dobU1_at_uni-berlin.de>

"Frank Piron" <empty_at_zero.nil> schrieb im Newsbeitrag news:opsc906dr2m0et4w_at_news.online.de...
> Hi,
>
> Am Wed, 25 Aug 2004 08:31:18 +0200 schrieb Jan Gelbrich
> <j_gelbrich_at_westfalen-blatt.de>:
>
> > "Frank Piron" <empty_at_zero.nil> schrieb im Newsbeitrag
> > news:opsc8s24cgm0et4w_at_news.online.de...
> >> Hi,
> >>
> >> Am Tue, 24 Aug 2004 16:50:40 +0200 schrieb Jan Gelbrich
> >> <j_gelbrich_at_westfalen-blatt.de>:
>
> > Thank You, Frank, for Your comment.
> >
> > But in a way, it does not make much sense to me.
> > Because: in a classic Name Search Problem,
> > the shorter the name part that is typed into the item field that is
> > calling
> > a LOV,
> > the more Names would match the pattern (e.g. 1000 matches for short
> > pattern
> > "Smi%"),
> > and if the full name is typed, only few names would be left to fit
> > (e.g. only 3 for long pattern "Schlottermeier%" )
> > and to be chosen from a LOV.
>
> You wrote, that with smaller RGQS values your LOV did not work
> for long patterns.
> Did it not work at all or only with poor performance?
>

It did not work, going to IF SHOW_LOV --> FALSE, what should be TRUE.

OK, here are more details:

Imagine a Form with among others
2 text items, :PERSON.ID and :PERSON.NAME in a canvas Both can be used for Name Search

PERSON.ID.KEY-NEXT-ITEM and
PERSON.NAME.KEY-NEXT-ITEM is:

   Execute_Trigger('Key-Exeqry');

The block PERSON showing one record only is table-based, but it is only used for searching. No DML allowed in this form in that context

The Key-EXEQRY of Block PERSON is thus (generalized):

DECLARE ...

BEGIN    --1. Search over ID
   If :Person.Id Is Not Null
   Then

      --dann hamwa die Id schon: gibs auch den Boten mit den Parametern dazu ?

• ... get the person over id - easy: a cursor fills all items up.

   --2. Search over Name oder NamePattern%    ElsIf :Person.Name Is Not Null
   Then

         p_testmessage('before SHOW_LOV');
         If Show_Lov('LOV_BOTENSUCHE') --Return Person.Id (Non-DBO)
         Then --TRUE
            --Parameter
               ... get the Person, Address, Parameter data and so on
         Else --FALSE: LOV cancelled

-->> goes in here
-->> when RG Size set too small

            Raise ErrBoteNichtGefunden;
         End If;

   End If;

   Synchronize;

EXCEPTION
   When ErrBoteNichtGefunden
   Then

      P_Fehlermeldungen('No Person found!');

   When Others
   Then

      P_Fehlermeldungen('Fehler Key-Exeqry: '||sqlerrm);

END; And the LOV based on an RG is a simple query:

Select Pers.Id,

   Pers.Name||', '||Pers.VName Fullname,

   Str.Post_Code,
   Str.City,
   Str.Street||' '||Adr.House_Nr||' '||Adr.House_Bs Str

From

   Person Pers,
   Parameter Par,
   Adress Adr,
   Street Str

Where Pers.Id = Par.Pers_Id
And   Pers.Id = Adr.Pers_Id
And    Str.Id = Adr.Str_Id
And Adr.Adr_Typ = 272

And Par.Company = :Steuer.Company
And Par.Location = :Steuer.Location
And Trunc(Sysdate) Between Par.Valid_From And Par.Valid_To And Trunc(Sysdate) Between Adr.Valid_From And Adr.Valid_To And Pers.Upper_Name Like Stored_Procedures.Fu_Upper(:Person.Name)||'%' Order By 2

which I tested before the LOV: Names were found, but the LOV said "no persons found". Received on Wed Aug 25 2004 - 11:08:33 CEST