let me write a saved proc that may collect all area values from multiple rows into a single output variable (maybe varchar(some_length)). It might appear strange solution but i have quite positive its the only person i'm able to use at this situation im in. I haven't used Firebird before and saved procs look way diverse from in other well-known db systems. My Firebird is 1.5 and dialect 3 (unsure what it really means). So perhaps someone may help me having a formula example.
The next procedure does that which you describe:
SET TERM !!; CREATE PROCEDURE concat_names RETURNS (concat VARCHAR(2000)) AS DECLARE VARIABLE name VARCHAR(100); BEGIN concat = ''; FOR SELECT first_name || ' ' || last_name FROM employee INTO :name DO BEGIN concat = concat || name || ', '; END END!! SET TERM ;!! EXECUTE PROCEDURE concat_names;
However I question the knowledge of the solution. How are you aware the VARCHAR is lengthy enough for the rows inside your preferred dataset?
It is simpler and far better to operate a query to come back the end result for an application row by row. Every application programming language has techniques to concatenate strings, but more to the point they've got more flexible techniques to handle the development of information.
Incidentally, "dialect" in Firebird and InterBase describes a compatibility mode which was introduced to ensure that programs produced for InterBase 5.x can function with later versions of InterBase and Firebird. Which was almost 10 years ago, and AFAIK you shouldn't have right now to use anything less than dialect 3.
You need to test for null values when concatenating, here's a good example for 2 fields along with a separator together:
CREATE PROCEDURE CONCAT( F1 VARCHAR(385), F2 VARCHAR(385), SEPARATOR VARCHAR(10)) RETURNS ( RESULT VARCHAR(780)) AS begin if ((:f1 is not null) and (:f1 <> '')) then result = :f1; if ((:f2 is not null) and (:f2 <> '')) then if ((result is not null) and (result <> '')) then begin if ((:separator is not null) and (separator <> '')) then result = result||separator||f2; else result = result||f2; end else result = f2; suspend; end