Re: Reading a temp table

iftikhar_khan@mailcity.com wrote: > > Hi Everybody > > I have a question on reading a temp table in Informix 4GL. In my > Informix 4GL code, I am taking some input from user and based on that > input I am creating a temp table and puting data into it from other > tables. At any time this temp table may have one or five or seven or > as many columns as possible. Now I want to read each column's data > value. I am preparing a query (SELECT * FROM myTempTable) and > declaring a cursor for this query. Now I want a loop of 'foreach' > mycursor into ...... . But I donot have variables for into clause > because I donot know how many I need and of what type. How can I solve > this problem? Is there anyway of defining variables dynamically in > Informix 4GL? > Please help. An alternative to Peter's method. BTW, I note Art's knee-jerk ESQL call, which was my first reaction as well. But I smelled some dynamic SQL on the way so I backed away. Suppose the maximum is 10 columns to get back. DEFINE perm_dummy array[10] of char(18) In a loop, set these all up to contain the word "DUMMY", including the quote marks. DEFINE col_names array[10] of char(18) These will be column names to list in your select statement. But before starting to piece together the SELECT statement, copy them all from perm_dummy[*]. While deciding what columns you need to select, copy these column names into succeeding entries in col_names[]. Suppose you need 4 columns this time: col_names[1-4] will be set with the necessary column names while col_names[5-10] will contain the quoted string "DUMMY". You are now ready to piece together the SELECT statement. In a loop, keep appending entries from col_names[] (don't forget the comma separators and to omit the comma after the last one). The select statement now looks like: SELECT col1, col2, col3, col4, "DUMMY", "DUMMY", "DUMMY", "DUMMY", "DUMMY", "DUMMY" FROM (whatever tables) WHERE (yada yada...) Always have 10 variables to receive the values from the select statement. Depending on 3 or 5 or 7 or 10 columns really needed, the variables corresponding to real column names will have the desired values. The remainder of the variables will contain the string DUMMY (without the quotes). Sound reasonable? No? Hey, you can always go back to the ESQL, which is probably the most correct (IMO) method. Good luck. -- -- Jake (Retrospectively realizes there is no future in hindsight) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+