|
|
Re: SQL Query execute more records in WHERE CLAUSE [message #674380 is a reply to message #674375] |
Thu, 24 January 2019 01:26 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Perhaps you need to consider the possibility that some of the values you are comparing could be NULL. For example:orclx>
orclx> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17:00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20:00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22:00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02:00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28:00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01:00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09:00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19:00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17:00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08:00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23:00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03:00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03:00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23:00:00:00 1300 10
14 rows selected.
orclx> select * from emp where comm=comm;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20:00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22:00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28:00:00:00 1250 1400 30
7844 TURNER SALESMAN 7698 1981-09-08:00:00:00 1500 0 30
orclx>
And, by the way, I wish you would not say "record" when you mean "row".
|
|
|
Re: SQL Query execute more records in WHERE CLAUSE [message #674385 is a reply to message #674380] |
Thu, 24 January 2019 04:10 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That where clause doesn't make any sense.
You're almost certainly hitting the null issue John mentions above, and taking the null issue into account that where clause is functionally equivalent to:
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
AND col3 IS NOT NULL
AND col4 IS NOT NULL
For each column you're saying:
Column value must match result of sub-query or column value must match itself.
Well column value will always match itself unless the column is null.
And if it is null it won't match the sub-query either.
So all the sub-queries are doing there is slowing the query down.
Did you want logic of the form:
Column must match sub-query result unless sub-query returns no rows?
If so then you want something like this:
SELECT DISTINCT COL1, COL2, COL3, COL4
FROM TABLE
LEFT JOIN RPPARAMLIST rp1 ON flg = 'I'
LEFT JOIN RPPARAMLIST rp2 ON flg = 'P'
....
WHERE (col1 = rp1.val OR rp1.val IS NULL)
AND (col2 = rp2.val OR rp2.val IS NULL)
.....
|
|
|