Hi

Is there a simple way to export a query result set in the same way as we
can export a table?

I have a solution that involves creating a temporary table with fields
that match the query and :-

EITHER reprocessing the query 'INSERT INTO temp ... SELECT ,,....
OR reading through the query inserting records into the temp table

.... then using EXPORT TABLE to make the export file. Quite a lot of code
involved.

Am I missing something or is this something worth suggesting to Tim?

I imagine it would be a doddle (as I have much faith in Tim) for Elevate
to include an extension like:-

INSERT INTO FILE "ArchivedOrders.CSV" IN STORE MyStore FORMAT DELIMITED
SELECT * FROM Orders
WHERE OrderDate BETWEEN DATE '2006-01-01' AND DATE '2006-12-31'

Perhaps this is already available, but I can't see it in the manuals.

Cheers

Jeff

Jeff,

> I have a solution that involves creating a temporary table with fields
> that match the query and :-

You can create a (temporary) table from the query in one step, then
export it, e.g.:

CREATE TEMPORARY TABLE "ExportTable" AS
SELECT * FROM MyTable
WITH DATA

Uli

Jeff,

Did you mean export to CSV?

This is one I use to make dozens of CSV files, one at a time. Note the peculiar delimiter; maybe you could use the 'standard' delimiter. There is also an Import method somewhere here - let me know if you need it.

Regards, Peter.

function TfrmMain.ExportOneTable(STblName, SFileName: string): boolean;
const
MAKE_CSV = 'EXPORT TABLE "%s" TO "%s.csv" IN STORE "CSVStore" '+
           ' DELIMITER CHAR #8 QUOTE CHAR ''"'' DATE FORMAT ''yyyy-mm-dd'' '+
           ' TIME FORMAT ''hh:mm:ss n'' AM LITERAL ''AM'' PM LITERAL ''PM'' '+
           ' DECIMAL CHAR ''.'' BOOLEAN TRUE LITERAL ''True'' FALSE LITERAL ''False'' '+
           ' INCLUDE HEADERS MAX ROWS -1';
begin
Result := False;
EDBDatabase1.Close;
EDBDatabase1.Database     := OPERDB;
EDBDatabase1.DatabaseName := OPERDB;
EDBQuery1.DatabaseName    := OPERDB;
EDBDatabase1.Open;
try
 EDBQuery1.SQL.Clear;
 EDBQuery1.SQL.Add(Format(MAKE_CSV, [STblName, SFileName]));
 EDBQuery1.ExecSQL;
except
 raise;
end;
Result := True;
end;

Peter,

his question was, how to export a query result, not how to export a
table generally.

Uli

Jeff


Uli's approach will work nicely. If its one you want to do frequently then consider a view - they can also be exported.

Roy Lambert

On 23/04/2015 5:43 p.m., Uli Becker wrote:
> You can create a (temporary) table from the query in one step, then
> export it, e.g.:
>

Yes, thanks Uli.  That is what Have done in some instances - I was just
hoping for a cleaner way.

Cheers

Jeff

On 23/04/2015 9:57 p.m., Roy Lambert wrote:
> Jeff
>
>
> Uli's approach will work nicely. If its one you want to do frequently then consider a view - they can also be exported.
>
> Roy Lambert
>

Thanks Roy