At 10:18 PM 04-10-02 -0300, you wrote:

>I have little doubt about using Interbase/Firebird.
>Supose a table ITEMS with 2 columns: ID_ITEM smallint not null, and
>ID_SUBITEM smallint not null.
>I need to concatenate the 2 columns like this:
>
>select cast(ID_ITEM as char(3)) || cast(ID_SUBITEM as char(3)) as ITEMNO
> from ITEMS
> order by ID_ITEM, ID_SUBITEM
>
>I intend to generate results like this:
>0101
>0102
>0103
>0201
>0202
>0301
>and so on...

SMALLINT could potentially have three significant digits. Why are you
casting these numbers as CHAR(3) literals if you want a four-digit result?

>but, I am obtaining only this results:
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
>and so on...
>
>I need stuff results with zeros further then spaces.
>How can I do this??? Can anyone help me???

One way to do this would be to make two char(3) proxy columns in your ITEMS
table which will be populated by triggers whenever you insert a new row.

ALTER TABLE ITEMS ADD STR_ITEM CHAR(3);
ALTER TABLE ITEMS ADD STR_SUBITEM CHAR(3);

If you want to restrict ID_ITEM and ID_SUBITEM to two digits, then you must
add CHECK constraints to prevent creation of three-digit numbers:

ALTER TABLE ITEMS ADD CONSTRAINT Check_item_length
CHECK (ID_ITEM < 100);
ALTER TABLE ITEMS ADD CONSTRAINT Check_subitem_length
CHECK (ID_SUBITEM < 100);

In the example below we'll assume you don't want this and work with
CHAR(3).....

You need to declare the STRLEN user-defined function from the ib_udf
library for this:
DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';

Then create the trigger something like this:
CREATE TRIGGER BI_ITEMS FOR ITEMS
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE string CHAR(3);
BEGIN
string = '000';
IF (NEW.ID_ITEM IS NOT NULL) THEN
BEGIN
string = CAST(NEW.ID_ITEM AS CHAR(3));
WHILE (STRLEN(string) < 3) DO
BEGIN
string = '0'||string;
END
NEW.STR_ITEM = string;
END
string = '000';
IF (NEW.ID_SUBITEM IS NOT NULL) THEN
BEGIN
string = CAST(NEW.ID_SUBITEM AS CHAR(3));
WHILE (STRLEN(string) < 3) DO
BEGIN
string = '0'||string;
END
NEW.STR_SUBITEM = string;
END
END ^

Then, at run-time, your query will be like this:

select STR_ITEM || STR_SUBITEM as ITEMNO
from ITEMS
order by ID_ITEM, ID_SUBITEM

Another alternative would be to get your original output set by way of a
selectable stored procedure that performs processing similar to the trigger
directly upon the SMALLINT values at run-time.

heLen