January 16, 2018 at 2:38 pm
Hello all,
I wasn't sure where exactly to create this question since the ultimate product is going into an SSRS report, but I'm having trouble writing the SQL query. I'm hoping someone out there will have some suggestions on how to approach this. Here's the situation:
I have an SSRS report with a multi-selection parameter which is querying on a field that has concatenated 1 - 2-character values separated by a comma. The sample data looks something like this:
DECLARE
@table TABLE
(clientid INT
, mobility VARCHAR(15)
)
INSERT INTO @table
VALUES
(
121,'A,WA,CB,V')
,(223,'WA,N,NR')
,(334,'AR')
,(445,'A,V1')
,(556,'LI,MO')
,(667,'WA')
,(778,'A')
DECLARE @MOBILITYAID VARCHAR(20) = 'A,WA'
SELECT *
FROM @TABLE
WHERE MOBILITY LIKE '%' + @MOBILITYAID + '%'
I'd like for the query to pull up all rides who have either A or WA codes, which would be 121, 223, 445,667 and 778.
This is going to be used in an SSRS report which allows for multiple values to be selected. Because of this, the user can select from a list of these two-character codes in which to search for all the clients who have any combination of those selected codes. The problem arises when the user selects "A" as one of the codes. There are many codes which contain the letter A, but there is also a code in and of itself the letter 'A' (and BTW always happens to be located at the beginning of the string if it's listed in mobility field). The problem with using a wildcard here is that when A is the parameter, all clients who have an A in their mobility string come up in the results. I want to be able to both search for records which have the A code, as well as whatever other codes they've selected from the list. Almost like search for "where mobility in ('A','A,') and mobility in ('%' + @mobility + '%') ---which I know is wrong, but I'm trying to articulate what I'm thinking here.
The original author of the SSRS report added this code below in the SSRS dataset to account for these codes, but it doesn't properly pick up the "A" code by itself so it's not very functional for the users who are specifically looking for those clients who have the "A" code (as well as any other single codes):
="SELECT * FROM Clients WHERE InActive = @Inactive " &
" AND (mobaids like '%" & Join(Parameters!mobaid.Value,"%' OR mobaids LIKE '%") & "%')" &
" order by lastname"
Is there any way that I can actually allow for this? The users need to be able to select each individual code they want from the list and find any client who has any combination of those selected codes, accounting for those codes which are single letters which also may show up as part of other codes.
(Hopefully I've explained this well enough).
Looking for any suggestions.
Thanks so much
January 16, 2018 at 3:03 pm
Use a string splitter on both the table field and the parameter, and then use an equality comparison.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2018 at 10:48 am
Thank you, Drew!
That did get me in the right direction (I think). At the very least I now have a cool string splitter function. I got something working in SSRS with this using this:
SELECT CLIENTID, CLIENT_CODE, CLIENTNAME, MOBAIDS
FROM VIEW_CLIENTS c
CROSS APPLY DBO.FX_DelimitedSplitter(C.mobaids,',') split
WHERE MOBAIDS IN (@mobaid)
...which kind of works -- in that I'm getting matching records back when selecting multiple values in the multi-value parameter, however, it's not quite right. It's only matching on those records which have exactly/only the codes selected and not records with have those selected codes as well as others. For example, I selected CA and AR for mobility parameters I want to see and it returned only those clients who have specifically "CA" or "AR" for their mobaid code, but not clients who have "CA, WA" or "AR, V1, WA" or any other combination which also includes the selected code.
I think I'm missing something. You mentioned using the function on both the table field and the parameter. How would I go about that in SSRS? Do I need to add a wildcard search in here somewhere? (or will that just put me right back where I started, pulling codes which it shouldn't)?
Thanks again for the help.
January 18, 2018 at 1:22 pm
I think I figured it out.
I was using the equality comparison (IN) against the wrong field. I changed the code to this:
SELECT DISTINCT CLIENTID, CLIENT_CODE, CLIENTNAME, MOBAIDS
FROM VIEW_CLIENTS c
CROSS APPLY DBO.FX_DelimitedSplitter(C.mobaids,',') split
WHERE item IN (@mobaid)
(used the "item" column from the splitter function instead of using the actual mobaid column from the view) and now it's showing all records which have the selected codes in their mobaid string.
Drew - thank you again for the tip, it was a huge help!
April 8, 2020 at 1:43 am
Hi Tacy,
I am stuck with an exactly similar problem as yours.
Can you please share your splitter function?
Thank you
April 8, 2020 at 1:16 pm
Hi Saty,
It's just the same splitter function that Drew posted above:
CREATE FUNCTION [dbo].[FX_DelimitedSplitter]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Good luck!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply