Home » SQL & PL/SQL » SQL & PL/SQL » Changing two clauses into one
Changing two clauses into one [message #185269] Mon, 31 July 2006 18:21 Go to next message
DownHere
Messages: 2
Registered: July 2006
Junior Member
I'm new to PL SQL and totally winging it here. Can anyone help with this query? I have tried a couple ways.
Any guidance appreciated.

Tried this:

select ep.emp_id, emp_name, emp_lastname, emp_firstname, emp_hire_date, count(entpol_id) as NUM_POLICIES, ENTEMPPOL_ENABLED
from ent_emp_policy eep, employee e
where ep.emp_id = e.emp_id
and ep.emp_id in(11093,11368,12399,12441,13052,13717)
having count(entpol_id) > 2
or ep.emp_id = e.emp_id
and ep.emp_id NOT in(11093,11368,12399,12441,13052,13717)
having count(entpol_id) > 1
group by EP.emp_id, emp_name, emp_lastname, emp_firstname, emp_hire_date, entpol_id, ENTEMPPOL_ENABLED
ORDER BY EMP_NAME

got error 01787 at line 1 col 1 - only one clause allowed per query block


tried this to see if I could pull it together

select ep.emp_id, emp_name, emp_lastname, emp_firstname, emp_hire_date, count(entpol_id) as NUM_POLICIES, ENTEMPPOL_ENABLED
from ent_emp_policy ep, employee e
if emp_id in(0001,0002,0003) MinC=2
else MinC=1
endif
where ep.emp_id = e.emp_id
having count(entpol_id) > MinC
group by EP.emp_id, emp_name, emp_lastname, emp_firstname, emp_hire_date, entpol_id, ENTEMPPOL_ENABLED
ORDER BY EMP_NAME;

Got error 933 at line 1 col 1 (states end of Group By line)
Re: Changing two clauses into one [message #185270 is a reply to message #185269] Mon, 31 July 2006 20:38 Go to previous messageGo to next message
ruyue
Messages: 9
Registered: July 2006
Junior Member
You can try this:
select ep.emp_id, emp_name, emp_lastname, emp_firstname, emp_hire_date, count(entpol_id) as NUM_POLICIES, ENTEMPPOL_ENABLED
from ent_emp_policy ep, employee e
where ep.emp_id = e.emp_id
having count(entpol_id) >(case when emp_id in (0001,0002,0003) then 2 else 1 end )
group by EP.emp_id, emp_name, emp_lastname, emp_firstname, emp_hire_date, ENTEMPPOL_ENABLED
ORDER BY EMP_NAME;
Re: Changing two clauses into one [message #185285 is a reply to message #185269] Tue, 01 August 2006 00:18 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

using decode function...

select ep.emp_id, 
	   emp_name, 
	   emp_lastname, 
	   emp_firstname, 
	   emp_hire_date, 
	   count(entpol_id) as NUM_POLICIES, 
	   ENTEMPPOL_ENABLED
 from ent_emp_policy ep, employee e 
 where ep.emp_id = e.emp_id 
group by EP.emp_id, emp_name, emp_lastname, emp_firstname, emp_hire_date, entpol_id, ENTEMPPOL_ENABLED
having count(entpol_id) >(decode(ep.emp_id,11093,2,11368,2,12399,2,12441,2,13052,2,13717,2,1)) 
ORDER BY EMP_NAME



Naveen
Re: Changing two clauses into one [message #185404 is a reply to message #185270] Tue, 01 August 2006 10:22 Go to previous message
DownHere
Messages: 2
Registered: July 2006
Junior Member
Marvelous! Works like a dream!

Thanks - to all who answered, too!
Previous Topic: Monitoring all indexes through execute immediate
Next Topic: Get data from more tables which have no direct relationship
Goto Forum:
  


Current Time: Mon Dec 05 12:39:23 CST 2016

Total time taken to generate the page: 0.13850 seconds