Hi,

> 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...
>
> 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???

Without 0 prefix :

SELECT
((ID_ITEM * 1000) + ID_SUBITEM) AS ITEMNO
FROM ITEMS
ORDER BY
ID_ITEM,
ID_SUBITEM

With 0 prefix :

SELECT
SUBSTRING(((1 * 1000000) + (ID_ITEM * 1000) +
ID_SUBITEM) FROM 2 FOR 6) AS ITEMNO
FROM ITEMS
ORDER BY
ID_ITEM,
ID_SUBITEM


Untested !

Regards,
Arno