CODEWITHSUNDEEP
sqlt-sql
DeadlyChambers
DeadlyChambers

Reputation: 5285

How can I combine this sql where clause to use one Select?

So if you look at the last WHERE clause. Where inv_location_id in One of them I am using an integer and the other is a set.

How can I combine these?

IF ( @inventory_location_id = NULL )
    SELECT  clinic_desc ,
            dbo.fn_get_clinic_address_formatted(@application_name,
                                                clinic_id, NULL, NULL,
                                                'STANDARD', NULL, NULL,
                                                NULL, NULL) AS formatted_address ,
            vfc_pin ,
            contact ,
            email_address ,
            phone ,
            fax
    FROM    dbo.clinics
    WHERE   clinic_id IN (
            SELECT  clinic_id
            FROM    dbo.clinic_inv_locations
            WHERE   inv_location_id IN (
                    SELECT  ilr.clinic_id
                    FROM    dbo.inv_location_reconciliation ilr
                    WHERE   inv_location_reconciliation_id = @reconciliation_id ) )

ELSE
    SELECT  clinic_desc ,
            dbo.fn_get_clinic_address_formatted(@application_name,
                                                clinic_id, NULL, NULL,
                                                'STANDARD', NULL, NULL,
                                                NULL, NULL) AS formatted_address ,
            vfc_pin ,
            contact ,
            email_address ,
            phone ,
            fax
    FROM    dbo.clinics
    WHERE   clinic_id IN (
            SELECT  clinic_id
            FROM    dbo.clinic_inv_locations
            WHERE   inv_location_id IN ( @inventory_location_id ) )

Upvotes: 0

Views: 66

Answers (3)

mo.
mo.

Reputation: 4245

Just change the where to be

WHERE (
    (
        @inventory_location_id IS NULL AND (
            inv_location_id IN (
            SELECT  ilr.clinic_id
            FROM    dbo.inv_location_reconciliation ilr
            WHERE   inv_location_reconciliation_id = @reconciliation_id )
        )
    )
    OR
    (
        @inventory_location_id IS NOT NULL AND (
            inv_location_id IN ( @inventory_location_id )
        )
    )
)

It can be made more concise, but I left it big and wordy for clarity here :)

Upvotes: 2

DeadlyChambers
DeadlyChambers

Reputation: 5285

This is what I ended up going with

DECLARE @temp TABLE (clinic_id INT )
IF(@reconciliation_id IS NOT NULL)
  INSERT INTO @temp SELECT ilr.clinic_id 
                    FROM dbo.inv_location_reconciliation ilr 
                    WHERE inv_location_reconciliation_id =@reconciliation_id
ELSE
  INSERT INTO @temp (clinic_id) VALUES (@inventory_location_id)
SELECT clinic_desc, dbo.fn_get_clinic_address_formatted(@application_name, clinic_id,      NULL, NULL, 'STANDARD', null,NULL, NULL, NULL) AS formatted_address,vfc_pin, contact, email_address, phone, fax 
FROM dbo.clinics 
WHERE clinic_id IN (SELECT clinic_id 
                    FROM dbo.clinic_inv_locations 
                    WHERE inv_location_id in (SELECT clinic_id FROM @temp))

Upvotes: 0

Hogan
Hogan

Reputation: 70523

I like CTEs with complicated queries because I think it makes them easier to read, so I would do something like below. There are many other ways to solve this problem and I don't claim this is the the fastest.

I haven't tested it so proceed with caution

with clinic_list AS
(
    SELECT  ilr.clinic_id as clinic_id  
    FROM    dbo.inv_location_reconciliation ilr
    WHERE   inv_location_reconciliation_id = @reconciliation_id 
    UNION ALL
    SELECT  clinic_id
    FROM    dbo.clinic_inv_locations
    WHERE   inv_location_id IN ( @inventory_location_id ) 
)
    SELECT  clinic_desc ,
            dbo.fn_get_clinic_address_formatted(@application_name,
                                                clinic_id, NULL, NULL,
                                                'STANDARD', NULL, NULL,
                                                NULL, NULL) AS formatted_address ,
            vfc_pin ,
            contact ,
            email_address ,
            phone ,
            fax
    FROM    dbo.clinics
    WHERE   clinic_id IN (SELECT  clinic_id FROM    clinic_list)

I also have a strong suspicion that using a function here is the wrong choice. Functions are quite horrible in SQL and are often the cause of bad performance and scaling.

Upvotes: 1

Related Questions