comma-delimited field as group, with one complication 1

[PhilB2]

Just in case someone has assigned more than one value to a multi-selection field in our database, I need to parse the values and treat the values as a group.

To clarify: if the field were a single-selection field, I could simply make a group out of it and I'd be done, but I can't because there may be an item or two with more than one values in the field.

The complication is that since the values are numeric, I need to use SQL to look up each value in another table.

Is there a somewhat painless way of doing this, or must I give this task to someone with more expertise and time?

 

[hilfy]

If I understand you correctly, the values are numeric, but they've been concatenated into a comma-delimited string. Is that correct?

You'll need to write a query that pulls back all of the data that you need for your report. This will go into a "Command" in the Database Expert.

For this example I'll call the table with the concatenated field "Table1" and the table you're joining to "Table2". Your query will look something like this:

Select
 <fields>
from Table1
  inner join Table2
    on InStr(Table1.ConcatField||',',Table2.ID||',') > 0

I used the extra commas to prevent linking on data that doesn't really match. For example, let's assume that 10 and 100 are both valid ID fields, the value in the field from Table1 is "100,23" and the value in the field from Table2 is 10.

Instr('100, 23', '10') = 1 so that record from Table2 would incorrectly join to the record in Table1. However, InStr('100,23,' '10,') = 0. (NOTE: this is based on Oracle, I can't remember whether MS SQL starts strings at 0 instead of 1, so you may need to play with this if that is your database.)

You would then group on the appropriate field in Table2 to show the data the way that you describe.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[PhilB2]

The values are numeric and comma-delimited in an Oracle table. Each numeric value is a key in a second table that provides the desired text value.

That, however, doesn't change the approach, does it? I would simply use the second table as the group?

 

[PhilB2]

I have never written a Crystal command (Crystal Reports 9), but the Help seems to be clear on how to do it. The approach you describe seems straightforward, but I'm not confident I can pull it off. One of the most experienced developers here stated that he's tried in Crystal to do this and had to resort to creating a program instead of using Crystal.

 

[PhilB2]

Do I need to tie the command to the group? Either way, is there a simple way to describe how/why this works?

 

[hilfy]

A Command is simply a SQL select statement. You want to create it so that it pulls ALL of the data for the report. If you combine a Command with tables in the Database expert, Crystal will not be able to tell the database to join the data together and instead will pull all of the data into memory then join and filter it. If you're using a client/server database like Oracle, this will slow your report down considerably.

I would create the query in something like PL/SQL Developer, Toad or SQL*Plus, depending on what you have available. Make sure the query works there before pasting it into Command Editor in Crystal.

If you use parameters to filter the data for your report, you need to create them in the Command Editor, NOT in the main report! There are a couple of tricks to this:

- If the parameter returns a single string value, you need to enclose it in single-quotes in the query. It will look like this:

table.field = '{?StringParam}'

- If the parameter can contain multiple values, you do NOT need to enclose it in parentheses. It will look like this:

table.field in {?MultiParam}

If you use your existing report and you have already created the parameters in the report, delete them and recreate them in the command.

The reason that you do this is to pass the work along to the database as part of the query. If you leave the params in the main report and use them in the Select Expert, Crystal will again pull in all of the data and filter it in memory, slowing down the report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[PhilB2]

In trying the SQL you suggested, I realized that I needed another step and view.

Table UBR_BADGIR has a CLOB field (DIALOGUE_APPLICATION) containing comma-delimited numeric values. Most records contain one value; some records contain two or three values. Crystal 9 won’t read the CLOB field, so I created a view converting the field thus:

, dbms_lob.substr(badgir.ts_dialogue_application,4000,1) "Dialog_Application"

The numeric values are keys in table TS_SELECTIONS, that also contains text values in field TS_NAME. In Toad, I was able to retrieve the text values using your template as follows:

Select TS_SELECTIONS.TS_NAME
from VTMTRACK
inner join TS_SELECTIONS
on InStr(VTMTRACK.DIALOG_APPLICATION ||”,”,TS_SELECTIONS.TS_ID ||”,”) > 0;

However, I get ‘Query Engine Error: ORA-00911:invalid character’ when I try to create the command in Crystal.

If I can get past this error, I still have questions:

1) Why would I not try to combine the select statement above with the truncation statement in my existing view (VTMTRACK)?
a) Is the reason performance, or that it won’t work?
b) If I can’t mix my views and the command in the Database Expert, do I need to instead insert all the relevant selects from my view into the command?
Also, below is what I’m trying to do, now that I have a better grasp of some of the components:

1) Create a group in Crystal, from the values in TS_NAME (above), displaying TS_NAME in the group header.
2) Create other groups from FILENAME and REVISION from view VVCACTIONS (View Version Control Actions, derived from table VCACTIONS), and one other group and a detail record from fields in view VTMTRACK.

I wanted to attach some screen shots, but apparently cannot.

 

[PhilB2]

Thank you again.

I succeeded in including your suggested formula in a view. The report is slow, but manageable so far:

, DIALOG_APPLICATION
, EXSTREAM_APPLICATION

, dbms_lob.substr(badgir.ts_dialogue_application,4000,1) "Dialog_Application"
, selectf.TS_NAME "Exstream_Application"

JOIN
teamtrack.ts_selections selectf on InStr(badgir.ts_dialogue_application ||',',selectf.TS_ID ||',') > 0

 

[PhilB2]

Well, not so fast. Only two groups out of 5 appear on the report.

 

[PhilB2]

Back on track! I changed my link to a left outer join, and the values appeared.