Home » SQL & PL/SQL » SQL & PL/SQL » SQL query. (Oracle 11g.)
SQL query. [message #683314] |
Mon, 28 December 2020 09:00 |
|
akarra21
Messages: 6 Registered: December 2020
|
Junior Member |
|
|
Hi I have a requirement as follows
please find the ex. data script to run
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;
--First test Query to run with dept number 10,20,30
--return the result set of all dept 10 and 20 and 30in both tables.
SELECT D.DEPTNO,
D.DNAME,
D.LOC,
E.EMPNO,
E.ENAME,
E.JOB,
E.DEPTNO
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO(+)
AND ((D.DEPTNO IN(&PNIN_DEPT_NO)) AND(E.DEPTNO IN(&PNIN_EDEPT_NO)))
For second query define parameter with
DEFINE pnin_dept_no = "0,40"
SELECT D.DEPTNO,D.DNAME,D.LOC, E.EMPNO,E.ENAME,E.JOB,E.DEPTNO FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO(+)
AND ((D.DEPTNO IN(40)) AND(E.DEPTNO IN(nvl(E.DEPTNO,0))))
The department 40 data from department should be displayed.
Expected result for second query is to get the result set of
Department 40.
In short i am running this query in an oracle report and will be passing 2 parameters from forms
results should be displayed in all scenerios
1) passing both parameter1 and parameter2 - result set expected
2) passing only parameter1 and nvl(0) as parameter2 - result set expected
3) passing only parameter2 and nvl(0) as parameter1 - result set expected
can you please advise.
Thanks very much.
--moderator edit: added [code] tags
[Updated on: Mon, 28 December 2020 09:34] by Moderator Report message to a moderator
|
|
|
|
Re: SQL query. [message #683318 is a reply to message #683314] |
Mon, 28 December 2020 13:57 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Condition AND(E.DEPTNO IN(&PNIN_EDEPT_NO))) simply makes no sense and isn'ty needed. But if you want to use it then you must speicy outer join for every table EMP column:
SELECT D.DEPTNO,
D.DNAME,
D.LOC,
E.EMPNO,
E.ENAME,
E.JOB,
E.DEPTNO
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO(+)
AND ((D.DEPTNO IN(&PNIN_DEPT_NO)) AND(E.DEPTNO(+) IN(&PNIN_EDEPT_NO)))
/
Enter value for pnin_dept_no: 0,40
Enter value for pnin_edept_no: 0,40
old 10: AND ((D.DEPTNO IN(&PNIN_DEPT_NO)) AND(E.DEPTNO(+) IN(&PNIN_EDEPT_NO)))
new 10: AND ((D.DEPTNO IN(0,40)) AND(E.DEPTNO(+) IN(0,40)))
DEPTNO DNAME LOC EMPNO ENAME JOB DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ----------
40 OPERATIONS BOSTON
SQL>
SY.
|
|
|
|
Re: SQL query. [message #683372 is a reply to message #683318] |
Tue, 05 January 2021 11:58 |
|
akarra21
Messages: 6 Registered: December 2020
|
Junior Member |
|
|
Hi,
thanks for the response but
I need to change the below query and pass parameters both dept number and empno
as follows
SELECT D.DEPTNO,
D.DNAME,
D.LOC,
E.EMPNO,
E.ENAME,
E.JOB,
E.DEPTNO
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO(+)
AND ((D.DEPTNO IN(&PNIN_DEPT_NO)) AND(E.EMPNO(+) IN(&PNIN_EEMP_NO)))
Enter value for pnin_dept_no: 10,20
Enter value for pnin_eemp_no: 7782,7839,7934,7369
20 RESEARCH DALLAS 7369 SMITH CLERK 20
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 10
if i pass only dept number and null in emp no all employees of that dept should display.
eg
Enter value for pnin_dept_no: 10,20
Enter value for pnin_eemp_no: 0
then should return all employees in both departments.(10 and 20)
can you please advise.
thanks very much
|
|
|
|
Re: SQL query. [message #683377 is a reply to message #683373] |
Tue, 05 January 2021 14:35 |
|
akarra21
Messages: 6 Registered: December 2020
|
Junior Member |
|
|
SELECT D.DEPTNO,
D.DNAME,
D.LOC,
E.EMPNO,
E.ENAME,
E.JOB,
E.DEPTNO
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO(+)
AND ((D.DEPTNO IN(&PNIN_DEPT_NO)) AND(E.EMPNO(+) IN(&PNIN_EEMP_NO)))
Enter value for pnin_dept_no: 10,20
Enter value for pnin_eemp_no: 7782,7839,7934,7369
old 10: AND ((D.DEPTNO IN(&PNIN_DEPT_NO)) AND(E.EMPNO(+) IN(&PNIN_EEMP_NO)))
new 10: AND ((D.DEPTNO IN(10,20)) AND(E.EMPNO(+) IN(7782,7839,7934,7369)))
DEPTNO DNAME LOC EMPNO ENAME JOB DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ----------
20 RESEARCH DALLAS 7369 SMITH CLERK 20
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 10
SQL>
if i pass only deptno and null in empno all employees of that dept should display.
but showing no data in emp table columns
eg
Enter value for pnin_dept_no: 10,20
Enter value for pnin_eemp_no: 0
SQL> /
Enter value for pnin_dept_no: 10,20
Enter value for pnin_eemp_no: 0
old 10: AND ((D.DEPTNO IN(&PNIN_DEPT_NO)) AND(E.EMPNO(+) IN(&PNIN_EEMP_NO)))
new 10: AND ((D.DEPTNO IN(10,20)) AND(E.EMPNO(+) IN(0)))
DEPTNO DNAME LOC EMPNO ENAME JOB DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ----------
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
SQL>
expected to show all employees of deptno 10 and deptno 20
please advise.
thanks.
|
|
|
|
|
Re: SQL query. [message #683380 is a reply to message #683379] |
Tue, 05 January 2021 15:23 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This looks like trolling: being deliberately stupid in an attempt to make people angry.
Good bye.
|
|
|
Re: SQL query. [message #683381 is a reply to message #683379] |
Wed, 06 January 2021 00:02 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
akarra21 wrote on Tue, 05 January 2021 22:21...
can you please tell me how you want me to format the data.
...
John Watson wrote on Mon, 28 December 2020 16:32...
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Michel Cadot wrote on Tue, 05 January 2021 19:09John Watson wrote on Mon, 28 December 2020 16:32...
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...
John Watson wrote on Tue, 05 January 2021 22:03If you would like help, please understand that repeatedly ignoring the rules for good behaviour will not help. It isn't difficult.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
[Updated on: Wed, 06 January 2021 00:06] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:32:17 CDT 2024
|