Find Invalid Combinition

  • Hi,

    I have a scenario where I need to develop a stored proc to identify invalid input provided.

    Following is a sample scenario

    Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10));

    insert into Product

    select 'Prod1',10,'Black' union ALL

    select 'Prod1',10,'BLue' union ALL

    select 'Prod2',20,'Green' union ALL

    select 'Prod2',10,'Black' ;

    CREATE table sizes (SizeId int, Size varchar(20));

    INSERT INTO sizes

    select 10,'S' union ALL

    select 10,'M' union ALL

    select 10,'L' union ALL

    select 20,'XL' union ALL

    select 20,'2XL' union ALL

    select 20,'3XL' ;

    --===================================================

    -- Function Creation

    --===================================================

    create FUNCTION [dbo].[fnSplit]

    (

    @ItemList NVARCHAR(4000),

    @delimiter CHAR(1)

    )

    RETURNS @IDTable TABLE (Item VARCHAR(50) collate database_default )

    AS

    BEGIN

    DECLARE @tempItemList NVARCHAR(4000)

    SET @tempItemList = @ItemList

    DECLARE @i INT

    DECLARE @Item VARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)

    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)

    BEGIN

    IF @i = 0

    SET @Item = @tempItemList

    ELSE

    SET @Item = LEFT(@tempItemList, @i - 1)

    INSERT INTO @IDTable(Item) VALUES(@Item)

    IF @i = 0

    SET @tempItemList = ''

    ELSE

    SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

    SET @i = CHARINDEX(@delimiter, @tempItemList)

    END

    RETURN

    END

    In following TSql Code , Color and Size are optional. Both are provided as comma separated input.

    I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.

    I had tried out join but it is not serving needs. Can you please suggest something ?

    ---===========================================

    -- Sql

    --============================================

    DECLARE

    @ProdId varchar(10),

    @color varchar(max) = Null,

    @size varchar(max) = Null

    BEGIN

    set @ProdId='Prod1';

    SET @color='Black,Blue,bbc' ; -- bbc is invalid

    set @size='L,S,M,MM'; -- MM is invalid

    SET NOCOUNT ON

    DECLARE @cteColors TABLE (ProductId varchar(10),CatalogColor VARCHAR(14) PRIMARY KEY)

    INSERT INTO @cteColors

    SELECT @ProdId,

    [CatalogColor] = Item

    FROM dbo.fnSplit(@color, ',');

    DECLARE @cteSizes TABLE (ProductId varchar(10),SizeDesc VARCHAR(10) PRIMARY KEY)

    INSERT INTO @cteSizes

    SELECT @ProdId,

    [SizeDesc] = Item

    FROM dbo.fnSplit(@size, ',');

    --========================================================================

    --== Inserting valid combinition to temp table

    --========================================================================

    SELECT distinct prod.ProductId,

    prod.ProductColor,

    Sizes.Size,

    '1' as status

    INTO #tmp

    FROM product prod

    INNER JOIN sizes sizes

    ON prod.SizeID = sizes.SizeId

    WHERE 1=1

    AND (EXISTS (SELECT sizedesc FROM @cteSizes WHERE sizedesc = sizes.Size)

    OR @size IS NULL)

    AND (EXISTS (SELECT CatalogColor FROM @cteColors WHERE CatalogColor = prod.ProductColor)

    OR @color IS NULL)

    AND prod.ProductId = @ProdId

    GROUP BY prod.ProductId,sizes.Size,prod.ProductColor

    ORDER BY 1,2,4;

    -- ==================================

    -- Trying to find invalid combinition

    --====================================

    WITH mycte

    as (

    select color.ProductId,color.catalogcolor,sz.SizeDesc

    from @cteColors color

    cross join @cteSizes sz

    )

    SELECT my.ProductId,

    my.catalogColor,

    my.SizeDesc

    ,tmp.*

    --,Case when tmp.ProductColor is null then '101'

    --END as Status

    FROM mycte my

    LEFT JOIN #tmp tmp

    ON my.ProductId = tmp.ProductId

    AND my.CatalogColor = tmp.ProductColor

    AND my.sizeDesc = tmp.Size;

    --

    drop table #tmp;

    END

  • Just to be clear I understand the requirements, you just want all possible combinations of the sizes and colors in the comma delimited lists, along with a flag that indicates whether that combination is valid?

    If so, there are a few ways of doing this, and I quickly wrote up a few of them just to illustrate how they might work. The performance characteristics of each are different, so you'll want to test them to see how they perform with your actual data.

    I'd also look at using one of the ultra-efficient splitters out there, like Jeff Moden's DelimitedSplit8k, and avoiding using ordinal column positions for ordering.

    At any rate, this should give you a good idea of some ways this can be done.

    DECLARE

    @ProdId varchar(10),

    @color varchar(max) = Null,

    @size varchar(max) = Null

    SET @ProdId='Prod1';

    SET @color='Black,Blue,bbc' ; -- bbc is invalid

    SET @size='L,S,M,MM'; -- MM is invalid

    --Option using EXCEPT, INTERSECT, and UNION ALL

    --I've included this mostly for completeness (and fun),

    --as it's both harder to follow and slower

    --than the others.

    SELECT InputSizes.Item AS Size, InputColors.Item AS Color, 0 AS IsValid

    FROM fnsplit(@size,',') InputSizes

    CROSS APPLY fnsplit(@color,',') InputColors

    EXCEPT

    SELECT Size, ProductColor, 0 AS IsValid

    FROM Sizes

    CROSS JOIN Product

    UNION ALL

    SELECT InputSizes.Item AS Size, InputColors.Item AS Color, 1 AS IsValid

    FROM fnsplit(@size,',') InputSizes

    CROSS JOIN fnsplit(@color,',') InputColors

    INTERSECT

    SELECT Size, ProductColor, 1 AS IsValid

    FROM Sizes

    CROSS JOIN Product

    PRINT 'Now NOT EXISTS'

    --Option using NOT EXISTS

    SELECT InputSizes.Item AS Size, InputColors.Item AS Color,

    CASE

    WHEN

    NOT EXISTS (SELECT NULL FROM Sizes S WHERE s.size=InputSizes.Item)

    OR

    NOT EXISTS (SELECT NULL FROM Product P WHERE p.ProductColor=InputColors.Item) THEN 0 ELSE 1 END AS IsValid

    FROM fnsplit(@size,',') InputSizes

    CROSS JOIN fnsplit(@color,',') InputColors

    PRINT 'Now NOT IN'

    --Option using NOT IN.

    SELECT InputSizes.Item AS Size, InputColors.Item AS Color,

    CASE

    WHEN

    InputSizes.Item NOT IN (SELECT size FROM sizes)

    OR

    InputColors.Item NOT IN (SELECT ProductColor FROM Product) THEN 0 ELSE 1 END AS IsValid

    FROM fnsplit(@size,',') InputSizes

    CROSS JOIN fnsplit(@color,',') InputColors

    PRINT 'Now LEFT JOIN'

    --Option using LEFT JOIN

    SELECT InputSizes.Item AS Size, InputColors.Item AS Color, CASE WHEN S.size+P.ProductColor IS NULL THEN 0 ELSE 1 END as IsValid

    FROM fnsplit(@size,',') InputSizes

    CROSS JOIN fnsplit(@color,',') InputColors

    LEFT JOIN Sizes S ON S.size=InputSizes.Item

    LEFT JOIN Product P ON P.ProductColor=InputColors.Item

    Cheers!

  • What is the desired output from the values you are using? Also, as stated previously ordering by ordinal position is just bad. Don't do it. And throw that splitter in the trash can. Using a while loop for a splitter is horribly inefficient and is the absolute slowest possible method of all splitters. In a perfect world you would pass in a table valued parameter instead of a delimited list and the problem becomes infinitely simpler.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you Jacob , I will test them .

  • Just a shot in the dark here. From the title it sounds like you want to find invalid combinations among the values being passed in. There are a number of ways to do this and even a number of ways of interpreting the question. Here is one way to accomplish what I think is all invalid combinations from the values passed in. This is using the DelimitedSplit8K table valued function. You can find it by following the link in my signature about splitting strings.

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = 'Black,Blue,bbc',

    @size varchar(max) = 'L,S,M,MM';

    with AllSizesAndColors as --this gets all combinations of color and size

    (

    select p.ProductColor, s.Size

    from Product p

    cross join sizes s

    )

    , RequestedSizesAndColors as --this gets all combinations from the values in the parameters

    (

    select c.Item as ProductColor

    , s.Item as Size

    from dbo.DelimitedSplit8K(@color, ',') c

    cross join dbo.DelimitedSplit8K(@size, ',') s

    )

    select r.ProductColor

    , r.size

    from RequestedSizesAndColors r

    left join AllSizesAndColors a on a.ProductColor = r.ProductColor

    and a.Size = r.Size

    where a.ProductColor IS NULL

    order by r.ProductColor

    , r.Size

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sean, I want to get output like following

    -- If color invalid status should be 100

    -- if color is valid but size is invalid status should be 101

    -- if valid status should be 102

    Product Color Size Status

    Prod1 Black S 102

    Prod1 Black M 102

    Prod1 Black L 102

    Prod1 Black MM 101

    Prod1 Blue S 102

    Prod1 Blue M 102

    Prod1 Blue L 102

    Prod1 Blue MM 101

    Prod1 bbc S 100

    Prod1 bbc M 100

    Prod1 bbc L 100

    Prod1 bbc MM 100

  • thbaig (9/30/2015)


    sean, I want to get output like following

    -- If color invalid status should be 100

    -- if color is valid but size is invalid status should be 101

    -- if valid status should be 102

    Product Color Size Status

    Prod1 Black S 102

    Prod1 Black M 102

    Prod1 Black L 102

    Prod1 Black MM 101

    Prod1 Blue S 102

    Prod1 Blue M 102

    Prod1 Blue L 102

    Prod1 Blue MM 101

    Prod1 bbc S 100

    Prod1 bbc M 100

    Prod1 bbc L 100

    Prod1 bbc MM 100

    Are there any other new rules? This seems very strange to me but certainly feasible.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This produces the expected output based on the sample data provided.

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = 'Black,Blue,bbc',

    @size varchar(max) = 'L,S,M,MM';

    with AllSizesAndColors as --this gets all combinations of color and size

    (

    select p.ProductColor, s.Size

    from Product p

    cross join sizes s

    )

    , RequestedSizesAndColors as --this gets all combinations from the values in the parameters

    (

    select c.Item as ProductColor

    , s.Item as Size

    from dbo.DelimitedSplit8K(@color, ',') c

    cross join dbo.DelimitedSplit8K(@size, ',') s

    )

    select @ProdID as Product

    , r.ProductColor

    , r.size

    , case when a.ProductColor IS NOT NULL then 102

    when p.ProductColor IS NOT NULL then 101

    else 100

    end as Status

    from RequestedSizesAndColors r

    left join Product p on p.ProductColor = r.ProductColor

    left join AllSizesAndColors a on a.ProductColor = r.ProductColor

    and a.Size = r.Size

    group by r.ProductColor

    , r.size

    , case when a.ProductColor IS NOT NULL then 102

    when p.ProductColor IS NOT NULL then 101

    else 100 end

    order by r.ProductColor

    , r.Size

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you Sean , it worked.

    How can we make color and Size optional ? if either one or both provided it should return on input . If both not provide it should return all available combination.

  • thbaig (10/1/2015)


    Thank you Sean , it worked.

    How can we make color and Size optional ? if either one or both provided it should return on input . If both not provide it should return all available combination.

    You will have to explain these new rules a little better. I don't quite understand what you mean. Also, if there are any other little nuances this would be the time to share them instead of after I help you with a solution.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This is the only thing left.

    Case-1

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.

    @size varchar(max) = 'L,S,M,MM'; -- Will consider only provided sizes

    Case-2

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = 'Black,Blue,bbc' -- Will consider only provided colors

    @size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product

    Case-3

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.

    @size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product

  • thbaig (10/1/2015)


    This is the only thing left.

    Case-1

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.

    @size varchar(max) = 'L,S,M,MM'; -- Will consider only provided sizes

    Case-2

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = 'Black,Blue,bbc' -- Will consider only provided colors

    @size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product

    Case-3

    DECLARE @ProdId varchar(10) = 'Prod1',

    @color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.

    @size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product

    Here is how I would deal with this. I would create a main procedure that receives all your parameters. Then a procedure for each possible path. So for example in Case-1 you would call the procedure that can handle that situation. The code would be very similar to when all parameters are passed except that you would just use a cross join to the ProductColor instead of splitting the parameter. Repeat this for each of your 3 new cases. Case-4 would be the code I already posted. Then you just need a procedure as the traffic controller. Sure you could do that all in a single procedure but you are going to have some serious performance problems. This article from Gail explains the performance benefits of this approach. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply