Re: distinct query in forms 3

From: Antony Sampson <sampson_at_dtf.sa.gov.au>
Date: 1996/03/12
Message-ID: <3c7cc$e52b.9f_at_MURDOCH>#1/1


jthomas_at_atc.ameritel.net (Jim Thomas) wrote:

>I hope someone can help me with this. I want to populate a simple form
>(SQL Forms3) to show 15 records at startup. I can not use a key-startup
>trigger with execute_query; as trigger text because I want distinct values.
 

>I tried the following as the key-startup
 

> select distinct a, b, c, d, e
> into :a, :b, :c, :d, :e
> from t;
 

>This compiles with no problem. But when executed I get only 1 record into
>the form with the error message 40735-Too Many Rows. I have talked to people
>locally who have a great deal more experience than I and they say it can't
>be done. What do you think?
>Thanks

Thomas,

This is achieved fairly easilly.
In key startup you need to define a CURSOR which comprises the query you are interested in. Look in your pl*sql manual on how to use cursor for loops.

Go to the first_record in the block;
Open the cursor.
Fetch first row into variables or screen fields

Loop until <cursor_name>%notfound

   do a next_record
   fetch next row into variables or screen fields. End loop

note that this is psuedo code, not real code.

Effectively you go down 1 row and return a row of the query into that row's fields until there are no more rows to return.

If you have trouble email me and I will provide more detail.

cheers,
Tony



Tony Sampson
DBA/software terrorist, South Australian Government Financing Authority sampson_at_dtf.sa.gov.au
                   The unnatural, that too is natural.
                                  Goethe
Received on Tue Mar 12 1996 - 00:00:00 CET

Original text of this message