How to check both conditions in the query and get a result

Question

I have a query where I am trying to add functionality to check for the below parameters:

'CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)','CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)','CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)','CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)'

I have data where I get the Parameter name (from above) and the count_found (which is the occurrence of the parameter given as 1 if found, 0 if not found)

Here I need to check if CRYPTO_CHECKSUM_TYPES_SERVER has either SHA256 or SHA512 set. If neither is set then the query needs to report. Same for CRYPTO_CHECKSUM_TYPES_CLIENT.

If I run the current query I have something like this:

HOST_NAME COUNT_FOUND   PARAMETER                                   FILE_CHECKED
host123    0           CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)    /local/dbms/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora
host123    0           CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)    /local/dbms/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora
host123    1           CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)    /local/dbms/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora
host123    0           CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)    /local/dbms/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora

I am now trying to add the check with current condition:

where (PARAMETER in ('CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)','CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)','CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)','CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)'))

new condition:

where (PARAMETER in ('CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)','CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)','CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)','CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)'))
and ((PARAMETER='CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)' and count_found=0) and (PARAMETER='CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)' and count_found=0))

But after adding the new condition the query does not return anything.

What is the issue here?

I tried to add the condition but the query returned no rows.How can i check for 2 parameters which are in 2 different rows.

Please post data as text not images. What is the expected output of the new query? — Serg, Jun 05 '23 at 12:39
Does this answer your question? [SQL query: Simulating an "AND" over several rows instead of sub-querying](https://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying) — astentx, Jun 05 '23 at 14:48

score 0 · Answer 1 · answered Jun 05 '23 at 12:33

Query you wrote doesn't return anything because no rows satisfy condition.

Did you mean something like this?

Sample data:

SQL> WITH
  2     test (count_found, parameter)
  3     AS
  4        (SELECT 0, 'CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)' FROM DUAL
  5         UNION ALL
  6         SELECT 0, 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)' FROM DUAL
  7         UNION ALL
  8         SELECT 0, 'CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)' FROM DUAL
  9         UNION ALL
 10         SELECT 0, 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)' FROM DUAL)

Query:

 11  SELECT *
 12    FROM test
 13   WHERE (parameter, count_found) IN (('CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)', 0),
 14                                      ('CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)', 0),
 15                                      ('CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)', 0),
 16                                      ('CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)', 0));

COUNT_FOUND PARAMETER
----------- -------------------------------------
          0 CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)
          0 CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)
          0 CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)
          0 CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)

SQL>

MT0 · Answer 2 · 2023-06-05T12:41:12.653

You are trying to find a single row where PARAMETER is 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)' and 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)' at the same time. That is never going to happen as a row can only contain a single value.

Instead, you want to check when the row is one value OR the other value and use OR rather than AND:

where PARAMETER in (
        'CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)',
        'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)',
        'CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)',
        'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)'
      )
and   (  (   PARAMETER = 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)'
         and count_found=0 )
      OR 
         (   PARAMETER = 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)'
         and count_found=0 )
      )

Which can be simplified to:

where PARAMETER in (
        'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)',
        'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)'
      )
and   count_found=0

If you want server values as well then:

where PARAMETER in (
        'CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256)',
        'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)',
        'CRYPTO_CHECKSUM_TYPES_SERVER=(SHA512)',
        'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)'
      )
and   count_found=0

instead of trying to find a single row where PARAMETER is 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA256)' and 'CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA512)' (which is wrong) can i check if the count_found is zero for a given host_name and file_checked,which i think would work — Roshni Rabi, Jun 05 '23 at 13:45
@RoshniRabi We do not have access to your sample data or know what the expected output would be for that sample data. Without knowing what you are trying to achieve it is difficult to say whether your suggestion would work. — MT0, Jun 05 '23 at 13:54

How to check both conditions in the query and get a result

2 Answers2