July 5, 2018 at 1:08 pm
I'm using a function to convert a users comma separated list of values to a table to use in a query.
I know using a wildcard in the IN statement is not possible, but how can I change this query to view all item records when the user doesn't enter anything in the @items variable?
Here is code I have now that doesn't work:
select *
from items
where item_no IN (
SELECT DISTINCT
CASE
WHEN Len(@items) > 0 THEN x.Item
ELSE '%'
END
FROM (SELECT ITEM FROM DBO.Splitstrings_moden(@items, ','))x
)
July 5, 2018 at 1:17 pm
if I understand correctly what you need I think the following covers it
select *
from items
where (len(@items) > 0
and item_no IN (
SELECT DISTINCT x.item
FROM (SELECT ITEM FROM DBO.Splitstrings_moden(@items, ','))x
)
)
or @items is null
or len(@items) = 0
July 5, 2018 at 1:26 pm
select *
from items
where isnull(@items, '') = '' OR
exists(select 1 from DBO.Splitstrings_moden(@items, ',') where Item = item_no)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2018 at 2:38 pm
I tried both of those, but they made my query run for over a minute. The EXISTS... gave me an idea. I performed a join like this and the query only takes about 11 sec. to return a bit under 1M records.
Anyone have any objection to this method?
select *
from items
join ( SELECT item, count(item) over (partition by (select 1)) cnt FROM DBO.Splitstrings_moden(nullif(@items,''), ',') )x
ON item_no = x.item OR x.cnt = 0
July 5, 2018 at 2:58 pm
Jackie Lowery - Thursday, July 5, 2018 2:38 PMI tried both of those, but they made my query run for over a minute. The EXISTS... gave me an idea. I performed a join like this and the query only takes about 11 sec. to return a bit under 1M records.
Anyone have any objection to this method?
select *
from items
join ( SELECT item, count(item) over (partition by (select 1)) cnt FROM DBO.Splitstrings_moden(nullif(@items,''), ',') )x
ON item_no = x.item OR x.cnt = 0
No particular objection if it works for you.
How many items are in the @items list? If it's more than a handful, you could try inserting them to a keyed temp table and do the exists on that:
select top (0) item_no as item
into #items
from items;
alter table #items add constraint items__pk primary key ( item );
insert into #items with (tablock)
select distinct item
from DBO.Splitstrings_moden(@items, ',');
select i.*
from items i
where isnull(@items, '') = '' OR
exists(select 1 from #items i2 where i2.Item = i.item_no)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 5, 2018 at 4:11 pm
I took your advice, sort of, on the function and revisited the EXISTS .. after some further reading. I found how to use a CASE statement to get the code to run even faster. Man, SQL is so fickle. lol
I rebuilt the function to return a table with a primary key. Here is new code for function.
Here is new code for string function:
CREATE FUNCTION [dbo].[SplitStrings_Moden]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS @X TABLE
(
Item CHAR(15) PRIMARY KEY
)
WITH SCHEMABINDING AS
BEGIN
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),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E42(N) AS (SELECT 1 FROM E4 a, E2 b),
cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1)))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
--SELECT Item = ltrim(rtrim(SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))))
-- FROM cteStart s
INSERT INTO @X(Item)
SELECT DISTINCT Item = ltrim(rtrim(SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))))
FROM cteStart s
RETURN;
END
Here is new code for query:
select i.*
from items i
WHERE item_no not like '**%' AND
CASE
WHEN len(@items) <= 0 THEN 1
WHEN EXISTS( Select 1 from DBO.SplitStrings_Moden(@items, ',') where Item = item_no ) THEN 1
ELSE 0
END = 1
July 5, 2018 at 4:24 pm
Here is my final full code and exec plan if you have a bit of time to look for any further issues. Thanks again for the help.
declare @items as char(350) = '23016CS3-12, 23036CS6-24, 600cs4-16, 90988CS1-4'
set nocount on
select doc_no as inv_no
, doc_dt as inv_date
, arof.YearNo
, arof.MonthNo
, arof.MonthText
, oehh.cus_no
, oehh.ord_no
, oehh.oe_po_no
, oelh.line_no
, oelh.item_no
, oelh.loc item_loc
, left(oelh.item_no, nullif(CHARINDEX('cs', oelh.item_no),0)-1) base_item_no
, oelh.item_desc_1
, oelh.qty_to_ship
, oelh.sls_amt as itm_sls_amt
, oehh.ship_to_name
, oehh.ship_to_addr_1
, oehh.ship_to_addr_2
, araa.city
, araa.state
, araa.zip
from (
select distinct a.doc_no
, a.doc_dt
, w.YearNo
, w.MonthNo
, w.MonthText
from aropnfil_sql a
JOIN wsTimeDim w ON a.doc_dt = cast(format(w.Date,'yyyyMMdd') as int)
where a.doc_type = 'I' AND a.doc_dt >= 20100101 AND a.doc_dt <= 20180602
) arof
join oelinhst_sql oelh ON arof.doc_no = oelh.inv_no AND oelh.item_no not like '**%'
AND CASE
WHEN len(@items) <= 0 THEN 1
WHEN EXISTS( Select 1 from DBO.SplitStrings_Moden(@items, ',') where Item = item_no ) THEN 1
ELSE 0
END = 1
join oehdrhst_sql oehh on oelh.ord_no = oehh.ord_no and oelh.ord_type = oehh.ord_type and oelh.inv_no = oehh.inv_no
left join ARALTADR_SQL araa on oehh.cus_no = araa.cus_no AND oehh.cus_alt_adr_cd = araa.cus_alt_adr_cd
July 6, 2018 at 10:29 am
You might want to look at:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/values-and-long-parameter-lists/
https://www.red-gate.com/simple-talk/sql/learn-sql-server/values-and-long-parameter-lists-part-ii/
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply