Re: Problem with dynamic select statement

From: DanHW <danhw_at_aol.com>
Date: 12 Apr 1999 00:48:07 GMT
Message-ID: <19990411204807.16128.00001767_at_ng-ft1.aol.com>


>hi, I have peculiar problem. I have two forms in forms5.0. In the first form
>i will be building a select statement dynamically at run time. Even the where
>clause is built dynamically. Also the table names from which i will be
>retrieving the data will also be decided at run time. My problem is that i
>have to send this select statement to second form where i have to display
>the matching records. The second form to which i will be passing the
select
>statement will have no base table blocks. It will have only one control
>block. My main problem is that since i will be passing the select
statement
>as a global variable how do i execute the query in the control block.I don't
>know how to retrieve the records based on the select statement. My second
>problem and the biggest problem is that i have to display the selected
>records in a hierarchial fashion. Just for an eg i am giving the structure
>union a branch 1 station 1 10 station 2 5 total 15 branch 2
station
>3 2 station 4 12 total 14 total 29 union b ... total ... 25

>total 165
>
>I don't know how at all i can display in a hierarchial manner in forms 5.0.
>The no of records will always be variable.
>
>I really don't know how to proceed with this.I am totally stuck up and i have
>to complete this in 1 week. Any help is greately appreciated. Thanks in
>advance
>
>aruna l
>

I have not done this, but with Forms 5.0 you can base a block on the results of a procedure. It sounds like you could write a package that has one procedure that 'loads' the query as a package-level cursor. and another function that then returns the result back. This is probably the one that you base your block on.

As I said, I have not done this yet, you might try it out and see if it works.

The other option involves creating a view that the form uses to be compiled against (it just needs a name and the correct columns number/types). At run-time, create (using DBMS_SQL) a private view that returns the correct data.

Hope these suggestions help out.

Dan Hekimian-Williams Received on Mon Apr 12 1999 - 02:48:07 CEST

Original text of this message