WHERE Filter

  • Hi to all. I haven't posted for a while and it is now time to wear my sql cap again 🙂

    I have a query which in brief has the following filter:

    WHERE ProductId = @ProductId AND StructureVersion = @StructureVersion AND Type IN (@Types)

    AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc

    The bit I am interested in is:

    AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc

    The filter currenty returns 1 row as both @ProdFolderReq and @ProdAidDoc = 1

    I would like to return 1 record if either or both return 1 (i.e. drop the AND). I tried OR but this returns multiple records.

    Can this be done?

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • 2Tall (11/13/2012)


    Hi to all. I haven't posted for a while and it is now time to wear my sql cap again 🙂

    I have a query which in brief has the following filter:

    WHERE ProductId = @ProductId AND StructureVersion = @StructureVersion AND Type IN (@Types)

    AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc

    The bit I am interested in is:

    AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc

    The filter currenty returns 1 row as both @ProdFolderReq and @ProdAidDoc = 1

    I would like to return 1 record if either or both return 1 (i.e. drop the AND). I tried OR but this returns multiple records.

    Can this be done?

    Kind Regards,

    Phil.

    1st. What you are expecting to have in @Types? Is it comma separated string? If yes, then your "Type IN (@Types)" filter will never work as it is absolutely the same as "Type = @Types"

    2nd. You should really show what you have tried with OR.

    But I guess it should be like that :

    AND

    ( ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq

    OR ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc )

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi. I have managed to get it working. The query has been written for a report.

    Let me explain. I was never able to workout how to pass values as a comma separated string so I have a fudge. I have a number of datasets that allow multi-value selection, this is where the IN (@Paremeter) comes in. Example:

    SELECT DISTINCT Type,

    CASE

    WHEN Type = 'T' THEN 'A'

    WHEN Type = 'P' THEN 'B'

    WHEN Type = 'M' THEN 'C'

    WHEN Type = 'B' THEN 'D'

    END AS TypeDescription

    FROM MyTable

    The report parameter allows multi-value selection and values are derived from a query.

    So for my boolean values I have datasets that returns both true and false. The report parameters default to 'True & False'. So the AND clause remains. The user can select 'True' for one parameter then 'False' for the other (or what ever combination they want).

    I hope that makes sense!

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • To pass values as a comma separated string, you need to concatenate them into one comma separated string and pass it.

    To use it in you procedure, you can use "IN" clause with Dynamic-SQL, or use some string splitter functions (you can find good one on this forum) to split into table.

    And as I said before, "IN (@Paremeter)" is exactly the same as "= @Paremeter", and it is irrelevant of what you have inside of @Paremeter.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Thanks for posting Eugene. The approach I have become used to implementing seems to work. I did search the forum many moons ago regards the approach you recommend but my grey matter couldn't get a handle on how to implement it 🙂

    In the appoach I have adopted the parameters list a set of values returned by the underlying query (that I want the user to see). They can select 1 or all values. So for instance if a product came in varying colours I might have a query that returned red, blue, yellow. The parameter uses this query and displays values as available selections. The user can select red,blue and yellow or just red. Data returned is filtered by their selection. I'm not trying to teach you how to suck eggs by the way, just explaining it as I see it! I am but a mere novice when it comes to the black art of writing code...........

    Thnaks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • Use Jeff Moden's splitter function (copied below for convenience).

    /* Sample Data */

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

    DROP TABLE #TempTable

    CREATE TABLE #TempTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [Type] CHAR(1) NULL,

    [TypeDesc] CHAR(1) NULL,

    PRIMARY KEY (ID),

    UNIQUE (ID))

    INSERT INTO #TempTable

    SELECT 'T','A'

    UNION

    SELECT 'P','B'

    UNION

    SELECT 'M','C'

    UNION

    SELECT 'B','D'

    UNION

    SELECT 'X','X'

    UNION

    SELECT 'Y','Y'

    UNION

    SELECT 'Z','Z'

    /* Display sample data */

    SELECT

    [Type]

    ,[TypeDesc]

    FROM

    #TempTable

    /* Show just the rows that match the delimited string */

    DECLARE @strTypes VARCHAR(50)

    SET @strTypes = 'T,P,M,B'

    SELECT DISTINCT

    [Type]

    ,[TypeDesc]

    FROM

    #TempTable AS tt

    CROSS APPLY

    dbo.tvfDelimitedSplit8K(@strTypes,',') AS ds

    WHERE

    tt.[Type] = ds.Item

    CREATE FUNCTION [dbo].[tvfDelimitedSplit8K]

    (

    @pString VARCHAR(8000)

    ,@pDelimiter CHAR(1)

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    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 (

    SELECT TOP (ISNULL(DATALENGTH(@pString),0))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E4

    ),

    cteStart(N1)

    AS (

    SELECT

    1

    UNION ALL

    SELECT

    t.N + 1

    FROM

    cteTally t

    WHERE

    SUBSTRING(@pString,t.N,1) = @pDelimiter

    ),

    cteLen(N1,L1)

    AS (

    SELECT

    s.N1

    ,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)

    FROM

    cteStart s

    )

    SELECT

    ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)

    ,Item = SUBSTRING(@pString,l.N1,l.L1)

    FROM

    cteLen l ;

    GO

  • Hi Steven. I read 'Jeff's' post after Eugene's replied (I did a search of the forum). To be honest I have no idea how to implement. I never took to programming so when people start talking functions my brain switches off........

    Feel free to educate me with an example, you never know, the penny might just drop!

    At the moment I have a query that I use in a report data set. When users want to select multiple values I provide them with another data set to select values from using IN.

    How would I replace with a function, it all seems complicated 🙂

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • Create a stored procedure like this (I will assume you have already created the splitter function):

    CREATE PROCEDURE dbo.GetTypeDesc

    @strTypes VARCHAR(255)

    ,@strDelimiter CHAR(1)

    AS

    BEGIN

    SELECT DISTINCT

    [Type]

    ,[TypeDesc]

    FROM

    #TempTable AS tt

    CROSS APPLY

    dbo.tvfDelimitedSplit8K(@strTypes,@strDelimiter) AS ds

    WHERE

    tt.[Type] = ds.Item

    END

    Then call the procedure by passing in your delimited string as the input parameter. Of course, instead of a temp table as used here for demo purposes you would probably have a more complex query against actual tables.

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

    DROP TABLE #TempTable

    CREATE TABLE #TempTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [Type] CHAR(1) NULL,

    [TypeDesc] CHAR(1) NULL,

    PRIMARY KEY (ID),

    UNIQUE (ID))

    INSERT INTO #TempTable

    SELECT 'T','A'

    UNION

    SELECT 'P','B'

    UNION

    SELECT 'M','C'

    UNION

    SELECT 'B','D'

    UNION

    SELECT 'X','X'

    UNION

    SELECT 'Y','Y'

    UNION

    SELECT 'Z','Z'

    /* Display sample data */

    SELECT

    [Type]

    ,[TypeDesc]

    FROM

    #TempTable

    /* Display the filtered data */

    EXEC dbo.GetTypeDesc 'T,P,M,B',',' -- comma delimited

    --or

    EXEC dbo.GetTypeDesc 'T|P|M|B','|' -- pipe delimited

    The Splitter Function is a TABLE-VALUED FUNCTION. So the output of that function is a TABLE just like a view or temp table and thus you can use the output to join to any other table. This turns the entire query into a set-based query that doesn't require additional looping once the function has parsed the delimited string into individual rows.

    The input string 'T,P,M,B' could be the output from an HTML dropdown, for example, and then the keys represented in the delimited string are used to cross-filter a query to show only the related rows.

     

  • Thanks Steven. I will have a play. Great stuff.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

Viewing 9 posts - 1 through 8 (of 8 total)

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