asked on

Evaluating Data with Multiple Values using a Multiple Value Parameter

Hello Experts –

I have a database that tracks product complaints. Each complaint may have one or more lot numbers associated with it. I have an SQL command that grabs the lot numbers (called “Command.Lots”) from various table locations and stuffs them into a string, separated by a comma . . . like this: “A001, A002, C001, D005”.

I now need to run a report for a specified set of 200 or so lots – which is my input parameter - and if any of those lots match any of the values in {Command.Lots}, then I want to pull that row of complaint data. Right now, I hard code a formula in Crystal like this below:

If Instr({Command_.Lots},"C001") > 0 then {Command_.Event Number} else
If Instr({Command_.Lots},"G005") > 0 then {Command_.Event Number} else
If Instr({Command_.Lots},"Q013") > 0 then {Command_.Event Number} else
If Instr({Command_.Lots},"A001") > 0 then {Command_.Event Number} else
If Instr({Command_.Lots},"P301") > 0 then {Command_.Event Number} else . . . etc.

I’d prefer the user to enter their own lots, so I tried to use a multi value input parameter called {?Lot Numbers} and evaluate each record using this formula for arrays below, but it takes way too long with 200 lots to evaluate for each record:

Local NumberVar i;

for i := 1 to UBound ({?Lot Numbers}) do
if " " + {?Lot Numbers} [ i ] in " " + {Command.Lots} then
exit for;

i <= UBound ({?Lot Numbers})

Is there a way to do this more efficiently either in Crystal itself, or using a Stored Procedure and/or SQL function – which could then be referenced within Crystal? Is there a way to do it so that the user could enter one long string of values separated by a comma instead of adding one lot at a time into the Crystal Parameter field?

Thank you!
- Marcos

Mike McCracken

I can't think of an easy way to do what you want in Crystal. If you can provide a list of values for the parameter then the user could select from the list but that isn't much of an improvement for input.

There is no easy way to compare 2 lists of values that is faster then comparing each value.

Let me make sure I understand how this works.

The system creates a list of values - Command.LOTS “A001, A002, C001, D005”
Does command lots change with each record?

The user enters a list of values - A004, D005, C003

Since both have D005 - this record is selected for the report?

mlmcc

vasto

Check this link:
http://www.r-tag.com/Pages/MultivalueParametersWithSQLSP.aspx

It contains 4 methods to send multiple values to a stored procedure.

As far as I can see the best way will be to send to the stored procedure a comma separated value -> parse it to a table and join it with the other table which contains you data

Marcos27

ASKER

mlmcc - correct. And yes, the Command.Lots is different for each complaint record.

Vasto - If I understand this correctly, I will create a table using a Table Valued Function which will contain all of my parameter lots - listed out as separate rows of data in a new table X. Then I will join table X to my real data set where lot# in X is in Command.Lots . . . and then pull the associated complaint record?

vasto

Yes, I believe this will take seconds . Just use the table-valued function to parse both comma-separated values and join the tables

vasto

You might also take a shortcut and bypass the generation of the comma separated string in your command. I guess your data is in a table structure and you are using a cursor or a isnull to create a comma separated string.

Marcos27

ASKER

I'm using a CTE and pulling lot fields using a Union, then stuffing them into a string . . .

stuff((select ', ' + [Lot Number] from CTE where [Event Number] = a.[Event Number] for xml path('')), 1, 1, '') as Lots

I understand what you're saying - why stuff and then parse, but ultimately I need the output to be in a comma separated string for each record. If there are 5 lots per record, I don't want to pull 5 separate rows.

vasto

OK , I see what you are doing. You can join the tables like this:

Table 1

ID Data
1 1,2,3,4,5
2 10,11,12,13

Parameters values 3,6,7 which will be parsed to a table like this
Table2

ID
3
6
7

SELECT ...
FROM Table1 t1
INNER JOIN Table2 t2 ON ',' + t1.Data+ ',' LIKE '%,' + t2.ID + ',%'

Mike McCracken

Will that result in duplicate records if 2 or more of the user entered values are in the LOT?

mlmcc

vasto

I guess you can use distinct

Mike McCracken

So long as you don't select the lot number field

mlmcc

vasto

If the lot number is selected there will be 2 records an this is normal ( again I guess). What will be the result with arrays ?

Mike McCracken

I think he is using it in the selection formula so a record should only be selected once since the formula is run once for each record.

mlmcc

vasto

I need to see the full SQL. The question is how to find matches and my answer it move this to the server an leave SQL to work for you. The same will be done in crystal but much slower

Kurt Reinhardt

Why are you concatenating the list of lots into a single string? That seems counterintuitive. Why not just list records, each of which with a lot number. Then, you could easily use a multi-value parameter without having to monkey with the SQL.

Marcos27

ASKER

Here’s the original SQL that produces complaint records with a string of lots (grabbed from various locations). The Subforms_Incident is a table that lists all “additional” lots per complaint record and looks something like this:

ParentID      LongText001
3815      C001
3815      A001
3815      A002
3815      D005

;with CTE as

(Select LongText001 as 'Lot Number', ParentID as 'Event Number' From dbo.Subforms_Incident where category = 'Lot Numbers'

Union

Select StandardText012 as 'Lot Number', IncidentID as 'Event Number' From dbo.Incident )

select distinct [Event Number], B.StandardText048 as "Product",
B.StandardText026 as "Event Code", B.Date_Incident as "Date Opened", B.CAAssignmentDept as "CAAssignment Dept",

stuff((select ', ' + [Lot Number] from CTE where [Event Number] = a.[Event Number] for xml path('')), 1, 1, '') as Lots

from ((CTE a LEFT OUTER JOIN Incident B ON a.[Event Number] = B.IncidentID)
LEFT OUTER JOIN CorrectiveAction ON B.CorrectiveActionID = CorrectiveAction.ActionID)

Where B.Category = 'Product Event Intake Report' and B.Void='False'

Marcos27

ASKER

If I don't need to string it together that's fine, but ultimately I need the record set to show that complaint ID 3815 has lots C001, A001, A002, and D005 . . . as one row of data that is pulled when I enter parameter lots A004, D005, C003.

Marcos27

ASKER

Vasto - help me out here. How do I join my SQL above to the table value function that parses out my string of input values into a table. The example from the link you sent is this - and I'm wondering how to join this - join on what?

CREATE FUNCTION [dbo].[fnTextToTable2008]
(@Data VARCHAR(MAX))

RETURNS @Tbl TABLE (ID VARCHAR(8000))

AS
BEGIN
DECLARE @Value VARCHAR(8000)

WHILE LEN(@Data) > 0
BEGIN
SET @Value = LEFT(@Data, ISNULL(NULLIF(CHARINDEX(',', @Data) - 1, -1), LEN(@Data)))
SET @Data = STUFF(@Data, 1, DATALENGTH(@Value) + 1, '')

IF RTRIM(@Value) <> ''
INSERT INTO @Tbl (ID) VALUES(@Value)
END

RETURN
END

vasto

try this:

;with CTE as 
(Select LongText001 as 'Lot Number', ParentID as 'Event Number' From dbo.Subforms_Incident where category = 'Lot Numbers'
Union
Select StandardText012 as 'Lot Number', IncidentID as 'Event Number' From dbo.Incident )

SELECT distinct t.* 
FROM (
	select distinct [Event Number], B.StandardText048 as "Product", 
	B.StandardText026 as "Event Code", B.Date_Incident as "Date Opened", B.CAAssignmentDept as "CAAssignment Dept", 
	stuff((select ', ' + [Lot Number] from CTE where [Event Number] = a.[Event Number] for xml path('')), 1, 1, '') as Lots
	from CTE a 
	  LEFT OUTER JOIN Incident B ON a.[Event Number] = B.IncidentID) 
	  LEFT OUTER JOIN CorrectiveAction ON B.CorrectiveActionID = CorrectiveAction.ActionID 
	Where B.Category = 'Product Event Intake Report' and B.Void='False' 
	) t INNER JOIN [dbo].fnTextToTable2008(@Parameter) p ON ',' + t.Lots + ',' LIKE '%,' + p.ID + ',%';

Open in new window

Marcos27

ASKER

OK, I see, so you join it on the ID field. I see now . . . INSERT INTO @Tbl (ID) VALUES(@Value). I was trying to join on @Value not seeing the (ID) there . . . got it. I'll give it a wirl and let you know how it goes. I'm going to create a Stored Procedure here that joins the two.

Marcos27

ASKER

Seems to work!! I'll have to test further and then create an input parameter in Crystal where my users can enter a string of values and run it. I want them to be able to enter "A00, C001" and pull all complaints with lots that contain "A00", so I adjusted the join like so:

ON ',' + t.Lots + ',' LIKE '%' + p.ID + '%';

This way, it doesn't limit to what's between the commas. I think this works. Let me know if you think otherwise . . .

vasto

If you want partial matches you can remove the commas

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

I think it might help to review what you're trying to do (as I understand it).

You have Complaints, and each Complaint can have one or more Lot Numbers, which are stored in separate rows. You want the user to be able to enter a list of Lot Numbers, and if a Complaint includes _any_ of those Lot Numbers, you want the report to show _every_ lot number for that Complaint (in a comma-separated list).

If you can get the data to include the desired Lot Numbers in separate rows, the report could handle combining them in a single string. It seems like that would be more efficient than combining them in the db and then trying to use LIKE to match substrings.

In simplified pseudo-code form, I think you want something like this:

Select * From Complaints
Where Complaint_ID In
(Select Complaint_ID From Complaints Where Lot_Number = <multi-value parameter>)

Does all of that sound correct?

Which version of CR are you using? I think I read that CR 2008 supports using multi-value parameters in Commands.

James

Marcos27

ASKER

I have to use Crystal 10. I’ve already tried to use multi valued parameters directly in Crystal and then evaluate the input array all within Crystal, and that takes too long. Sometimes I need to enter up to 200 lots. Also, I’d like to enter those lots as a string of values, separated by commas – which this new method allows me to do.

James – you have the business process correct. There can be an infinite number of lots to pull for each complaint record, and they are stored in two locations: In the Incident table, and in the Subform_Incident table, which is joined by Incident ID. This is why I have a Union query, because I need to list all lots per Incident from multiple locations. I just made that a Union All to improve performance, which worked. If there’s a better way to join the rows, evaluate, and then stuff the lots into a string for output, I’m all ears. However, the report is running for 200 lots in a matter seconds right now, so I’m pretty pleased.

In my example, I left out many fields in the select statement – including fields in the CorrectiveAction table, which is why that table is joined. Also, we have to pull “distinct” records between my original SQL (t) and the new table of parsed input parameters (p), because we don’t want to list records multiple times if the user enters two or more lots that show up for any one complaint.

PortletPaul

select distinct works across the entire row, the more columns you add the more it has to evaluate.

using select distinct often isn't the best - or only way - to arrive at a non-repeating result. don't forget 'group by' also produces unique rows.
or
if using select distinct, work with perhaps just unique keys in the deeper subqueries, and at the last moment join to the balance of the required information.

using union all is better - but please pay attention to where you are filtering this information, there isn't much point unioning data then excluding it - consider repeating some or all of the where clause to reduce the unioned results. Putting your where clause into the CTE will be of benefit every time you re-use the CTE(s) as well.

James0628

I don't know if it would be "better", but if each Lot Number was in a separate row, the report could be grouped by Complaint and formulas could concatenate the Lot Numbers for each complaint in a string variable and then output the result in the group footer. But if you're happy with the performance that you're getting now, there may not be any need to change things.

James

vasto

You can try also this one. It will avoid "LIKE" operator and will be faster. I don't expect a noticeable performance difference for datasets with few hundreds records

;with CTE as 
(Select LongText001 as 'Lot Number', ParentID as 'Event Number' From dbo.Subforms_Incident where category = 'Lot Numbers'
Union
Select StandardText012 as 'Lot Number', IncidentID as 'Event Number' From dbo.Incident )

select distinct a.[Event Number], B.StandardText048 as "Product", 
B.StandardText026 as "Event Code", B.Date_Incident as "Date Opened", B.CAAssignmentDept as "CAAssignment Dept", 
stuff((select ', ' + [Lot Number] from CTE where [Event Number] = a.[Event Number] for xml path('')), 1, 1, '') as Lots
FROM
  (
  	SELECT distinct a.[Event Number] 
  	FROM CTE a  
  	  INNER JOIN [dbo].fnTextToTable2008(@Parameter) p ON a.[Lot Number] = p.ID
  ) a
  LEFT OUTER JOIN Incident B ON a.[Event Number] = B.IncidentID 
  LEFT OUTER JOIN CorrectiveAction ON B.CorrectiveActionID = CorrectiveAction.ActionID
Where B.Category = 'Product Event Intake Report' and B.Void='False';

Open in new window

Table CorrectiveAction is not used in the SQL, but I left it because I don't know if this is your final SQL

PortletPaul

Bravo! use of distinct against one field (excellent) and avoiding LIKE (wonderful)
but UNION has crept back in :(

I realize I'm being a pain here, but there remain a few strong issues here - which not only affect performance but the validity of the results.

In the CTE, 2 sources of data are combined. That's necessary it seems, but in the very last part of the overall query some where conditions are imposed.

validity:
some rows of the CTE come from dbo.Subforms_Incident but the final where conditions may EXCLUDE THEM (i.e. may be affecting validity)
(Subforms+Incident) = CTE > join Incident > join corrective > where = no subforms

performance:
zero filtering is applied in the CTE - why not?
the CTE is much bigger than it needs to be (more i/o & memory consumed than needed)
a select distinct is performed across the CTE, it would be vastly faster if the CTE was smaller
The CTE is joined to an unindexed table (from function) also faster if CTE is reduced

The where clause:
2 left joins are used, but then the where conditions force the result back to inner joins
(left join to correctiveAction) then insist all records have a correctiveAction category of 'Product Event Intake Report'

This is NOT just a performance thing, but:
BOTH dbo.Subforms_Incident & dbo.Incident should have relevant where clauses INSIDE the UNION ALL

The final joins and where clause needs some thought e.g.:
LEFT JOIN Incident B ON a.[Event Number] = B.IncidentID
LEFT JOIN CorrectiveAction ON B.CorrectiveActionID = CorrectiveAction.ActionID
AND B.Category = 'Product Event Intake Report'
AND B.Void = 'False'
left join (records in CTE from dbo.Subforms_Incident) to something?

Marcos27

ASKER

OK, Paul, so what you are looking at here is a workflow. It starts as an Incident, then progresses to a CorrectiveAction, then to Subtasks (the Subtasks table been excluded in this example). The left joins are necessary because we want to show ALL records, even those without lots (CTE), as well as those that have not yet reached the Corrective Action step, or those that may not have any Subtasks assigned. The WHERE that I have here is just limiting the outer most table (Incident), so it is not killing the left join.

I understand your concern about the UNION, and I have implemented your suggestion to include the WHERE in the CTE as well.

;with CTE as
(Select LongText001 as 'Lot Number', ParentID as 'Event Number' From dbo.Subforms_Incident where category = 'Lot Numbers'
Union All
Select StandardText012 as 'Lot Number', IncidentID as 'Event Number' From dbo.Incident where category='Product Event Intake Report'
and Void='False')

I'm not sure I can limit this any more though. In the Subform table, there's nothing there other than Category='Lot Numbers' - which I already have.

Marcos27

ASKER

Vasto – this new SQL doesn’t work. If the first Lot # in the input parameter is a match, it works fine. However, if the first lot # provided is not a match, but one of the subsequent lots is a match, it should pull that record, but it doesn’t. It's much faster though - so I'd like to make it work. When i run the old query in Production (with much more data) it's slow. If I'm going to use that, I'll have to add more filters - maybe create a procedure for each product line.

ASKER CERTIFIED SOLUTION

vasto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

>>The WHERE that I have here is just limiting the outer most table (Incident), so it is not killing the left join.
incorrect, it is killing the left join

cte as (
lets give 1001 as the incident from dbo.Subforms_Incident, category = 'Lot Numbers'
union all
and 2001 as the incident from dbo.Incident, category='Product Event Intake Report'
)
... more processing

select from CTE
...
left join stuff
where category='Product Event Intake Report'

result
2001

1001 gets excluded

the final where clause IS a PROBLEM as far as I can see.

please see http://sqlfiddle.com/#!3/abaa7/1
which is a quick simulation of what appears to be happening

I admit I don't know your data model - but I feel pretty certain that final where clause is not doing what you expect.

vasto

I agree with PortletPaul. The where clause was changing the left join to inner join. To avoid that in the last SQL I moved the filtering to the join level

Marcos27

ASKER

Thanks to you both. I see what you're saying, and normally this would be true, but the "Lot Number" subform is only used on the "Product Event Intake Report" category of the Incident, so it's not an issue in this particular case due to the nature of the data. I suppose there could be "void" records with lots that are being pulled in the CTE, but it would be minimal. I'll test out vasto's new SQL . . .

Marcos27

ASKER

Vasto - this latest SQL still isnt working correctly. Here's what is happening. I have a record (IncidentID) #19005 that has the following lots: A001, B001, C001, D001.

I enter the following input lots for @parameter: F001, A001, but the record (19005) doesn't pull, event though there is a match. I then enter A001, F001, and it does pull. So it only seems to be looking for a match based on the first value entered in @parameter.

vasto

You might need to use F001,A001 (without space ) instead of F001, A001

Marcos27

ASKER

It worked! Thanks Vasto and PortletPaul. Great feedback. This site rules!