Home » SQL & PL/SQL » SQL & PL/SQL » Challenge and A challenging question (merged 2 cross-posts)
icon14.gif  Challenge and A challenging question (merged 2 cross-posts) [message #194025] Wed, 20 September 2006 08:23 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

First there is a table caleed department with 3 fields empname,empid,departmentname.

Using sql query(dont use pl/sql) To display name of employee when inputing id of that particular employee(id starts from 1),if we enter id as 0 then want to display all the name in that table.Can any one give a solution for this using sql query
A challenging question [message #194026 is a reply to message #194025] Wed, 20 September 2006 08:26 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

First there is a table caleed department with 3 fields empname,empid,departmentname.

Using sql query(dont use pl/sql) To display name of employee when inputing id of that particular employee(id starts from 1),if we enter id as 0 then want to display all the name in that table.Can any one give a solution for this using sql query
Re: Challenge [message #194030 is a reply to message #194025] Wed, 20 September 2006 08:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
create table emp (ename  varchar2(30),empno  number, deptno  number);

insert into emp values ('Fisher',10,10);
insert into emp values ('Brown',20,10);
insert into emp values ('Oliver',30,20);
insert into emp values ('Flasheart',40,20);

SQL> SELECT ename,empno,deptno
  2  FROM   emp
  3  WHERE  empno = &&empno
  4  union all
  5  SELECT ename,empno,deptno
  6  FROM   emp
  7  WHERE  0 = &&empno;
Enter value for empno: 10
old   3: WHERE  empno = &&empno
new   3: WHERE  empno = 10
old   7: WHERE  0 = &&empno
new   7: WHERE  0 = 10

ENAME                               EMPNO     DEPTNO
------------------------------ ---------- ----------
Fisher                                 10         10

SQL> undefine empno
SQL> SELECT ename,empno,deptno
  2  FROM   emp
  3  WHERE  empno = &&empno
  4  union all
  5  SELECT ename,empno,deptno
  6  FROM   emp
  7  WHERE  0 = &&empno;
Enter value for empno: 0
old   3: WHERE  empno = &&empno
new   3: WHERE  empno = 0
old   7: WHERE  0 = &&empno
new   7: WHERE  0 = 0

ENAME                               EMPNO     DEPTNO
------------------------------ ---------- ----------
Fisher                                 10         10
Brown                                  20         10
Oliver                                 30         20
Flasheart                              40         20
Re: A challenging question [message #194031 is a reply to message #194026] Wed, 20 September 2006 08:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Don't post questions to multiple forums
2) This isn't really an Expert question.
Re: Challenge [message #194033 is a reply to message #194025] Wed, 20 September 2006 08:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As anacedent would say, "homework tutor is down the hall & 2nd door on the left."
Re: A challenging question [message #194119 is a reply to message #194026] Wed, 20 September 2006 17:28 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Hi!

Probably it is the answer:

select empname,empid,departmentname from
department where empid=decode(:p_id,0,empid,:p_id)

P_id is the parameter.


Zoltán Patalenszki
Re: A challenging question [message #194290 is a reply to message #194031] Thu, 21 September 2006 07:29 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Probably you can try with this

select empname,empid,departmentname from
department where
empid = :pid or :pid = 0
empid=decode(:p_id,0,empid,:p_id)


Re: A challenging question [message #194291 is a reply to message #194119] Thu, 21 September 2006 07:30 Go to previous message
Aju
Messages: 94
Registered: October 2004
Member
I am sorry forgot to delete the last line I copied

Should be like

select empname,empid,departmentname from
department where
empid = :pid or :pid = 0

Previous Topic: Query needed
Next Topic: The procedure execute very slow,how to change it!
Goto Forum:
  


Current Time: Wed Dec 07 05:05:14 CST 2016

Total time taken to generate the page: 0.12113 seconds