Solved

How to return records from Pivot View with multiple criteria

Posted on 2012-04-13
16
359 Views
Last Modified: 2012-08-14
I am trying to figure out a way to return records from a PivotView based on multiple selections from two list boxes.  Below is the code for retrieving records based on selections made from two comboboxes (See Original code below.  It worked fine, however the requirements have changed and now management wants to be able to make multiple selections from two list boxes so that essentially @Review and @Status would each contain a comma delimited string each string consisting of multiple reviews and statuses respectively.  I think what need to do is to build the where clause via the following pseudocode.  Basically I need some brainstorming

--------------------Psuedocode
For Each Review in @Review
       ADD the following to the Where
            Review1 = ISNULL(@Review, Review1) OR
            Review2 = ISNULL(@Review, Review2) OR
            Review3 = ISNULL(@Review, Review3) OR
            Review4 = ISNULL(@Review, Review4) OR
            Review5 = ISNULL(@Review, Review5) OR
            Review6 = ISNULL(@Review, Review6) OR
            Review7 = ISNULL(@Review, Review7) OR
            Review8 = ISNULL(@Review, Review8)
NEXT

For Each Status in @Review
      Add the following to the where clause
            Status1 = ISNULL(@Status, Status1) OR
            Status2 = ISNULL(@Status, Status2) OR
            Status3 = ISNULL(@Status, Status3) OR
            Status4 = ISNULL(@Status, Status4) OR
            Status5 = ISNULL(@Status, Status5) OR
            Status6 = ISNULL(@Status, Status6) OR
            Status7 = ISNULL(@Status, Status7) OR
            Status8 = ISNULL(@Status, Status8)
NEXT

------------------------------------ORIGINAL CODE-----------------------------------
CREATE PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS




SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
            Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
FROM [dbo].[vwPivotedReviewsRevised]
WHERE
(
Review1 = ISNULL(@Review, Review1) OR
Review2 = ISNULL(@Review, Review2) OR
Review3 = ISNULL(@Review, Review3) OR
Review4 = ISNULL(@Review, Review4) OR
Review5 = ISNULL(@Review, Review5) OR
Review6 = ISNULL(@Review, Review6) OR
Review7 = ISNULL(@Review, Review7) OR
Review8 = ISNULL(@Review, Review8)
)
AND
(
Status1 = ISNULL(@Status, Status1) OR
Status2 = ISNULL(@Status, Status2) OR
Status3 = ISNULL(@Status, Status3) OR
Status4 = ISNULL(@Status, Status4) OR
Status5 = ISNULL(@Status, Status5) OR
Status6 = ISNULL(@Status, Status6) OR
Status7 = ISNULL(@Status, Status7) OR
Status8 = ISNULL(@Status, Status8)
)
      

ORDER BY USI
GO
0
Comment
Question by:chtullu135
  • 11
  • 5
16 Comments
 

Author Comment

by:chtullu135
ID: 37844704
I've been working on it and I think I'm making some headway.


CREATE PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS

DECLARE @R varchar(max)
SET @R = @Review
SET @R = @R + ','

DECLARE @S varchar(max)
set @S = @Status
set @S = @S + ','

Declare @SqlString as varchar(max)
SET @SqlString =      'SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised] WHERE '
WHILE len(@R)>0
BEGIN
@SqlString = @SqlString +
                                          (
                                          Review1 = ISNULL(@R, Review1) OR
                                          Review2 = ISNULL(@R, Review2) OR
                                          Review3 = ISNULL(@R, Review3) OR
                                          Review4 = ISNULL(@R, Review4) OR
                                          Review5 = ISNULL(@R, Review5) OR
                                          Review6 = ISNULL(@R, Review6) OR
                                          Review7 = ISNULL(@R, Review7) OR
                                          Review8 = ISNULL(@R, Review8)
                                          )

END
AND
WHILE len(@S)>0
BEGIN
@SqlString = @SqlString + 'AND'
                                          (
                                          Status1 = ISNULL(@S, Status1) OR
                                          Status2 = ISNULL(@S, Status2) OR
                                          Status3 = ISNULL(@S, Status3) OR
                                          Status4 = ISNULL(@S, Status4) OR
                                          Status5 = ISNULL(@S, Status5) OR
                                          Status6 = ISNULL(@S, Status6) OR
                                          Status7 = ISNULL(@S, Status7) OR
                                          Status8 = ISNULL(@S, Status8)
                                          )
END      

ORDER BY USI
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
0
 

Author Comment

by:chtullu135
ID: 37844928
To make it simpler to code, I'm going to work on one section at a time.  I will be first working on the Review portion

ALTER PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS
DECLARE @R varchar(max)
SET @R = @Review
SET @R = @R + ','

Declare @SqlFrom as varchar(max)
SET @SqlFrom = 'SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised] WHERE Review'
DECLARE @Where as varchar(max)

WHILE len(@R)>0
BEGIN
      SET @Where = (
                                          Review1 = ISNULL(@R, Review1) OR
                                          Review2 = ISNULL(@R, Review2) OR
                                          Review3 = ISNULL(@R, Review3) OR
                                          Review4 = ISNULL(@R, Review4) OR
                                          Review5 = ISNULL(@R, Review5) OR
                                          Review6 = ISNULL(@R, Review6) OR
                                          Review7 = ISNULL(@R, Review7) OR
                                          Review8 = ISNULL(@R, Review8))

END

DECLARE @Sql as varchar(max)
SET @Sql = @SqlFrom + ' ' + @Where
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37847423
>>essentially @Review and @Status would each contain a comma delimited string each string consisting of multiple reviews and statuses respectively<<

If I understood correctly

you can just try this where clause without the dynamic SQL there:

WHERE
(
',' + Review1 + ',' LIKE '%,' + @Review ',%' OR
',' + Review2 + ',' LIKE '%,' + @Review ',%' OR
',' + Review3 + ',' LIKE '%,' + @Review ',%' OR
',' + Review4 + ',' LIKE '%,' + @Review ',%' OR
',' + Review5 + ',' LIKE '%,' + @Review ',%' OR
',' + Review6 + ',' LIKE '%,' + @Review ',%' OR
',' + Review7 + ',' LIKE '%,' + @Review ',%' OR
',' + Review8 + ',' LIKE '%,' + @Review ',%'
)
AND
(
',' + Status1 + ',' LIKE '%,' + @Status1 ',%' OR
',' + Status2 + ',' LIKE '%,' + @Status2 ',%' OR
',' + Status3 + ',' LIKE '%,' + @Status3 ',%' OR
',' + Status4 + ',' LIKE '%,' + @Status4 ',%' OR
',' + Status5 + ',' LIKE '%,' + @Status5 ',%' OR
',' + Status6 + ',' LIKE '%,' + @Status6 ',%' OR
',' + Status7 + ',' LIKE '%,' + @Status7 ',%' OR
',' + Status8 + ',' LIKE '%,' + @Status8 ',%'
)
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 41

Expert Comment

by:ralmada
ID: 37847428
or if you create the function below you can use it in your WHERE clause like this

WHERE
(
Review1 in (select value from dbo.parmstolist(@Review)) OR
Review2 in (select value from dbo.parmstolist(@Review)) OR
Review3 in (select value from dbo.parmstolist(@Review)) OR
Review4 in (select value from dbo.parmstolist(@Review)) OR
Review5 in (select value from dbo.parmstolist(@Review)) OR
Review6 in (select value from dbo.parmstolist(@Review)) OR
Review7 in (select value from dbo.parmstolist(@Review)) OR
Review8 in (select value from dbo.parmstolist(@Review))
)
AND (
Status1 in (select Value from dbo.parmstolist(@Status)) OR
Status2 in (select Value from dbo.parmstolist(@Status)) OR
Status3 in (select Value from dbo.parmstolist(@Status)) OR
Status4 in (select Value from dbo.parmstolist(@Status)) OR
Status5 in (select Value from dbo.parmstolist(@Status)) OR
Status6 in (select Value from dbo.parmstolist(@Status)) OR
Status7 in (select Value from dbo.parmstolist(@Status)) OR
Status8 in (select Value from dbo.parmstolist(@Status))
)



-----------------------------------
create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) )
returns @result TABLE (Value varchar(500), rn int identity)
AS
begin
declare @dx varchar(9)
-- declare @loops int
--set @loops = 0

DECLARE @TempList table
(
Value varchar(500)
)

if @delimiter is null set @delimiter = ' '
if len(@delimiter) < 1 set @delimiter = ' '
set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1)

DECLARE @Value varchar(8000), @Pos int

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

IF REPLACE(@Parameters, @delimiter, @dx) <> ''
BEGIN
WHILE @Pos > 0 -- AND @Loops < 100
BEGIN
--set @loops = @loops + 1
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
END
SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000)
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

END
END
INSERT @result
SELECT value
FROM @TempList
RETURN
END
0
 

Author Comment

by:chtullu135
ID: 37848335
Basically @Review would contain the data in the following format.
Loss Mitigation, Complaints, Bankruptcy
@Status would contain the respective data in the following format
345, 566, 2             where the numbers represent the statusid code,  I was using the status names but figured using the id numbers would improve performance a little bit more
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37848743
So have you tried my suggestions?
0
 

Author Comment

by:chtullu135
ID: 37849238
I'll be trying it tomorrow at work.  I'm really interested in the performance gain.
0
 

Author Comment

by:chtullu135
ID: 37852098
Hello ralmada,

The dynamic sql method though while it works is way to messy.  I've been trying your method that employs a function however I am getting the following error message

Msg 102, Level 15, State 1, Procedure spRetrieveByReviewOrStatusRev3, Line 12
Incorrect syntax near ','.

Now I did test the function via the following and it worked fine, so I'm not sure what is going on

SELECT * from dbo.ParmsToList('Complaints,Bankruptcy', ',')
CREATE PROC [dbo].[spRetrieveByReviewOrStatusRev3]
@Review AS varchar(max) = NULL,
@Status as varchar(max) = NULL
AS
DECLARE @Delimiter As varchar(10)
SET @Delimiter = ','
SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised]
WHERE
(
Review1 in (select value from dbo.parmstolist(@Review), ',') OR
Review2 in (select value from dbo.parmstolist(@Review), ',') OR
Review3 in (select value from dbo.parmstolist(@Review), ',') OR
Review4 in (select value from dbo.parmstolist(@Review), ',') OR
Review5 in (select value from dbo.parmstolist(@Review), ',') OR
Review6 in (select value from dbo.parmstolist(@Review), ',') OR
Review7 in (select value from dbo.parmstolist(@Review), ',') OR
Review8 in (select value from dbo.parmstolist(@Review), ',')
)
AND (
Status1 in (select Value from dbo.parmstolist(@Status), ',') OR
Status2 in (select Value from dbo.parmstolist(@Status), ',') OR
Status3 in (select Value from dbo.parmstolist(@Status), ',') OR
Status4 in (select Value from dbo.parmstolist(@Status), ',') OR
Status5 in (select Value from dbo.parmstolist(@Status), ',') OR
Status6 in (select Value from dbo.parmstolist(@Status), ',') OR
Status7 in (select Value from dbo.parmstolist(@Status), ',') OR
Status8 in (select Value from dbo.parmstolist(@Status), ',')
)
0
 

Author Comment

by:chtullu135
ID: 37852128
Never mind I found the problem,
Review1 in (select value from dbo.parmstolist(@Review), ',') should be
Review1 in (select value from dbo.parmstolist(@Review, ','))
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37852295
yep, extra brackets there

same with @Status
0
 

Author Comment

by:chtullu135
ID: 37852316
It's working.  However, I want to account for when one or both input parameters to the stored procedure are null
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 37852406
just add this

WHERE
(
@Review is null OR
Review1 in (select value from dbo.parmstolist(@Review, ',') OR
Review2 in (select value from dbo.parmstolist(@Review, ',') OR
Review3 in (select value from dbo.parmstolist(@Review, ',') OR
Review4 in (select value from dbo.parmstolist(@Review, ',') OR
Review5 in (select value from dbo.parmstolist(@Review, ',') OR
Review6 in (select value from dbo.parmstolist(@Review, ',') OR
Review7 in (select value from dbo.parmstolist(@Review, ',') OR
Review8 in (select value from dbo.parmstolist(@Review, ',')
)
AND (
@Status is null OR
Status1 in (select Value from dbo.parmstolist(@Status, ',') OR
Status2 in (select Value from dbo.parmstolist(@Status, ',') OR
Status3 in (select Value from dbo.parmstolist(@Status, ',') OR
Status4 in (select Value from dbo.parmstolist(@Status, ',') OR
Status5 in (select Value from dbo.parmstolist(@Status, ',') OR
Status6 in (select Value from dbo.parmstolist(@Status, ',') OR
Status7 in (select Value from dbo.parmstolist(@Status, ',') OR
Status8 in (select Value from dbo.parmstolist(@Status, ',')
)
0
 

Author Comment

by:chtullu135
ID: 37852437
This is what I'm thinking to deal with nulls
ALTER PROC [dbo].[spRetrieveByReviewOrStatusRev3]
@Review AS varchar(max) = NULL,
@Status as varchar(max) = NULL
AS
DECLARE @Delimiter As varchar(10)
SET @Delimiter = ','
SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised]

WHERE

(
Review1 in (select value from dbo.parmstolist(@Review,',')) OR
Review2 in (select value from dbo.parmstolist(@Review,',')) OR
Review3 in (select value from dbo.parmstolist(@Review,',')) OR
Review4 in (select value from dbo.parmstolist(@Review,',')) OR
Review5 in (select value from dbo.parmstolist(@Review,',')) OR
Review6 in (select value from dbo.parmstolist(@Review,',')) OR
Review7 in (select value from dbo.parmstolist(@Review,',')) OR
Review8 in (select value from dbo.parmstolist(@Review,','))
)
AND (

IF @Status IS NOT NULL THEN
      BEGIN
            Status1 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status2 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status3 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status4 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status5 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status6 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status7 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status8 in (select Value from dbo.parmstolist(@Status,','))
      END
ELSE
      BEGIN
            Status1 LIKE ' % ' OR
            Status2 LIKE ' % ' OR
            Status3 LIKE ' % ' OR
            Status4 LIKE ' % ' OR
            Status5 LIKE ' % ' OR
            Status6 LIKE ' % ' OR
            Status7 LIKE ' % ' OR
            Status8 LIKE ' % ' OR
      END
)
0
 

Author Comment

by:chtullu135
ID: 37852441
I just saw what you had posted for dealing with nulls.  It can't get any simpler - thanks
0
 

Author Comment

by:chtullu135
ID: 37852485
Everything is working.  Although the dynamic sql method worked, the code you posted was much simpler and is easier to maintain.  I've learned quite a bit.  Thanks again.
0
 

Author Closing Comment

by:chtullu135
ID: 37852493
Thanks for your help.  I've learned a lot.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

820 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