Solved

SQl help with selection

Posted on 2016-11-07
14
70 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 41877837
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
ID: 41877939
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_
ID: 41877958
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41878097
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
ID: 41878139
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
ID: 41878163
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 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41878166
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41878614
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 50

Expert Comment

by:Vitor Montalvão
ID: 41878621
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 29

Expert Comment

by:Pawan Kumar
ID: 41878641
@Author - Have you tried the code I gave?

Thank you!
0
 

Author Comment

by:welcome 123
ID: 41878805
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
ID: 41878809
Thank You!
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41878839
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_
ID: 41878854
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

690 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