• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Help with SQL Query

I have a query that need to filter data based on 4 input parameter supplied to the stored procedure.

The parameters are
      Board_id, class_id, subject_id,exam_category_id

If board_id >-1 and remaining of the parameters are not -1, then
      the board_id criteria in the where clause should be used.

ie if my query is
      SELECT * FROM EXAM_RESULTS_VIEW
      WHERE
            
            SUBJECT_ID                  =      @SUBJECT_ID  AND
            CLASS_ID                  =      @CLASS_ID  AND
            BOARD_ID                  =      @BOARD_ID AND
            EXAM_CATEGORY_ID            =      @EXAM_CATEGORY_ID

Is there a way i could do it to see if the where clause criteria uses only if the value of any parameter is greater than -1

to illustrate it further,
      if class_id=2 and board_id=1 and subject_id=-1 and exam_category_id=-1
then the query should use on the Board_ID and Class_ID to filter the data based on the data supplied.

0
TECH_NET
Asked:
TECH_NET
  • 5
  • 4
  • 4
2 Solutions
 
appariCommented:
try like this

SELECT * FROM EXAM_RESULTS_VIEW
      WHERE
           
            (SUBJECT_ID                  =      @SUBJECT_ID or @SUBJECT_ID = -1) AND
            (CLASS_ID                  =      @CLASS_ID or @CLASS_ID = -1)  AND
            (BOARD_ID                  =      @BOARD_ID or @BOARD_ID = -1)  AND
            (EXAM_CATEGORY_ID            =      @EXAM_CATEGORY_ID or @EXAM_CATEGORY_ID = -1)
0
 
SharathData EngineerCommented:
try like this.


SELECT * FROM EXAM_RESULTS_VIEW
      WHERE 
            
            SUBJECT_ID                  =      case when @SUBJECT_ID > -1 then @SUBJECT_ID else  SUBJECT_ID end AND
            CLASS_ID                  =      case when @CLASS_ID > -1 then @CLASS_ID else CLASS_ID end AND
            BOARD_ID                  =      case when @BOARD_ID > -1 then @BOARD_ID else BOARD_ID end and
            EXAM_CATEGORY_ID            =      case when @EXAM_CATEGORY_ID > -1 then @EXAM_CATEGORY_ID else EXAM_CATEGORY_ID end

Open in new window

0
 
TECH_NETAuthor Commented:
Sharath,

can you explain how your solution is different from that suggested by appari. The solution of appari does work.

I am curious to know which would be good from a performance standpoint.

Appari,

When you use in the where clause
(SUBJECT_ID                  =      @SUBJECT_ID or @SUBJECT_ID = -1)
so what does the subject_id equate to. what i meant is if the subject_id supplied is -1, it would be
(SUBJECT_ID                  =      -1 or @SUBJECT_ID = -1)

I am understanding what is the difference between SUBJECT_ID =-1  and @SUBJECT_ID=1
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
appariCommented:
>>(SUBJECT_ID                  =      -1 or @SUBJECT_ID = -1)
using "or" operator so even if one condition is true the expression evaluates to true for all the records. when @subject_id is -1 it simply ignores the check on Subject_id column.
0
 
SharathData EngineerCommented:
<< Is there a way i could do it to see if the where clause criteria uses only if the value of any parameter is greater than -1

to illustrate it further,
      if class_id=2 and board_id=1 and subject_id=-1 and exam_category_id=-1
then the query should use on the Board_ID and Class_ID to filter the data based on the data supplied. >>

With appari's solution, for the example you mentioned, the query becomes as


SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1 or -1 = -1)
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1)
   AND (CLASS_ID = 2)  
   AND (BOARD_ID = 1  or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)
   
appari's query is using all the filters ( your intention is not to user filter on Subject_Id and EXAM_CATEGORY_ID which is not happening in this case)

I am not sure how are you getting your deserved result with appari's query.

My query will become as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE SUBJECT_ID = case when -1 > -1 then -1 else SUBJECT_ID end
   AND CLASS_ID = case when 2 > -1 then 2 else CLASS_ID end
   AND BOARD_ID = case when 1 > -1 then 1 else BOARD_ID end
   and EXAM_CATEGORY_ID = case when -1 > -1 then -1 else EXAM_CATEGORY_ID end

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE SUBJECT_ID = SUBJECT_ID
   AND CLASS_ID = 2
   AND BOARD_ID = 1
   and EXAM_CATEGORY_ID = EXAM_CATEGORY_ID

In my query, filters are applied on only CLASS_ID and BOARD_ID. Comparing SUBJECT_ID with SUBJECT_ID and EXAM_CATEGORY_ID with EXAM_CATEGORY_ID will not effect the end result.

I have attached a sample data. You can test with @col values as 10, -1 , 1 and see the deference between two queries.
declare @table as table (col int)
insert into @table values (10)
insert into @table values (20)
insert into @table values (1)
insert into @table values (-1)
insert into @table values (30)
declare @col int
select @col = 10
select * from @table where (col = @col or col = -1)
select * from @table where col = case when @col > -1 then @col else col end

Open in new window

0
 
TECH_NETAuthor Commented:
Moderator, Sorry i made a mistake in awarding the point. please revert the point to appari. His solutions did work. I did not try out Sharath_123:

0
 
TECH_NETAuthor Commented:
Moderator, could you spli t he point to both appari and sharath_123.

0
 
TECH_NETAuthor Commented:
Thank sharath, your explaination and insight is very good. Thanks a ton for the detailed illustration.

0
 
appariCommented:
**************
SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1 or -1 = -1)
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1)
   AND (CLASS_ID = 2)  
   AND (BOARD_ID = 1  or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)
   
appari's query is using all the filters ( your intention is not to user filter on Subject_Id and EXAM_CATEGORY_ID which is not happening in this case)
***********
Sharat_123,
check again,
my query evaluates to this query,

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE -1 = -1
   AND CLASS_ID = 2
   AND BOARD_ID = 1
   AND -1 = -1
0
 
SharathData EngineerCommented:
appari - can you test the sample data attached in my post# 26197951 and comment.
0
 
appariCommented:
SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1 or -1 = -1)
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1)
   AND (CLASS_ID = 2)  
   AND (BOARD_ID = 1  or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

---(SUBJECT_ID = -1 or -1 = -1)  this one always becomes true as -1 = -1 is true so this condition is true irrespective of Subject_ID column value and same with  (EXAM_CATEGORY_ID = -1 or -1 = -1)

you check it yourself.
0
 
appariCommented:
the sql in your comment  after --which can be further written as
part is wrong it evaluates to

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE true -- (SUBJECT_ID = -1 or -1 = -1)  ****** as -1 = -1
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND true -- (EXAM_CATEGORY_ID = -1 or -1 = -1) ****** as -1 = -1
0
 
SharathData EngineerCommented:
<< appari - can you test the sample data attached in my post# 26197951 and comment.>>

for the sample set, i provided, getting different results with both queries. Did you get a chance to check that one?
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now