Concatenate Values into a Comma-Delimited String

[mercenguk]

Hi All,

I am trying to write an Interbase (Version 6.5) stored procedure (or view) that returns a key field and count followed by comma-separated string derived from a recordset containing multiple rows.

For example, my data is as follows :

Machine Centre Machine Name
C/Lathe Colch2000
C/Lathe HardingeHC3
CNC Mill Cinc2
CNC Mill Cinc3
CNC Mill BlueMat
CNC Mill RedMat

I need the stored procedure to return the following

Machine Centre Count Machine Names Machine Names
C/Lathe 2 Colch2000,HardingeHC3
CNC Mill 4 Cinc2,Cinc3,BlueMat,RedMat

I have tried using FOR SELECT to concatenate the Machine Name field into a string, but have not been able to get it to work.

I would be grateful if anyone has any suggestions.

Many thanks,

MercEng

 

[unclejimbob]

I have just arrived at work so not able to give a decent reply right now, will post again in approx 10 hours with a proper answer if you haven't heard back from anyone else by then or found the solution yourself. The approach would be to do what you are currently thinking, order the result set by centre and machine name, set up a string var that is reset to an empty string on change of centre. I guess there are at least two options here...

just have 1 for..select and create 3 vars, one to hold the centre, one to hold the count and one to hold the comma delimited string of machine names and provide appropriate reinitalisation code within the loop to provide the necessary output

or

have 2 nested for..selects, the outer one a GROUP BY that provides the machine centre and count and the inner one to provide the 'detail' line with the comma-delimted machine names.

hth

ujb

 

[unclejimbob]

Here is my sample code - no doubt there is a more elgant way to do it but this works.

[tt]
/***START***/
DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';

DECLARE EXTERNAL FUNCTION substr
CSTRING(80), SMALLINT, SMALLINT
RETURNS CSTRING(80) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

create table Machine_Centre
(Machine_Centre_Name varchar(200));
commit;

create table Machine_Name
(Machine_Name_Name varchar(200),
Machine_Centre_Name varchar(200)
);
commit;

insert into Machine_Centre (Machine_Centre_Name) values ('C/Lathe');
commit;

insert into Machine_Centre (Machine_Centre_Name) values ('CNC Mill');
commit;

insert into Machine_Name (Machine_Name_Name,Machine_Centre_Name) values ('Colch2000','C/Lathe');
commit;

insert into Machine_Name (Machine_Name_Name,Machine_Centre_Name) values ('HardingeHC3','C/Lathe');
commit;

insert into Machine_Name (Machine_Name_Name,Machine_Centre_Name) values ('Cinc2','CNC Mill');
commit;

insert into Machine_Name (Machine_Name_Name,Machine_Centre_Name) values ('Cinc3','CNC Mill');
commit;

insert into Machine_Name (Machine_Name_Name,Machine_Centre_Name) values ('BlueMat','CNC Mill');
commit;

insert into Machine_Name (Machine_Name_Name,Machine_Centre_Name) values ('RedMat','CNC Mill');
commit;

create procedure csvtest
returns (sMachine_Centre_Name varchar(200), iCount int, sCSV varchar(10000))
as
declare variable sMachine_Name varchar(200);
begin
for select mc.Machine_Centre_Name, count(mc.Machine_Centre_Name) from Machine_Centre mc join Machine_Name mn
on mc.Machine_Centre_Name = mn.Machine_Centre_Name
group by mc.Machine_Centre_Name
into :sMachine_Centre_Name, :iCount
do
begin
sCSV = '';
for select Machine_Name_Name from Machine_Name where Machine_Centre_Name=:sMachine_Centre_Name into :sMachine_Name
do
begin
sCSV = sCSV || sMachine_Name || ',';
end
sCSV = substr(sCSV,1,strlen(sCSV)-1);
suspend;
end

end;
commit;

select * from csvtest;
/***END***/

[/tt]

 

[mercenguk]

Unclejimbob

Many thanks for taking the time to reply (twice!)

I copied your example and adapted it to reflect the structure of my data and it works a treat.

The only problem I'm having is with the line to strip off the trailing comma (sCSV = substr(sCSV,1,strlen(sCSV)-1).

I have defined both the external functions in the UDFs section of the database but the compiler flags substr as being an invalid function.

Are these two functions included in the ib_udf.dll as standard or do I have to write these myself and if so, how do I reference them?

Actually, I can live with the trailing comma, but it would be nice to understand external functions as I haven't used them before.

Once again, thanks for your help.

Regards,

MercEng

 

[unclejimbob]

Yes these functions reside in ib_udf.dll and these should work 'right out of the box' on a standard IB install. I'm a bit hazy on this point myself for earlier versions of IB but I think the problem is simply that it can't find the location of your DLL. I use IB 7.5 but I distinctly remember having to copy ib_udf.dll to the \bin folder (or vice versa) in order to get it to work on pre 7 versions.

If you are getting something similar to 'entrypoint not found' then I recommend you try:
1) altering the declaration to include the .DLL extension thus...

DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf.dll';

2) copying the ib_udf.dll from lib to bin

I think you also need to copy the other DLL ib_util.dll into the \bin folder as well.

ujb