Having problems writing sql script which includes an IN clause with multiple departments but display only limited 3 departments on report.

I want to only display 3 departments

probably can change it like this?

select d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name,
sum(e.SALARY) Total
from employees e inner join departments d
on(e.department_id = d.department_id)
where e.department_id IN( 10, 20, 30 ) << Put in your 3 departments' ID here?
group by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name
order by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name

I need the user to be able to choose from all the departments within the IN clause. But I want to only display 3 departments on the report at a time.

I also I want to use a substitution variable if possible to select 3 department at once.
.

But I want to only display 3 departments on the report at a time.

Ok, so just pass the variables into the IN clause there?

btw, are you using Oracle or SQL Server?

the in clause is somewhat a problem in that area

a temp table could solve that

create global temporary table search_dep (department_id number) on commit delete rows;

Select allOpen in new window

in yer app, switch off autocommit before inserting into the temp table :

insert into search_dep ( department_id) values (1);
insert into search_dep ( department_id) values (5);
insert into search_dep ( department_id) values (6);

Select allOpen in new window

select d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name, 
sum(e.SALARY) Total
from employees e 
  inner join departments d on (e.department_id = d.department_id)
  inner join search_dep s on (s.department_id = d.department_id)
group by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name
order by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name

Select allOpen in new window

and why exactly do you need the sum of a salary ? (or sum of 1 row) ?

If you have more than three departments in the "in" clause, how is Oracle supposed to know that you only want three returned, and how is Oracle supposed to determine which three to return?

Substitution variables can contain any single variable, they don't contain "in" lists.

You can if you use dynamic SQL.
(And pass in the where clause).

Yes, dynamic SQL offers that flexibility.   And it comes with a performance penalty and a security liability.  So, is that a good option here?  We can't make that decision for the user.

Exactly. As a DBA, I have had my issues with Dynamic SQL. It is harder to debug, tougher to identify which source code is actually running, must be reparsed, etc.

So we don't run willy nilly into its use. However, the questioner might consider if it is suitable for their requirement and implementation.

Dynamic SQL is probably not a good option.

So you have a situation where you want your where clause to be restricted based on user input at runtime. This is a classic example of where dynamic SQL is used.

Just stating it's a bad idea, is a bad idea. Offer up some criticism or reasoning. Mark has already highlighted the impacts on performance. That is something the questioner might be able to live with.

A time and a place.
Each feature has an appropriate time and an appropriate place in which it should be used. The author may know, but you cannot make that decision.

Whilst it can make a DBAs life harder, it isn't a developer's job to make our lives easier. It is to build software that meets the needs of its users, *ideally* operating in an easy to maintain and highly performant manner, but not exclusively.

lol, jtrifts,
don't assume developers know how the underlying system actually works
often i come across programs which have a poor locking mechanism, because the oracle locking mechanism just isn't understood
> for example: use of "LOCK TABLE" to prevent simultaneous updates

an in clause doesn't lend itself well for searching with variables
a secondary table solves that problem
and performs well too

by design you shouldn't use an in clause for searching

using substitution variables is by defintion using dynamic sql
your sql statement is not fixed, it changes by value of the substitution variable

This tendency towards "DBA superiority" is tedious. I've seen enough of it over the past 20+ years. (It is the reason I stopped contributing to "LazyDBA" site, despite having some great minds there.

At any rate, a real problem with DBAs sometimes is the "black and white" nature of things. Dynamic SQL is one of them...and I've sometimes been guilty of being on the other side.

But let's look at the implementation. We're talking employees and departments. The largest employer in the world, has 2.3M "employees", but most will only have thousands or tens of thousands (or fewer). The number of departments then is likely to be in tens or Mac, hundreds. Thus the performance consideration is such that, although having to reparse the query, it's execution is likely to be against a relatively small dataset. As such, I'm less concerned about the performance impact.

Sure, we can get into the IN Vs EXISTS discussion, but AGAIN... "IN" as a feature is not evil... It just means you're going to get more comparisons than strictly needed. Here, having three items in your IN will have quite a small cost against a small dataset.
Let's solve real world problems with real world solutions. And get away from gold plating. I'm an 80/20 guy. Spend your time producing business value, bringing to market quickly. Don't look for perfection if good enough will do.

jtrifts,
you assume they only store 1 record per employee ...
don't make assumptions

a database lends itself to abuse very well :)

My only assumption is that the data sets are relatively small.

Question author: please confirm the sizes of your data sets in terms of number of records and size of records.

The dataset is small but we are having huge performance issues.

Thanks, everyone.  Always learning.

performance issues ?
how is this question related to that ?

i was thinking this was trying something out ... :)

if you have actual performance problems ... top 20 cpu querries:

select * from (select * from v$sql order by buffer_gets desc) where rownum <= 20;

Select allOpen in new window

try and found out for each query, if it's a background or a frontend query ... or if it's related to the performance problem
and try and tune that
>> usually adding indexes helps