Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query execute more records in WHERE CLAUSE (Oracle Database 10g, Windows XP)
SQL Query execute more records in WHERE CLAUSE [message #674375] Wed, 23 January 2019 23:30 Go to next message
rehmankhan
Messages: 22
Registered: July 2018
Junior Member
I have SQL Query in which execute data from VIEW. In this Query i have 4 parameters, all parameters data stored in another table. 3 Parameters have data in table and 1 Parameter has no record. In 3 Parameters there are total "1080" records, but this query execute more than "13000" records.

My SQL Query:




SELECT DISTINCT COL1, COL2, COL3, COL4

FROM TABLE

WHERE (COL1 IN (SELECT VAL FROM RPPARAMLIST where flg = 'I') OR COL1 = COL1)
AND (COL2 IN (SELECT TO_NUMBER(VAL) FROM RPPARAMLIST where flg = 'P') OR COL2 = COL2)
AND (COL3 IN (SELECT TO_NUMBER(VAL) FROM RPPARAMLIST where flg = 'C') OR COL3 = COL3)
AND (COL4 IN (SELECT TO_NUMBER(VAL) FROM RPPARAMLIST where flg = 'U') OR COL4 = COL4);


In "RPPARAMLIST" table 3 Columns have records and last column COL4 has no record

"RPPARAMLIST" Table:

https://ibb.co/MVfv1nn

When i remove OR condition in WHERE CLAUSE Then show empty records.

Sample Data:

1080 records

https://i.stack.imgur.com/07aaE.png

Output Data:

13619 Records

https://i.stack.imgur.com/Xpj8z.png

Please help me, How to solve this problem?
Re: SQL Query execute more records in WHERE CLAUSE [message #674378 is a reply to message #674375] Thu, 24 January 2019 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67134
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: SQL Query execute more records in WHERE CLAUSE [message #674380 is a reply to message #674375] Thu, 24 January 2019 01:26 Go to previous messageGo to next message
John Watson
Messages: 8268
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 Go to previous message
cookiemonster
Messages: 13894
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)
.....
Previous Topic: changing telephone format
Next Topic: RETURNING BULK COLLECT INTO COLLECTION
Goto Forum:
  


Current Time: Thu May 28 04:06:44 CDT 2020