Solved

SQl help with selection

Posted on 2016-11-07
14
41 Views
Last Modified: 2016-11-08
I have a lookup table called Bill which haves actives and inactive values

KIndly help with the logic in stored proc so i get proper values if user selects various combinations of valid and invalid Bills in selection
IN Code I am showing two different dropdown list one for actives and one for inactives where both have select all and select none values

Now I pass a null value if user wants to select all the bill values and pass -1 if user does not want to get any values.

Now if user selects all for actives and selects none for in actives my stored proc is failing please help:

Values in Bill Table
ID     Value    Isvalid
-1                   True
2      Test1      True
3     Test2      False

Values in Table A
ID  BillID   Name
1     2         John
2     3         Jim

select * from Table A
left outer Join Bill on Bill.ID = A.BillID

where @ActiveBillID is NULL or A.BillID = @ActiveBillID and
@InActiveBillID is NULL or A.BillID = @InActiveBillID
0
Comment
Question by:welcome 123
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
EDIT: First thing that stands out is you must use parenthesis when mixing AND / OR to ensure the operations occur in the desired order.  Not sure I completely understand the logic, but assuming you want to return records where both conditions are true:

WHERE  ( @ActiveBillID is NULL OR A.BillID = @ActiveBillID )
AND
              ( @InActiveBillID is NULL OR  A.BillID = @InActiveBillID )
1
 

Author Comment

by:welcome 123
Comment Utility
Yes I do have Parenthesis already  by mistake didn't post it above.

I need the logic where I pass various combinations like NUll, _1 or _1 NUll, 2,3 etc values of Bill ID for active and inactive parameters.

If user select Null for Active(which means select all) and -1 for inactive which means select nothing I am not getting values and vice versa
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
If user select Null for Active(which means select all) and -1 for inactive which means select nothing I am not getting values and vice versa

Probably because you're matching on the same ID, which can only have a single value. So if the @ActiveBillID  and @InActiveBillID values are different, this would never be true

WHERE  ( @ActiveBillID is NULL OR A.BillID = @ActiveBillID )
AND      ( @InActiveBillID is NULL OR  A.BillID = @InActiveBillID )

You could try using OR  instead:

WHERE   @ActiveBillID is NULL
OR           A.BillID = @ActiveBillID
OR          @InActiveBillID is NULL
OR          A.BillID = @InActiveBillID

... but not sure I understand the filtering/relationship between the two tables.  Using the sample data, what results are you expecting?
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
try this..


Table creation

--

CREATE TABLE Bill
(
	 ID INT
	,Value VARCHAR(25)    
	,Isvalid VARCHAR(5)
)
GO

INSERT INTO Bill VALUES
(-1  ,   NULL         ,  'True' ),
(2   ,   'Test1'    ,  'True'),
(3   ,   'Test2'     , 'False')
GO

CREATE TABLE TableA
(
	 ID INT
	,Value VARCHAR(25)    
	,Name VARCHAR(50)
)
GO

INSERT INTO TableA VALUES
(1  ,   2         ,'John'),
(2  ,   3         ,'Jim')
GO

Open in new window



Query to fetch data


NULL trail

--


DECLARE @ActiveBillID AS INT = NULL

SELECT * FROM Bill b
LEFT OUTER JOIN TableA a ON b.ID = a.ID
WHERE b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID 
                     WHEN @ActiveBillID = -1 THEN -999999
			    ELSE
					 @ActiveBillID
				END


--

Open in new window


Output
------------------------
ID      Value      Isvalid      ID      Value      Name
-1      NULL      True      NULL      NULL      NULL
2      Test1      True      2      3      Jim
3      Test2      False      NULL      NULL      NULL


--


DECLARE @ActiveBillID AS INT = -1

SELECT * FROM Bill b
LEFT OUTER JOIN TableA a ON b.ID = a.ID
WHERE b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID 
                     WHEN @ActiveBillID = -1 THEN -999999
			    ELSE
					 @ActiveBillID
				END


--

Open in new window



Output
------------------------
ID      Value      Isvalid      ID      Value      Name


GO

--


DECLARE @ActiveBillID AS INT = 2

SELECT * FROM Bill b
LEFT OUTER JOIN TableA a ON b.ID = a.ID
WHERE b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID 
                     WHEN @ActiveBillID = -1 THEN -999999
			    ELSE
					 @ActiveBillID
				END


--

Open in new window



Output
------------------------
ID      Value      Isvalid      ID      Value      Name
2      Test1      True      2      3      Jim

Hope it helps !!
0
 

Author Comment

by:welcome 123
Comment Utility
Thanks for the help all experts. I am sorry I did not explain the question clearly as was in a hurry my bad.

Expert Pawan: The above query with examples you gave is fantastic. In your first example if I pass @ActiveBillID as Null then  ID 3 will not return as its Isvalid column is False which turns out to be inactive

@ActiveBillID  = all Bill IDS where Isvalid is True
@InActiveBillID = all Bill ID's where isValid is False

So now I need to pass both @ActiveBillID (which are the Id values of Isvalid true Ids in bill table ) and also @InActiveBillID parameters together which could be cominations like:
@ActiveBillID  = Null,  @InActiveBillID = Null
Result: all the bill id values will be returned as user selected Select ALL for both
ID  BillID   Name
1     2         John
2     3         Jim

@ActiveBillID  = -1,  @InActiveBillID = -1
Result: NO results as user selected Select None for both

@ActiveBillID  = Null,  @InActiveBillID = -1
Result:  User selected Select ALL for ActiveBILL and SelectNone for InactiveBill
ID  BillID   Name
1     2         John

@ActiveBillID  = -1,  @InActiveBillID = Null
Result: User selected None for ActiveBill and Select All for InactiveBill
ID  BillID   Name
2     3         Jim

@ActiveBillID  =2 ,  @InActiveBillID = 3
Result: User selected a value for each of ActiveBill as well as Inactive Bill from their respective dropdownlists
ID  BillID   Name
1     2         John
2     3         Jim


@ActiveBillID  =2 ,  @InActiveBillID = -1
Result: User selected a value for each of ActiveBill and select None for Inactive Bill
ID  BillID   Name
1     2         John

and you can imagine some more like the above
0
 

Author Comment

by:welcome 123
Comment Utility
I used the below where I know there are mistakes and hitting me huge syntax errors obviously

A.ID  in ( CASE WHEN @ActiveBillID  IS NULL and  @InActiveBillID IS NULL THEN A.ID  
                     WHEN @ActiveBillID  = -1 and   @InActiveBillID = -1 THEN -999999
                               WHEN @ActiveBillID  IS NULL and  @InActiveBillID = -1 THEN @ActiveBillID
                               WHEN @ActiveBillID  = -1 and  @InActiveBillID IS NULL THEN  @InActiveBillID
                               WHEN @ActiveBillID  IS NOT NULL AND  @InActiveBillID IS NOT NULL THEN @ActiveBillID , @InActiveBillID  
                              
                               WHEN @ActiveBillID  IS NOT NULL AND  @InActiveBillID IS NULL THEN @ActiveBillID
                               WHEN @ActiveBillID  IS NULL AND  @InActiveBillID IS NOT NULL THEN  @InActiveBillID
                               WHEN @ActiveBillID  IS NOT NULL AND  @InActiveBillID = -1 THEN @ActiveBillID
                               WHEN @ActiveBillID  = -1 AND  @InActiveBillID IS NOT NULL THEN  @InActiveBillID
)
0
 
LVL 16

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
Here it is complete code --  <<Used tables I created above.>>

--1. Try1

DECLARE @ActiveBillID AS INT = NULL
DECLARE @InActiveBillID AS INT = NULL


--

DECLARE @ActiveBillID AS INT = NULL
DECLARE @InActiveBillID AS INT = NULL

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID 
                     WHEN @ActiveBillID = -1 THEN -999999
			    ELSE
					 @ActiveBillID
				END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID 
                     WHEN @InActiveBillID = -1 THEN -999999
			    ELSE
					 @InActiveBillID
				END AND b.Isvalid = 'False' ) )

GO

--

Open in new window






Output
---------------------

ID      Value      Name
1      2          John
2      3          Jim





--1. Try2
DECLARE @ActiveBillID AS INT = -1
DECLARE @InActiveBillID AS INT = -1



--

DECLARE @ActiveBillID AS INT = -1
DECLARE @InActiveBillID AS INT = -1

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID 
                     WHEN @ActiveBillID = -1 THEN -999999
			    ELSE
					 @ActiveBillID
				END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID 
                     WHEN @InActiveBillID = -1 THEN -999999
			    ELSE
					 @InActiveBillID
				END AND b.Isvalid = 'False' ) )

GO

--

Open in new window





Output
---------------------

ID      Value      Name






--1. Try3
DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = 3


--

DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = 3

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID 
                     WHEN @ActiveBillID = -1 THEN -999999
			    ELSE
					 @ActiveBillID
				END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID 
                     WHEN @InActiveBillID = -1 THEN -999999
			    ELSE
					 @InActiveBillID
				END AND b.Isvalid = 'False' ) )

GO

--

Open in new window





Output
---------------------
ID      Value      Name
1      2         John
2      3         Jim






--1. Try4
DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = -1


--

DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = -1

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID 
                     WHEN @ActiveBillID = -1 THEN -999999
			    ELSE
					 @ActiveBillID
				END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID 
                     WHEN @InActiveBillID = -1 THEN -999999
			    ELSE
					 @InActiveBillID
				END AND b.Isvalid = 'False' ) )

GO

--

Open in new window


 


Output
---------------------


ID      Value      Name
1      2              John




Hope it helps!!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I can only see this done by using a dynamic SQL or have some queries built and run the one for the case you want:
IF @ActiveBillID IS NULL AND @InActiveBillID IS NULL 
	select * 
	from Table A
		Left outer Join Bill on Bill.ID = A.BillID
ELSE IF @ActiveBillID IS NULL 
	select * 
	from Table A
		Left outer Join Bill on Bill.ID = A.BillID
	where A.BillID = @InActiveBillID 
ELSE IF @InActiveBillID IS NULL 
	select * 
	from Table A
		Left outer Join Bill on Bill.ID = A.BillID
	where A.BillID = @ActiveBillID 
ELSE
	select * 
	from Table A
		Left outer Join Bill on Bill.ID = A.BillID
	where A.BillID = @ActiveBillID OR A.BillID = @InActiveBillID 

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
And this is the version for dynamic SQL:
DECLARE @MySQL VARCHAR(MAX)
DECLARE @MyFilter VARCHAR(MAX) = ''

SET @MySQL = 'select * 
		from Table A
		Left outer Join Bill on Bill.ID = A.BillID'

IF @ActiveBillID IS NOT NULL 
	SET @MyFilter = @MyFilter + ' WHERE A.BillID = ' + CAST(@ActiveBillID AS VARCHAR)
ELSE IF @InActiveBillID IS NOT NULL 
	IF @MyFilter = ''
		SET @MyFilter = @MyFilter + ' WHERE A.BillID = ' + CAST(@InActiveBillID AS VARCHAR)
	ELSE
		SET @MyFilter = @MyFilter + ' OR A.BillID = ' + CAST(@InActiveBillID AS VARCHAR)

SET @MySQL = @MySQL + @MyFilter

EXEC(@MySQL)

Open in new window

0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Have you tried the code I gave?

Thank you!
0
 

Author Comment

by:welcome 123
Comment Utility
Thanks a Lot Expert Pawan . It worked like a charm. I am really impressed with your test cases which made the requirement even clear to myself. You are simply awesome. I am so glad I pay experts exchange just because of experts like you.

I am sorry I get a chance to try your solution expert  Vitor Montalvão and sure that might work too but caught with a already working solution by Expert Pawan.
0
 

Author Closing Comment

by:welcome 123
Comment Utility
Thank You!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I am sorry I get a chance to try your solution expert  Vitor Montalvão and sure that might work too but caught with a already working solution by Expert Pawan.
You're the client you should choose what's best for you. We are only giving you options but the last call is always yours.
I just hope that you understand the differences from one solution to the others. When you have time just run all the solutions to check if they are working. If they do you then you can also try to check the performance differences so you can understand them better for future issues.
Cheers.
1
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Thanks for the help all experts. I am sorry I did not explain the question clearly as was in a hurry my bad.

Ohh.. those examples clarify things, thanks. Personally, I would have gone with a UNION. Though a bit more verbose, it makes the intent of the query it a bit more obvious than with CASE statements IMO.  Edit: Though as Victor mentioned, it is always a good idea to understand the different options and ramifications of each one.

--- Get specified "active" records
SELECT a.ID, a.Name
FROM   TableA a INNER JOIN Bill b ON a.BillID = b.ID
WHERE  a.BillID = b.ID
AND    b.IsValid = 'True'
AND   ( @ActiveBillID IS NULL OR a.BillID = @ActiveBillID )
UNION ALL
--- Get specified "inactive" records
SELECT a.ID, a.Name
FROM   TableA a INNER JOIN Bill b ON a.BillID = b.ID
WHERE  a.BillID = b.ID
AND    b.IsValid = 'False'
AND   ( @InActiveBillID IS NULL OR a.BillID = @InActiveBillID )

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now