Subject Re: MDX tags and sql builder From Mervyn Bick <invalid@invalid.invalid> Date Wed, 4 Dec 2024 12:19:21 +0200 Newsgroups dbase.getting-started On 2024/12/03 11:56, Mervyn Bick wrote: > For the second .sql file do exactly the same.  After you have saved > the .sql file open it in the Sourcecode editor and add DISTINCT > immediately after SELECT. Oops.  My mind wasn't properly in gear when I wrote that.  :-( This will NOT create a rowset that is identical to using a unique index on the surname field.   If one has, say, entries for Smith as a surname with John, Mary, Peter and Paul as first names the unique index will only include the record, with all it's fields, for the first Smith in the table.  Select DISTINCT * .... will include all the Smiths if one is using localSQL. It is possible to create the required rowset using "proper" SQL such as, say, Firebird but due to the limitations of localSQL I'm afraid you're going to have to create a temporary table.  To ensure that the temporary table is always up to date put the code in the header section of any form where you need to use this. Something like the following. if file('whatever_temp.dbf')     drop table whatever_temp endif use whatever copy struc to whatever_temp use q = new query() q.sql = 'select * from whatever' q.active = true q1 = new query() q1.sql = 'select * from whatever_temp' q1.active = true nSize = q.rowset.fields.size do while not q.rowset.endofset     cSeek = q.rowset.fields['surname'].value     if not q1.rowset.applylocate("surname = cSeek")        q1.rowset.beginappend()        for n = 1 to nSize            if  q.rowset.fields[n].type <>'AUTOINC'                q1.rowset.fields[n].value := q.rowset.fields[n].value            endif        next        q1.rowset.save()     endif     q.rowset.next() enddo q1.active = false q.active = false If you have an autoinc field in your table be aware that the values for the field in the temporary table won't match the values in the original table.  If this is a problem use ALTER TABLE to drop the autoinc field from the temporary table and then use ALTER TABLE to replace the field as an integer type before you append the records.  Remove the test for the AUTOINC field. Mervyn.