×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Concatenate Values into a Comma-Delimited String

Concatenate Values into a Comma-Delimited String

Concatenate Values into a Comma-Delimited String

(OP)
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

RE: Concatenate Values into a Comma-Delimited String

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

RE: Concatenate Values into a Comma-Delimited String

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


/***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***/

RE: Concatenate Values into a Comma-Delimited String

(OP)
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

RE: Concatenate Values into a Comma-Delimited String

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close