On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, October 21, 2008

Where: How to Search for Various Conditions

This query presents the operator with a choice of how to find data. After selecting a search field, and entering a value, a decode is used to generate SQL code. The New_Value clause sets that equal to &xWhere, and it is used with the 'Where' clause.

(I have tried incorporating it directly into the Where with no luck.)
Prompt
Prompt +------------------------------------------+
Accept Choice Prompt '| Search by 1-EmpNo 2-EName : '
Accept Value Prompt '| Please enter Value : '
Prompt +------------------------------------------+

Set Term Off
Column WhereClause New_Value xWhere
Select Decode(&Choice, 1, 'EmpNo=&Value',
2, 'EName='||Upper('''&Value''')
)
As
WhereClause
From Dual;
Set Term On

Select EmpNo, EName, Job, HireDate, DeptNo
From Emp
Where &xWhere;

No comments:

Post a Comment