Qty Total Qty
1 4
2 4
3 4
4 4
I understand the While loop would be something like:
While Qty <= ALP3.PROPERTYVALUE Do
<<output>>
Loop
So, your "quantity" column is not actually a quantity of some real data (like quantity of containers in cargo ship), but a row number in some your output report/grid.
And then what you want is limiting the output "rowset" - matrix, table, grid - to some N first rows.
Well, that is exactly how it is done, asking for the first rows only.
Select FIRST(4) column1, column2, column3
From table 1
Where condition1 and condition2 or condition3
See the "first" clause in documentation: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html
Also see "Limiting result rows" chapyer in Wikipedia: https://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows
You can also use "window functions" starting with Firebird version 3, but they are somewhat overkill for the simple task of "only give me first N rows".
Now, there is one more method that provides for embedding a totally voluntary condition, but that is from "ugly hacks" toolsets and does not work in a typical situation when several simultaneous connections form different client programs are running. You can use a "generator" as part of the WHILE clause:
Select .....
Where (GEN_ID(cancel_generator_name, 0) = 0) AND ( ...you normal conditions...)
You set the generator value to 0 before the query, and your client evaluates some conditions of your choice while reading the data, and when it wants to - from some another SQL command library object it issues the generator change command, which would immediately skip the rest of the query. However while sometimes this is a useful technique, but only in very specific rare situations.
Since Mark seems to be better guessing than me, then some outlines for future guesswork.
SP is a standard abbreviation for SQL Stored Procedure. Firebird's Execute Block is essentially an anonymous non-persistent SP.
So, we start with a persistent and named SP.
create or alter procedure SEQ (
FROM_1_TO integer not null)
returns (
COUNTER integer)
as
begin
counter = 1;
while ( counter <= from_1_to ) do begin
suspend;
counter = counter + 1;
end
end
Select 1, s.counter from rdb$database, seq(5) s
CONSTANT COUNTER
1 1
1 2
1 3
1 4
1 5
The next question would be how to
- join the table with SP (stored procedure) dependent upon specific table row values
- avoid SP being executed with NULL parameter values
The answer is - LEFT JOIN, as shown in the FAQ: http://www.firebirdfaq.org/faq143/
CREATE TABLE T2 (
ID INTEGER NOT NULL PRIMARY KEY,
TITLE VARCHAR(10) NOT NULL,
QTY INTEGER NOT NULL
);
INSERT INTO T2 (ID, TITLE, QTY) VALUES (1, 'aaaa', 2);
INSERT INTO T2 (ID, TITLE, QTY) VALUES (2, 'bbbb', 5);
INSERT INTO T2 (ID, TITLE, QTY) VALUES (3, 'ccccc', 4);
Select * from t2 t
left join seq(t.qty) s on 1=1
ID TITLE QTY COUNTER
1 aaaa 2 1
1 aaaa 2 2
2 bbbb 5 1
2 bbbb 5 2
2 bbbb 5 3
2 bbbb 5 4
2 bbbb 5 5
3 ccccc 4 1
3 ccccc 4 2
3 ccccc 4 3
3 ccccc 4 4
If you would have many different queries on different tables/fields that would require this rows-cloning added then having a dedicated counter-generating SP makes sense.
However if you only need this rather exotic rows cloning once, then maybe polluting a global namespace with an SP you would never need again would be less of a good idea.
It seems one can not select from an EB, though: Select from execute block?
So you would have to make a specific ad-hoc EB exactly for your select statement. Which, arguably, might be the very reason d'etre for anonymous non-persistent EB.
execute block
returns (ID INTEGER, TITLE VARCHAR(10), QTY INTEGER, COUNTER INTEGER)
as
begin
for select
id, title, qty from t2
into :id, :title, :qty
do begin
counter = 1;
while
(counter <= qty)
do begin
suspend;
counter = counter + 1;
end
end
end
However the data access library your application uses to connect to Firebird should understand then that while this query is not SELECT-query it still returns the "rowset". Usually they do, but who knows.
