Concatenating an Oracle Array field

[krpurcel]

Hi,

I am trying to pull two fields called TICKET# and DESCRIPTION from an Oracle database using Crystal Reports2008. The DESCRIPTION field is in an array table.

When I pull TICKET# and DESCRIPTION to the details section of my report, the DESCRIPTION comes out in multiple lines per ticket as follows:

TICKET1 I need
TICKET1 this all
TICKET1 on one line
TICKET2 How do
TICKET2 I do that

I need the results to be:

TICKET1 I need this all on one line
TICKET2 How do I do that?

If I use the query engine in the Oracle db itself, it concatenates this perfectly and exports each DESCRIPTION as a single line. Of course, the db query capability is limited and very slow.

Can someone tell me how I can accomplish this using Crystal formulas?

Thanks!

 

[lbass]

Group on {table.ticket} and then create three formulas:

//{@reset} for the ticket group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {table.description};

//{@display} to be placed in the ticket group footer:
whileprintingrecords;
stringvar x;

I'm not sure whether the description field ends with a space when it continues into the record or whether you need to add a space:

{table.description} + " ";

...in the {@accum} formula.

Suppress the detail section and group header.

-LB

 

[krpurcel]

lbass,

As always, you are amazing. This works perfectly. Thanks very much for your expertise, and the fact that you are always willing to help!

You are appreciated!

 

[krpurcel]

LBass,

I've been using your solution and it works beautifully. Today I came across a situation where concatenating it all together created a string that was larger than 65,534 characters.

Any suggestions on how to deal with that?

Thanks!

 

[lbass]

Try changing the {@accum} formula to:

whileprintingrecords;
stringvar x;
stringvar y;
if len = "" then
(
if len(x)+len({table.description}) > 65534 then
y := y + {table.description} else
x := x + {table.description}
) else
if len+len({table.description}) > 65534 then
y := y else
y := y + {table.description}
;

Change the reset formula to:

whileprintingrecords;
stringvar x;
stringvar y;
if not inrepeatedgroupheader then (
x := "";
y := ""
);

Create an additional display formula {@displayy}:
whileprintingrecords;
stringvar y;

Then drop the two display formulas into a text box. Not sure which one should be first.

-LB

 

[krpurcel]

Thanks very much LBass. Once again it works perfectly. I want to be you when I grow up!