Re: distinct query in forms 3
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. GoetheReceived on Tue Mar 12 1996 - 00:00:00 CET