Complex CASE WHEN SQL query

Question

I have SQL query as below:

SELECT COUNT(*) FROM table t
WHERE LENGTH(TRIM(TRANSLATE(t.field, 'BWE123', ' '))) > 0
OR SUBSTR(t.field, 0, 1) IN ('0', '1')

Above query checks that field in the table does not contain values other than 'BWE123' and whether the first character is not 0 or 1. If the field accepts other characters than allowed query returns the number of invalid records. I would like to extend above query:

If count = 0 below query should be started:

SELECT count(*) FROM table

If count > 0 below query should be started:

SELECT * FROM table t
WHERE LENGTH(TRIM(TRANSLATE(t.field, 'BWE123', ' '))) > 0
OR SUBSTR(t.field, 0, 1) IN ('0', '1')

Anyone have an idea how to create that kind of query?

Also i know that i can user regexp instead of LENGTH - TRIM - TRANSLATE, I will change it later.

Regards

I am sorry for the confusion, I will try to explain from the beginning what I want to achieve. At the moment I have exactly such queries:

SELECT 'MY_TABLE' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.field1' AS COLUMN FROM my_table mt
WHERE LENGTH(TRIM(TRANSLATE(mt.field1, '0123456789', ' '))) > 0
OR mt.field1 IS NULL
UNION
SELECT 'MY_TABLE' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.field2' AS COLUMN FROM my_table mt
WHERE LENGTH(TRIM(TRANSLATE(mt.field2, '0123456789.-*', ' '))) > 0
OR mt.field2 IS NULL;

Result of their execution is below table (Let's assume that field1 value in table is '1234' so we have 0 errors, field2 value is 'a1234' so we have an error)

TABLE       ERROR_COUNT     COLUMN 
-----       -----------     ------
MY_TABLE    0               mt.field1
MY_TABLE    1               mt.field2

I give as an example the smallest table that I have to test, I also have tables that have 200 or more columns. What I want to achieve: If we have errors (as for field2) I want to create and run additional query which return below table (in second query result tab):

TABLE       INVALID_CHAR    COLUMN 
-----       -----------     ------
MY_TABLE    a               mt.field2

Example table (IP):

ID      IP_NO           PHONE   
-----       --------        ---------
101     192.205.230.70      +535950331
10#     192.205a.230.70     +672819233
103     192.205.230.70      +991873112
10!     192.205.230.70      +764616233

Query:

SELECT 'IP' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.ID' AS COLUMN FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.id, '0123456789', ' '))) > 0
OR ip.id IS NULL
UNION
SELECT 'IP' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.ip_no' AS COLUMN FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.ip_no, '0123456789.', ' '))) > 0
OR ip.ip_no IS NULL
UNION
SELECT 'IP' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.phone' AS COLUMN FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.phone, '+0123456789', ' '))) > 0
OR ip.phone IS NULL

Result table:

TABLE       ERROR_COUNT     COLUMN 
-----       -----------     ------
IP          2               mt.id
IP          1               mt.ip_no
IP          0               mt.phone

Table which I want to create:

TABLE       INVALID_CHAR    COLUMN 
-----       -----------     ------
IP          #               mt.id
IP          !               mt.id
IP          a               mt.ip_no

You can't have a query that returns either a single (numeric, count) column or multiple columns. The number of columns has to be fixed. How would whatever calls this interpret the results? You could maybe append the count as an extra column and union the results, but it's unclear what you want to happen. — Alex Poole
Actually at this moment we can skip the condition on count = 0. With second query I want to verify which invalid characters appear if count in first query is > 0 — Łukasz Skowroński
If you don't want to look at the count then your final query already only lists those rows? — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2019-09-10T08:35:11

A query always has to return the same number of columns. The closest I can see to what you are suggesting is to union together the results of two queries, with dummy columns so the projection is always the same; and use a CTE to reduce repetition. Something like:

WITH cte as (
  SELECT t.field,
    COUNT(*) OVER () as total_count,
    CASE WHEN LENGTH(TRIM(TRANSLATE(t.field, 'BWE123', ' '))) > 0
              OR SUBSTR(t.field, 0, 1) IN ('0', '1')
         THEN 1
         ELSE 0
    END as is_invalid,
    COUNT(CASE WHEN LENGTH(TRIM(TRANSLATE(t.field, 'BWE123', ' '))) > 0
                    OR SUBSTR(t.field, 0, 1) IN ('0', '1')
               THEN 1
          END) OVER () as invalid_count
  FROM your_table t
)
SELECT cte.field, null as total_count
FROM cte
WHERE is_invalid = 1
UNION ALL
SELECT null, max(total_count)
FROM cte
WHERE invalid_count = 0

Only one branch of the union can find any results for any set of base data. You either get a result like:

FIELD  TOTAL_COUNT
------ -----------
0                 
1BWE23            

or

FIELD  TOTAL_COUNT
------ -----------
                 4

Complex CASE WHEN SQL query

1 Answers