CODEWITHSUNDEEP
sql
Hardik Mishra
Hardik Mishra

Reputation: 14877

Pass % using IN SQL

I have a query

select dim.name as division,dm.name as department, cuser.contractor_name, cuser.contractor_surname,d.status as status,
d.date_submission,cm.name as code,scm.name as subCode,cuser.company_name as company, gt.grade_name as grade, gm.grade_value as gradeValue
from document d, selected_grade_map sgm, selected_grade_details sgd, code_master cm,code_master scm,
contractor_user_master_ipms cuser, department_master dm, division_master dim, grade_master gm, grade_type gt
where 
convert(datetime, convert(varchar(10), d.date_submission, 101)) <= convert(datetime, '2011-06-22') AND 
convert(datetime, convert(varchar(10), d.date_submission, 101)) >= convert(datetime, '2010-12-22') AND
d.application_status = 'NEW' 
and d.status != 'Forked'
and d.id = sgm.doc_id 
and sgm.selected_grade_id = sgd.selected_grade_id
and sgd.code_id = cm.code_id 
and sgd.sub_code_id = scm.code_id 
and d.contractor_user = cuser.id 
and d.department_id = dm.id 
and d.division_id = dim.id 
and sgd.grade_id = gm.grade_id
and gm.grade_type_id = gt.grade_type_id
and d.department_id IN ('%')
and dim.id IN (1,2)
and cm.code_id like '%' 
and scm.code_id like '%' 
and gt.grade_type_id like '%'
and d.status like '%'
and cuser.reservation_category like '%'
and cuser.company_name like '%'

Situation is that .. User can select multiple criteria as filter but If a user does not select any filter then I need to pass all. So, I tried using % in SQL IN. But Here columns are integer so. It will not allow this.

The query is static and written in iReport

How do I can use pass all in SQL like

d.department_id IN ('%')

Upvotes: 0

Views: 109

Answers (6)

Bivoauc
Bivoauc

Reputation: 853

If you want to select all values if your input is null you could use COALESCE

so instead of

cm.code_id like '%'

use

cm.code_id = COALESCE(@inputvalue, cm.code_id)

COALESCE will select the first non-null value. So if you give a null input it will select itself.

COALESCE Article

Upvotes: 0

niktrs
niktrs

Reputation: 10066

In case input is none can you give a dummy value like -1?

Then transform your query like 

(  
  d.department_id IN (@Userparam) 
    OR
  (-1) IN (@Userparam) -- If you pass -1 then it returns all user departments
)

Upvotes: 3

James Anderson
James Anderson

Reputation: 27478

You could possibly try doing something similar with "BETWEEN". Default to BETWEEN(0,9999) and substitute BETWEEN(100,100) if the user enters department 100.

However the best advice would be to tailor the SQL to each query if that's possible using your language in your environment.

If you are in a more constrained environment where you can only use static SQL it may make more sense to write three or four simpler queries with fewer substitutions.

Upvotes: -1

Thilo
Thilo

Reputation: 262534

The hack with Like '%' is horrible even where it works. You must investigate how you can alter the query dynamically.

If there is really only one integer column that you want to either filter on or not filter on, you might get away with having two static queries to select from (one that has the condition, one that does not).

Upvotes: 1

Jonathan Hall
Jonathan Hall

Reputation: 79604

The % character is only special for LIKE. When you use it with = or IN(), etc, it is taken literally.

Upvotes: 0

Koterpillar
Koterpillar

Reputation: 8104

An IN query won't allow you to pass a string, and that string will not match on any integer because it is not in LIKE. Look into how your iReport can alter this part of the query dynamically.

Upvotes: 2

Related Questions