Re: Can I sort on a lookup? i4gl basics

On Wed, 12 Nov 1997, Nelson Fredsell wrote: > I'm trying to display data from two tables and want to make use > of the asterisk (*) convention when defining RECORD. Also, > I want to sort on the column coming from the subservient/child > table. > > I'm able to use the asterisk in the SQL statement: > > SELECT table1.*, table2.col1 > FROM table1, OUTER table2 > ORDER BY table2.col1, table1.col1 Yes. > I haven't see a correlation to this for the DEFINE RECORD statement. That's because there isn't one. > DEFINE pr_table1 RECORD LIKE table1.* > won't let me integrate table2.col1. (Keyword THRU doesn't seem to be > an option as it is with SCREEN RECORD.) Correct. > Can I avoid explicitly naming all the fields in table1? Yes, but only by deciding to use two variables, one a record and one a simple variable (or you could use a second record if you wanted to). DEFINE pr_form1 RECORD LIKE Table1.* DEFINE pr_sortcol LIKE Table2.Col1 Or: DEFINE pr_form1 RECORD LIKE Table1.* DEFINE pr_form2 RECORD col1 LIKE Table2.Col1 END RECORD Your SELECT would then be: SELECT table1.*, table2.col1 INTO pr_form1.*, pr_sortcol FROM table1, OUTER table2 ORDER BY table2.col1, table1.col1 Or: SELECT table1.*, table2.col1 INTO pr_form1.*, pr_form2.* FROM table1, OUTER table2 ORDER BY table2.col1, table1.col1 Why are you sorting by a column which can be null? Does that really make sense. Also, I note that there is no WHERE clause, so you are doing a Cartesian Product of the two tables and the OUTER makes no difference unless there are zero rows in Table2. > DEFINE pr_form1 RECORD > col1 LIKE table1.col1, > col2 LIKE table1.col2, > ... > col1 LIKE table2.col1 > END RECORD > > Originally (hours ago), I defined a record on table1, then did a > lookup on table2.col1. Then I realized how nifty it would be to sort > on this looked up column. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>