How to loop through query result set and execute specific code block depending on column recordcount-CodePudding

Once TADOQuery.Open (cte query) I would like to iterate over the result set and depending on whether there is only one record with check_id(50001, 50003) to execute one block of code. In case when there are two or more records with the same check_id(50002) to execute different block of code.

Result set structure example.

row_num	buyer_id	amount	check_id
1	10001	25	50001
1	10001	30	50002
2	10002	10	50002
1	10003	10	50003

Much appreciated

CodePudding user response：

You can use Group By and Count in the SQL query to get a result like below.

SQL:

SELECT check_id, COUNT(check_id) as check_id_count
FROM my_table
GROUP BY check_id
ORDER BY check_id;

Result:

check_id	check_id_count
50001	1
50002	2
50003	1

Now you can iterate the records like that:

Delphi code

cte.SQL.Text := 'SELECT check_id, COUNT(check_id) as check_id_count FROM my_table GROUP BY check_id ORDER BY check_id';
cte.Open;

while not cte.Eof do
begin
  case cte.Fields[1].AsInteger of
    1: Foo(cte.Fields[0].AsInteger)
    2: Bar(cte.Fields[0].AsInteger)
    // add more when necessary
  else
    FooBar; // every other count of check_id
  end;
  cte.Next;
end;