RE: Question about Constructs and Prepares in Informix

If I read you correctly, one possible solution could be: function declare_the_cursor(view_name, where_clause) define view_name char(10), where_clause char(1900), prepare_str char(2048) let prepare_str="select ", your_field_list, " from ", view_name, " where ", where_clause prepare search_stat from prepare_str declare search_curs cursor for search_stat end function .... function do_search() .... call declare_the_cursor("view1", your_search_conditions) foreach search_curs into... .... end foreach free search_stat .... call declare_the_cursor("view2", your_search_conditions) foreach search_curs into... .... end foreach free search_stat .... call declare_the_cursor("view3", your_search_conditions) foreach search_curs into... .... end foreach free search_stat .... end function. ciao, marco. ____________________________________________________________________________ rem radioterapia, which I immeritately manage, seldom agrees with what I say marco greco (Catania, Italy) Work: marcog@ctonline.it rem radioterapia 39 95 447828 fax 446558 (was mar.greco@agora.stm.it) Achea 39 95 503117 --- On 27 Mar 1996 18:56:04 EDT dcmichae@accessus.net wrote: } }Folks, I've got a true idiot's question here, and I'm hoping that }someone will simply advise me that I'm being silly, and tell me }what I need to do here; I'm stumped. } }I've got 3 databases in a single OnLine instance. Each database tracks }patient name and birthdate. In 2 of those databases, those values are }tracked in separate tables; in the remaining one, they're tracked in the }same table. The object here is to allow a user to perform a query }against all of these tables without having to enter the query-by-example }data more than once. Here's what I've done so far, and I'm not committed }to this solution. It was just the first thing I could think of: } }Created synonyms from database 1 and database 2 for tables A and B into }database 3. In other words, I've gotten all of the tables that I want to }select from into the same database. Then, I've created a view for }each combination of tables A and B (synonymed into the same database) so }that I've got the same structure for fields in all tables that I want to }select from. The structure now looks like this: } } } }+-----------+ +-----------+ }| Table A | ===> | Table A1 | }| Database 1| | Database 3| + +-----------+ }+-----------+ +-----------+ | | View 1 | } >===> | Database 3| }+-----------+ +-----------+ | +-----------+ }| Table B | ===> | Table B1 | + }| Database 1| | Database 3| }+-----------+ +-----------+ } }+-----------+ +-----------+ }| Table A | ===> | Table A2 | }| Database 2| | Database 3| + +-----------+ }+-----------+ +-----------+ | | View 2 | } >===> | Database 3| }+-----------+ +-----------+ | +-----------+ }| Table B | ===> | Table B2 | + }| Database 2| | Database 3| }+-----------+ +-----------+ } }+-----------+ +-----------+ +-----------+ }| Table C | ===> | Table C1 | =====> | View 3 | }| Database 3| | Database 3| | Database 3| }+-----------+ +-----------+ +-----------+ } } }Having gotten here, with all the ending views having the same structure }(as far as field names go), I used a construct statement to create a }query string to use against each of the views. So far, so good, and it }worked. But now, I go to prepare the select statement, and here's where }I have my problem. If I prepare the statment against view1, I can't use }it against view2, and so forth (at least, I don't know how I can). So I }thought that I'd create the view with the same name, and execute the }prepared statment against the view 3 times. This also seems to not work }(I keep getting no rows returned by the select, and I believe that's }because there are no matching rows in the first view). Finally, I }thought I'd prepare the select 3 separate times, but I get a compile-time }error saying I can't use the same name for a cursor more than once per }module. } }If anyone has any creative ideas around this, I'd really appreciate the }help. } }TIA, } }-- Dan