question

Neil avatar image
Neil asked

Multiple OR conditions in Where clause

Hello there - I'm a grandad using SQL and this is my first post - please be gentle with me. I used to work for IBM but that's many years ago now.......

Thanks, in advance, for any help you may be able to offer.

Given, as an example, the following query extracted from a TVF against the T_ServiceProviders table. The ProviderName is NVARCHAR, unique and the primary key. The other columns are all BIT and NOT NULL.. Input parameters (BIT) to the TVF correspond to each service provider type to be returned: manufacturer, supplier, etc and are set depending on whether the user is seeking only manufacturers, only suppliers, both manufacturers and suppliers, etc - I'm sure you get the picture.

My problem is how to construct the Where clause. If I simply use the input parameters in the Where clause I'll get the wrong result set as, assuming a request for manufacturers only, I'd also be retrieving records where IsSupplier, IsWarrantyGuaranteeProvider and IsMaintenanceProvider is zero. Currently, I'm using a lot of If statements to construct the Where clause but, having added a new service provider type, the number of If statements, by my reckoning, will amount to factorial 4. There must be a better way, methinks.

Please tell me I've overlooked something obvious and I've not wasted too much of anyone's time.

Neil

SELECT ServiceProvider_ID,
               ProviderName,
               IsMaintenanceProvider,
               IsManufacturer,
               IsSupplier,
               IsWarrantyGuaranteeProvider 
               FROM T_ServiceProviders
               WHERE (
                 (IsMaintenanceProvider = 0) OR
                 (IsManufacturer = 1) OR
                 (IsSupplier = 0) OR
                 (IsWarrantyGuaranteeProvider = 0)
                 );
whereclause
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why would you want to use the OR logical operator? If you want to find only rows which have IsManufacturer=1 and the other bits set to 0, you should use the AND logical operator.

Please provide examples of what you want to achieve.

0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

How about this?

And if this suggestion works, change your function to be an inline Table Valued Function, it will perform a lot better.

SELECT ServiceProvider_ID,
    ProviderName,
    IsManufacturer,
    IsSupplier,
    IsWarrantyGuaranteeProvider 
    FROM T_ServiceProviders
    WHERE ((@IsManufacturer=1 AND IsManufacturer=1) OR (@IsSupplier=1 AND IsSupplier = 1) OR (@IsWarrantyGuaranteeProvider=1 AND IsWarrantyGuaranteeProvider = 1))
ORDER BY ProviderName
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Neil avatar image
Neil answered

Thank you for your reply. A service provider can be of one, several, or all provider types. If I were to use the And operator to find rows where IsManufacturer = 1 then I would need to know, in advance, whether service providers were also IsSupplier, IsMaintenanceProvider and so on, otherwise I would only retrieve rows where service providers were IsManufacturer only, i.e. not also IsSupplier, IsMaintenanceProvider, etc.

Below I have attached the TVF before the addition of the IsMaintenanceProvider option, hopefully to demostrate what I'm trying to achieve. I've looked at this issue for so long now that it could easily be that I can no longer see the wood for the trees.

ALTER FUNCTION [dbo].[tvf_ServiceProviders] 
(
    @IsManufacturer bit = 0,
    @IsSupplier bit = 0,
    @IsWarrantyGuaranteeProvider bit = 0
)
RETURNS @ResultTable TABLE
(
    ServiceProvider_ID int,
    ProviderName nvarchar(64),
    IsManufacturer bit,
    IsSupplier bit,
    IsWarrantyGuaranteeProvider bit
)
AS

BEGIN

        IF NOT (
            (@IsManufacturer IS NULL) OR 
            (@IsSupplier IS NULL) OR 
            (@IsWarrantyGuaranteeProvider IS NULL) AND NOT
            (@IsManufacturer = 0 AND @IsSupplier = 0 AND @IsWarrantyGuaranteeProvider = 0)
            )

            BEGIN

                IF @IsManufacturer = 1 AND @IsSupplier = 1 AND @IsWarrantyGuaranteeProvider = 1

                    BEGIN
                        --
                        --    All service provider types
                        --
                        INSERT INTO @ResultTable
                        SELECT ServiceProvider_ID,
                            ProviderName,
                            IsManufacturer,
                            IsSupplier,
                            IsWarrantyGuaranteeProvider 
                            FROM T_ServiceProviders
                            WHERE (IsManufacturer = 1 OR IsSupplier = 1 OR IsWarrantyGuaranteeProvider = 1)
                        ORDER BY ProviderName

                    END

                ELSE

                    BEGIN

                        IF @IsManufacturer = 1
                            --
                            --    At least manufacturer-type
                            --
                            BEGIN

                                IF @IsSupplier = 0 AND @IsWarrantyGuaranteeProvider = 0
                                    --
                                    --    Only manufacturer-type
                                    --
                                    BEGIN

                                        INSERT INTO @ResultTable
                                        SELECT ServiceProvider_ID,
                                            ProviderName,
                                            IsManufacturer,
                                            IsSupplier,
                                            IsWarrantyGuaranteeProvider 
                                            FROM T_ServiceProviders
                                            WHERE (IsManufacturer = 1) 
                                        ORDER BY ProviderName

                                    END

                                ELSE

                                    BEGIN

                                        IF @IsSupplier = 1 AND @IsWarrantyGuaranteeProvider = 0
                                            --
                                            --    Manufacturer and supplier-types
                                            --
                                            BEGIN

                                                INSERT INTO @ResultTable
                                                SELECT ServiceProvider_ID,
                                                    ProviderName,
                                                    IsManufacturer,
                                                    IsSupplier,
                                                    IsWarrantyGuaranteeProvider 
                                                    FROM T_ServiceProviders
                                                    WHERE (IsManufacturer = 1 OR IsSupplier = 1) 
                                                ORDER BY ProviderName

                                            END

                                        ELSE

                                            BEGIN

                                                IF @IsSupplier = 0 AND @IsWarrantyGuaranteeProvider = 1
                                                    --
                                                    --    Manufacturer or warranty/guarantee-types
                                                    --
                                                    BEGIN

                                                        INSERT INTO @ResultTable
                                                        SELECT ServiceProvider_ID,
                                                            ProviderName,
                                                            IsManufacturer,
                                                            IsSupplier,
                                                            IsWarrantyGuaranteeProvider 
                                                            FROM T_ServiceProviders
                                                            WHERE (IsManufacturer = 1 OR IsWarrantyGuaranteeProvider = 1) 
                                                        ORDER BY ProviderName

                                                    END

                                            END
                                            
                                    END

                            END

                        ELSE
                            --
                            --    Definitely not manufacturer-type
                            --
                            BEGIN

                                IF @IsSupplier = 1
                                    --
                                    --    At least supplier-type
                                    --
                                    BEGIN

                                        IF @IsWarrantyGuaranteeProvider = 0
                                            --
                                            --    Only supplier-type
                                            --
                                            BEGIN

                                                INSERT INTO @ResultTable
                                                SELECT ServiceProvider_ID,
                                                    ProviderName,
                                                    IsManufacturer,
                                                    IsSupplier,
                                                    IsWarrantyGuaranteeProvider 
                                                    FROM T_ServiceProviders
                                                    WHERE (IsSupplier = 1) 
                                                ORDER BY ProviderName

                                            END

                                        ELSE
                                            --
                                            --    Supplier or warranty/guarantee-types
                                            --
                                            BEGIN

                                                INSERT INTO @ResultTable
                                                SELECT ServiceProvider_ID,
                                                    ProviderName,
                                                    IsManufacturer,
                                                    IsSupplier,
                                                    IsWarrantyGuaranteeProvider 
                                                    FROM T_ServiceProviders
                                                    WHERE (IsSupplier = 1 OR IsWarrantyGuaranteeProvider = 1) 
                                                ORDER BY ProviderName

                                            END

                                    END

                                ELSE
                                    --
                                    --    Definitely not manufacturer or supplier-types
                                    --
                                    BEGIN

                                        IF @IsWarrantyGuaranteeProvider = 1
                                            --
                                            --    Warranty/guarantee-type
                                            --
                                            BEGIN

                                                INSERT INTO @ResultTable
                                                SELECT ServiceProvider_ID,
                                                    ProviderName,
                                                    IsManufacturer,
                                                    IsSupplier,
                                                    IsWarrantyGuaranteeProvider 
                                                    FROM T_ServiceProviders
                                                    WHERE (IsWarrantyGuaranteeProvider = 1) 
                                                ORDER BY ProviderName

                                            END

                                    END


                            END

                    END

            END
    
RETURN 
END


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Neil avatar image
Neil answered

Magnus - pure genius - many thanks and I've changed to an iTvf.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.