Solved

Evaluating Data with Multiple Values using a Multiple Value Parameter

Posted on 2013-05-29
37
581 Views
Last Modified: 2013-06-05
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
0
Comment
Question by:Marcos27
  • 13
  • 13
  • 4
  • +3
37 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
0
 

Author Comment

by:Marcos27
Comment Utility
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?
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
Yes, I believe this will take seconds . Just use the table-valued function to parse both comma-separated values and join the tables
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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.
0
 

Author Comment

by:Marcos27
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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 + ',%'
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Will that result in duplicate records if 2 or more of the user entered values are in the LOT?

mlmcc
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
I guess you can use distinct
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
So long as you don't select the lot number field

mlmcc
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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 ?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
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.
0
 

Author Comment

by:Marcos27
Comment Utility
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'
0
 

Author Comment

by:Marcos27
Comment Utility
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.
0
 

Author Comment

by:Marcos27
Comment Utility
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
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Marcos27
Comment Utility
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.
0
 

Author Comment

by:Marcos27
Comment Utility
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 . . .
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
If you want partial matches you can remove the commas
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
Comment Utility
a UNION (is this really needed, could union all be used?)
2 distincts (surely not)
splitting strings (can't this be avoided?)
multiple LIKE matching with wildcards both sides

table CorrectiveAction is joined in but never used in a select list by the way

lot's of monkeys in that short list.

is the CTE the "sql command" referred to here?
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 think there are a small barrel of monkeys still, comments below
;WITH CTE
AS (
    SELECT LongText001 AS 'Lot Number'
        , ParentID AS 'Event Number'
    FROM dbo.Subforms_Incident
    WHERE category = 'Lot Numbers'
    
    UNION -- << possible performance issue here, union all ?
    
    SELECT StandardText012 AS 'Lot Number'
        , IncidentID AS 'Event Number'
    FROM dbo.Incident
    -- there is a filter on this table later on, why not filter here?
    )
SELECT DISTINCT t.* -- << performance issue here (distinct)
FROM (
    SELECT DISTINCT [Event Number] -- << performance issue here (distinct)
        , 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 JOIN Incident B ON a.[Event Number] = B.IncidentID
    -- what happens to the unioned records from dbo.subforms_Incident? are they ignored?
    
    -- why is this table joined, as it is not used anywhere else
    LEFT JOIN CorrectiveAction ON B.CorrectiveActionID = CorrectiveAction.ActionID

    WHERE B.Category = 'Product Event Intake Report'
        AND B.Void = 'False'
    -- won't this where clause exclude stuff from the union above too? why union at all then?
    ) t
    -- and joining to a function, with multiple LIKE operations with wildcards both sides
INNER JOIN [dbo].fnTextToTable2008(@Parameter) p ON ',' + t.Lots + ',' LIKE '%,' + p.ID + ',%';

Open in new window

0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
0
 

Author Comment

by:Marcos27
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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?
0
 

Author Comment

by:Marcos27
Comment Utility
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.
0
 

Author Comment

by:Marcos27
Comment Utility
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.
0
 
LVL 18

Accepted Solution

by:
vasto earned 400 total points
Comment Utility
@Marcos27 it might be a little hard to find why it is not working without having the data. What the SQL should do is to get the list of Event Numbers for the provided Lots and join this with the Incident table. Try this one :

;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 
  	  INNER JOIN [dbo].fnTextToTable2008(@Parameter) p ON CTE.[Lot Number] = p.ID
  ) a
  LEFT JOIN Incident B ON a.[Event Number] = B.IncidentID AND B.Category = 'Product Event Intake Report' and B.Void='False';

Open in new window


I changed the left join to apply the where clause
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>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.
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
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
0
 

Author Comment

by:Marcos27
Comment Utility
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 . . .
0
 

Author Comment

by:Marcos27
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
You might need to use F001,A001 (without space ) instead of F001, A001
0
 

Author Closing Comment

by:Marcos27
Comment Utility
It worked! Thanks Vasto and PortletPaul. Great feedback. This site rules!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now