Home » SQL & PL/SQL » SQL & PL/SQL » SQL query. (Oracle 11g.)
SQL query. [message #683314] Mon, 28 December 2020 09:00 Go to next message
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 #683315 is a reply to message #683314] Mon, 28 December 2020 09:32 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
I think you are making a basic error in your SQL. There is no point in using an outer join, if you then add a filter on the non-preserved table. Think about it: if the outer join generates a row in the non-preserved table, its columns will all NULL. So any filter will reject them.

And btw, to do the moderator bit:

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Re: SQL query. [message #683318 is a reply to message #683314] Mon, 28 December 2020 13:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
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 #683324 is a reply to message #683318] Tue, 29 December 2020 09:14 Go to previous messageGo to next message
akarra21
Messages: 6
Registered: December 2020
Junior Member
thanks very much it worked.
thanks a lot again.
Happy New Year.
Re: SQL query. [message #683372 is a reply to message #683318] Tue, 05 January 2021 11:58 Go to previous messageGo to next message
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 #683373 is a reply to message #683372] Tue, 05 January 2021 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Mon, 28 December 2020 16:32
...
And btw, to do the moderator bit:

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
If you don't follow the forum rules you will no more get help.

Re: SQL query. [message #683377 is a reply to message #683373] Tue, 05 January 2021 14:35 Go to previous messageGo to next message
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 #683378 is a reply to message #683377] Tue, 05 January 2021 15:03 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
If 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.

Re: SQL query. [message #683379 is a reply to message #683378] Tue, 05 January 2021 15:21 Go to previous messageGo to next message
akarra21
Messages: 6
Registered: December 2020
Junior Member
i am sorry i am not sure
how to do the formatting
i used the Instant SQL Formatter to format and copy and paste.
can you please tell me how you want me to format the data.
thanks for the help
Re: SQL query. [message #683380 is a reply to message #683379] Tue, 05 January 2021 15:23 Go to previous messageGo to next message
John Watson
Messages: 8531
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 Go to previous message
Michel Cadot
Messages: 67809
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:09
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.
...


John Watson wrote on Tue, 05 January 2021 22:03
If 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

Previous Topic: oracle pl/sql
Next Topic: opening and closing balance
Goto Forum:
  


Current Time: Tue Apr 13 17:39:14 CDT 2021