Re: Question about Constructs and Prepares in Informix 4GL

Dan, Idea #1) Use dynamic SQL to prepare 3 queries. Each uses the same where clause (the one obtained from the construct) but goes against one of the three tables. For example: DEFINE sql_stmt CHAR(256) LET sql_stmt = "SELECT * FROM view1 WHERE ", <your where clause> CLIPPED PREPARE p_stmt FROM sql_stmt DECLARE c_cur1 CURSOR FOR p_stmt LET sql_stmt = "SELECT * FROM view2 WHERE ", <your where clause> CLIPPED PREPARE p_stmt FROM sql_stmt DECLARE c_cur2 CURSOR FOR p_stmt LET sql_stmt = "SELECT * FROM view3 WHERE ", <your where clause> CLIPPED PREPARE p_stmt FROM sql_stmt DECLARE c_cur3 CURSOR FOR p_stmt You can then use the 3 cursors (c_cur1, c_cur2, c_cur3) to select from the corresponding table/view. Idea #2) Super Union! Construct a statement like this: DEFINE sql_stmt CHAR(256) LET sql_stmt = " SELECT * FROM view1 WHERE ", <your where clause> CLIPPED, " UNION ", " SELECT * FROM view2 WHERE ", <your where clause> CLIPPED, " UNION ", " SELECT * FROM view3 WHERE ", <your where clause> CLIPPED PREPARE p_stmt FROM sql_stmt DECLARE c_super CURSOR FOR p_stmt The advantage here is that you select from all 3 tables as if they were one table. If this is a lookup function, this is much easier to handle. It may suffer from performance problems and you can't create an update cursor this way. It just depends on your application. (BTW, you can add an ORDER BY to sort the results of the 3 selects together.) Is this what you were looking for? (In what way was this an idiot question?!) On 26 Mar 1996 20:49:47 GMT, Dan Michaelis <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. > ====================================================================== Craig Kickel | Unix/C/Database Consultant ckickel@cyberhighway.net | <This space intentionally left blank> ======================================================================