Two passes of a recordset

General
1

How can I go thru a recordset two times ? (without querying the DB twice) ?

IS there an alternative to rs.movefirst ?

2

Depends on the database type. FWIW you wouldn’t need to worry about anything like this if you used ActiveRecord and ARGen.

3

go thru a recordset two times
Care to explain what you really want to do ? Reading a RecordSet twice or thice the SAME Record # ?

rs.movefirst
Read Record Unique ID = 0 (or 1) ?

4

I can try to explain why I want to read the recordset twice…

it is a rs that comes from a fairly complex query of many tables with joins and unions and where’s…

I need to count the occurrences of a certain label which has a value but as at the same time assign a value to that label which is the SUM of all the values of the occurrences.

Example

PLANE 44
PLANE 21
PLANE 10
CAR 5
CAR 10
BIKE 1

RESULT =
PLANE 75
CAR 15
BIKE 1

I could do this in the query itself, but the query is already complicated. I’d rather not mess with it.

So I thought , in the first pass of the recordset, create the dictionary (assign the sum of the values to each label).

In the second pass I can customize how do I show the records in a listbox, using the data I already collected in the first pass.

(I need to create as many columns as keys are in the dictionary, no more no less)

Did I make myself clear ?

There’s probably a better way to do this, but I’m hitting the limits of my knowledge…

5

by the way… it’s an SQLite DB

6

FWIW you wouldn’t need to worry about anything like this if you used ActiveRecord and ARGen.

@Tim Parnell That’s correct. I would have to worry about the learning curve. :slight_smile:

7

SQLite supports MoveFirst. Why won’t that work for you?

8

I think it works… For some reason I thought there was another better way to do it…

9

Write yourself a small class which populates a dictionary by reading the recordset only once. A dictionary gives you ultimate freedom in finding, moving and manipulating records.

10

I made a class that mimics a recordset, but stores the values in a local array.
that way you read first the records, and then do what you need with the array.

for your particular case, I would make the sqlquery to sum the occurences
but then make an update query with the values calculated before.
that way you go through the recordset only once.

11

@Jean-Yves Pochez but then make an update query with the values calculated before… I’m not sure I understood. Could you please elaborate?
Thanks

12

are you using SUBQUERY in your SQL? you say the main query is too complex, so basicaly do not change it. but encapsulate it .
example

SELECT key,sum(abc) as abc,sum(def) as def
FROM (

<my complex query>

)
GROUP BY key
13

[quote=387244:@Roman Varas]@Jean-Yves Pochez but then make an update query with the values calculated before… I’m not sure I understood. Could you please elaborate?
Thanks[/quote]
in fact you could do all of it in a single sql update query…

sqlexecute something like this:

UPDATE items_table A SET A.item_total = (SELECT SUM(B.item_value) FROM items_table B WHERE B.item_name=A.item_name)
14

thanks guys. Your input is very valuable.

15

@Dave S : Dave, if I do that, can I use the data from the SUBQUERY (as well)?

(I’m not sure what the return of that query would be)

@Jean-Yves Pochez : I don’t want to write anything to the DB… it’s just a select…

16

No… but that was prompted by

What you COULD do … depending on how much control you have over the database… is create a VIEW that is comprised of your “complex” query… then you could

CREATE VIEW myVIEW AS <complex query SQL code>;

SELECT key,sum(abc) FROM myVIEW GROUP by key;

SELECT * from myVIEW;

Note : the CREATE VIEW only needs to be done ONCE, and it will live in the database and will reflect ALL changes made to any table that view refers to…

WANT? or CAN?