4GL Query By Example and ANSI Databases

I'm trying to prepare a 4GL program for a read-only database query function. The users of this program are assigned a log-in that does not own the database being queried. That log-in is assigned connect and select priveleges on the database. I am having real trouble preparing a QBE select statement that works. I keep getting the message Table (%s) not selected in query. From the following code : LET q_txt = "SELECT ROWID FROM ""swkit"".requests WHERE ", q_txt CLIPPED WHENEVER ERROR CONTINUE OPTIONS SQL INTERRUPT ON MESSAGE "Searching ..." PREPARE q_sid FROM q_txt IF STATUS THEN CALL err_print(STATUS) OPTIONS SQL INTERRUPT OFF RETURN END IF DECLARE q_curs CURSOR FOR q_sid IF STATUS THEN CALL err_print(STATUS) OPTIONS SQL INTERRUPT OFF RETURN END IF LET q_cnt = 0 FOREACH q_curs INTO w_rowid LET q_cnt = q_cnt + 1 IF s_rowid_s(q_cnt) != 0 THEN ERROR " Memory allocation error, out of memory " OPTIONS SQL INTERRUPT OFF RETURN END IF CALL w_rowid_s(q_cnt, w_rowid) IF int_flag THEN EXIT FOREACH END IF END FOREACH It seems to be a problem with quoting the owner table name or the ROWID variable. I've tried many combinations to no avail. Any help will be greatly appreciated. -- jdryburn@ingr.com ---------------------------------------------------------------- Joe Ryburn | CIM Manager | Intergraph Corporation Ext 5639 | Manufacturing Integration | Huntsville, AL 35894 ----------------------------------------------------------------

Joe Ryburn (jdryburn@smt_6.b21.ingr.com) writes: >I'm trying to prepare a 4GL program for a read-only database query >function. The users of this program are assigned a log-in that does >not own the database being queried. That log-in is assigned connect >and select priveleges on the database. I am having real trouble >preparing a QBE select statement that works. I keep getting the message >Table (%s) not selected in query. >From the following code : > LET q_txt = "SELECT ROWID FROM ""swkit"".requests WHERE ", q_txt CLIPPED My guess would be that your CONSTRUCT is producing something which is not accepted by an ANSI database. Can you supply us with the DISPLAY output of q_txt? What I generally do with CONSTRUCT (adapted for MODE ANSI) is: CONSTRUCT q_where ON a.col01, a.col02, a.col03 FROM s_query.* LET q_txt = "SELECT ROWID FROM ""owner"".table a WHERE ", q_where CLIPPED The main point here is that I supply an alias for the table, and I use the alias in the CONSTRUCT statement. This pays biggest dividends when the query can be against one or two or more tables, because I can analyse the q_where string to see which tables need to be included in the actual SELECT statement I build. Maybe a variant of this will help you? Certainly, you should show us your complete q_txt; and I have to ask the obvious -- is the table requests owned by swkit? Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>