Home » SQL & PL/SQL » SQL & PL/SQL » query by categorized parameter
query by categorized parameter [message #197790] Thu, 12 October 2006 13:41 Go to next message
Jae Bosou
Messages: 7
Registered: December 2003
Junior Member
I would like to pull query based on a given categorized parameter:

Example table (emp_tbl) with the following columns:
------------------------------------------------------------------
id Name class_code
------------------------------------------------------------------
1234 John Doe FF
1235 Doe Mark SF
1236 Mary John HF
1237 Dung Guyen FP
1238 Lopez Guero HP
1239 Kim Pool SP
------------------------------------------------------------------

I would like to categorize the class code as below:
* FF & FP are in 'Faculty'
* SF & SP are in 'Salary'
* HF & HP are in 'Hourly'

What would be my sql script if I want query using a parameter based on the category listed above?
e.g

select * from emp_tbl
where &CATEGORY_PARAMETER

e.g if I enter 'Faculty' in the parameter, only the faculty group will show:

id Name class_code
------------------------------------------------------------------
1234 John Doe FF
1237 Dung Guyen FP
------------------------------------------------------------------

Thanks for your help

JB
Re: query by categorized parameter [message #197799 is a reply to message #197790] Thu, 12 October 2006 14:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select * from emp_tbl
	where class_code like 'case lower('&CATEGORY_PARAMETER')
				when 'faculty' then 'F%'
				when 'salary' then 'S%'
				when 'hourly' then 'H%' end);
Re: query by categorized parameter [message #197816 is a reply to message #197790] Thu, 12 October 2006 16:19 Go to previous messageGo to next message
Jae Bosou
Messages: 7
Registered: December 2003
Junior Member
Thank you for the quick reply.

The 'case' works very well if the class_code has standardized begining letter like HF & HP for hourly and SF & SP for salary. How about if we have another Hourly code that start with different letter like 'MP', or Salary code - 'PN'?

How would you change the script?

Thank you once again.

JB
Re: query by categorized parameter [message #197826 is a reply to message #197816] Thu, 12 October 2006 18:20 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
In that case, the following should work:

select id, name, class_code
	from emp_tbl
	where (case when lower('&&CC') = 'faculty' and class_code in ('FF','FP') then 1
		    when lower('&&CC') = 'salary' and class_code in ('SP','SF','PN') then 1 
		    when lower('&&CC') = 'hourly' and class_code in ('HF','HP','MP') then 1 end) = 1;

Previous Topic: How to flatten a hierarchy
Next Topic: return multiple row in function
Goto Forum:
  


Current Time: Sun Dec 04 23:04:58 CST 2016

Total time taken to generate the page: 0.07085 seconds