SQL help needed 
Author Message
 SQL help needed

Hi all!

Can anyone help me wit SQL query. I need to retrieve data from table and
ADD column with continuously increasing numbers from 1 to N (where N is
numer of rows in sql query). Is here some SQL magician ???

Thank very much

Martin



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL help needed

I suppose that, in fact, you want to put this result in a report. If
so, you don't need to do anything through SQL. Just insert a control
(in QuickReport, TQRDBCalc) wich summarizes Count() in the detail band
and every record will show an increased number.

If this is not your situation, please let me know.

EBR



Quote:
>Hi all!

>Can anyone help me wit SQL query. I need to retrieve data from table and
>ADD column with continuously increasing numbers from 1 to N (where N is
>numer of rows in sql query). Is here some SQL magician ???

>Thank very much

>Martin




Wed, 18 Jun 1902 08:00:00 GMT  
 SQL help needed

Unfortunatelly I do not need it for report :(

I need to add record numbers to some output query (dataset). And to use
this
number as record number (because .RecNo property doesn't work with remote
database - I'm using MS SQL Server 6.5 via ODBC). I need to add this
continously increasing numbers not into table but just as a new column with
nubers from 1 to n to output of query.



Quote:
> I suppose that, in fact, you want to put this result in a report. If
> so, you don't need to do anything through SQL. Just insert a control
> (in QuickReport, TQRDBCalc) wich summarizes Count() in the detail band
> and every record will show an increased number.

> If this is not your situation, please let me know.

> EBR



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL help needed



Quote:
>Unfortunatelly I do not need it for report :(

>I need to add record numbers to some output query (dataset). And to use
>this
>number as record number (because .RecNo property doesn't work with remote
>database - I'm using MS SQL Server 6.5 via ODBC). I need to add this
>continously increasing numbers not into table but just as a new column with
>nubers from 1 to n to output of query.

Well, this case is more complex. In this case, try to create an SQL
statement with a new column, like:

Select 0 As Sequence, YourTable.*, From YourTable;

Then write a little piece of code that changes the contents of
'Sequence'. I recommend you to deliver query results to a temporary
table (via TBatchMove), because SQL itself should return a read-only
column.

I hope this helps more...

EBR



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL help needed

Quote:

> Hi all!

> Can anyone help me wit SQL query. I need to retrieve data from table and
> ADD column with continuously increasing numbers from 1 to N (where N is
> numer of rows in sql query). Is here some SQL magician ???

> Thank very much

Hi Martin,

I don't know exactly but I think that the SQL query can hold only one real
SQL statement which you can add by clearing the SQL query and adding the
statement after making sure that the query is closed.

  MyQuery.Close;
  MyQuery.SQL.Clear;
  MyQuery.SQL.Add('SELECT * FROM Orders');
  MyQuery.SQL.Add('WHERE Company = ''Siemens''');
  MyQuery.Open;

I'm no SQL magician but I hope I could help you a little bit.

Ciao
     Jens-Peter



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL help needed

Problem is, that I do not know what to write as Sequence ;). I can't make
temp table due to multiuser enviroment. I just need this number to show
users on which record of query (which they selected) they are using now
(just row number of some selection). But NOT number of table row but row
number of SELECTION. I need to add something like increment column to
SELECT query (but I do not know how :)

Thank you

Martin



Quote:


> Well, this case is more complex. In this case, try to create an SQL
> statement with a new column, like:

> Select 0 As Sequence, YourTable.*, From YourTable;

> Then write a little piece of code that changes the contents of
> 'Sequence'. I recommend you to deliver query results to a temporary
> table (via TBatchMove), because SQL itself should return a read-only
> column.



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL help needed

Ok, let's go to write an example. Warning: this example is being
written by mind, so the bugs always appear, but I believe it will give
you good ideas.

As I said before, make your query to generate a new column. Don't
activate it, because my intention is to transfer the result to a
temporary table on your drive c:\.

Select 0 As Sequence, YourTable.*, From YourTable;

The sequence is: a TBatchMove process moves your records from the
query to a paradox table. Then, you use the paradox table with the new
column and put the numbers there.

This is not a "smart" solution, but it works. Maybe you experience
some performance problems, specially if the amout of data is very
large.

The code can be as follows:

Procedure CreateTempTable;

var TableParadox : TTable;
        Transfer : TBatchMove;
        { I don't think you'll get more than 65535 records }
        Numbering : Word;

begin
        TableParadox := TTable.Create(self);
        Transfer := TBatchMove.Create(self);

        { If you want to use in a network, generate a random number
          rather than a constant string }
        TableParadox.TableName := 'Temp.DB';

        TableParadox.DatabaseName := 'c:\';
        { if you don't supply the. DB extension,
        set the TableParadox.TableType := ttParadox }
        TableParadox.TableName := 'TableName.DB';

        with Transfer do begin
                Source := YourQuery;
                Destination := TableParadox;
                Mode := batCopy;
                { Now execute the BatchMove itself }
                Execute;
        end;

        TableParadox.Open;
        TableParadox.First;

        Numbering := 1;
        while not TableParadox.Eof do begin
           TableParadox.FieldByName('Sequence').AsInteger := Numbering
           Inc( Numbering );
           TableParadox.Next;
        end;

        TableParadox.Close;
        TableParadox.Free;
        Transfer.Free;
end;

Last, you must assure your datasource is now pointing to the new
table.

I hope this really helps!

EBR



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. CAST in Local SQL? SQL Help needed

2. SQL Help Needed!

3. Nested SQL help needed

4. dBase and UPDATE SQL help needed

5. SQL help needed

6. SQL Help Needed

7. SQL Neophite needs some SQL Help Please...

8. **** HELP **** needed with a paramatized SQL QUery in Delphi 2

9. Need help with SQL under Delphi

10. SQL Join help needed (for MS Access convert)

11. Hey, TEAMB...Need some real help with TTable and CreateTable in SQL Back end

12. Need Help on Writing contents of a TMemo Using SQL

 

 
Powered by phpBB® Forum Software