How can I use data_type in where clause

Question

1. Is there any way to use column data type in where clause in Oracle SQL? My table contains columns with different data types and, as a result, I want to have only columns with 'char' data type.

2. I need to check, whether at least 1 value in every column is set to 'T'. Is there better way to check this, than using if statment?

Thank you for help.

EDIT

To be more specific, I add simple table to represent the problem.

Moreover, as I said, there are more columns, including some date columns. Let's say, that user has both

+----------+------------+------+------+--------+----------+--------+
| Datatype | Privileges | Open | Edit | Delete | Download | Upload |
+----------+------------+------+------+--------+----------+--------+
| PNG      | Default    | T    | T    | T      | T        | T      |
| JPEG     | Default    | T    | T    | T      | T        | T      |
| PDF      | Default    | T    | F    | F      | T        | T      |
| DOCX     | Default    | T    | T    | F      | T        | T      |
| PNG      | Test       | T    | F    | F      | T        | F      |
| PDF      | Test       | T    | F    | F      | T        | F      |
+----------+------------+------+------+--------+----------+--------+

Moreover, as I said, there are more columns, including some date columns. Let's say, that user has both privileges. I need to check, which datatype user has access to and what can he do with each. I.e this user can work with PNG file and execute every kind of operation, because of Default privileges. Test privileges offers some operations on PNG file too, but not every o them. This is like bit OR operation I assume. If at least one is set to T, then user can work with file using set operation. There is more datatypes, privileges and columns, this is just simple example.

I suppose, that there is no fast way to find only CHAR columns, so I have to simply write every column in Select.

Answer 1

Alex Poole has a good comment--if you already know which columns contain the T/F data, there is no need to query by data type, since you already would know which columns to check by name. You can just query by column name.

I'll provide an answer in case you do Not already know which columns will need to be checked but will include far below an example of static sql as well. I'm not sure exactly what is meant by your second requirements, so I'll add a couple examples that take different angles. These will use EXECUTE IMMEDIATE, as Gordon Linoff mentioned in a comment.

This first example interprets your requirements that you don't know the table beforehand (otherwise you can just check its CHAR colums and query those directly), but want to check whether at least one row has a T for each of a given TABLE's CHAR columns (across rows).

The block takes a TABLE_NAME as a parameter, then builds a dynamic query that checks whether each COLUMN has at least one entry in the table with a value of T.

First create a test table with different data types including some CHAR:

CREATE TABLE HETEROGENEOUS (
  CHAR_COL_1      CHAR(10),
  NUMBER_COL_1    NUMBER,
  CHAR_COL_2      CHAR(10),
  TIMESTAMP_COL_1 TIMESTAMP,
  CHAR_COL_3      CHAR(10)
);

Then add some test data. This first load has two of three columns with at least one T value, so will fail the test.

INSERT INTO HETEROGENEOUS VALUES ('Chewbacca', 1, 'VOLTRON', SYSTIMESTAMP, 'Gundam');
INSERT INTO HETEROGENEOUS VALUES ('T', 1, 'Frodo', SYSTIMESTAMP, 'Starscream');
INSERT INTO HETEROGENEOUS VALUES ('X', 1, 'Bombadil', SYSTIMESTAMP, 'T');

Then run the block. This block counts the number of CHAR columns, then executes a dynamic query to count how many columns have at least one row with a T value in each CHAR column and compares the count of T columns with the count of CHAR columns:

DECLARE
  V_TABLE_NAME VARCHAR2(128) := 'HETEROGENEOUS';
  V_SQL_TEXT VARCHAR2(32000);
  V_REQUIRED_COLUMN_COUNT NUMBER := 0;
  V_OK_COLUMN_COUNT NUMBER := 0;

BEGIN
  EXECUTE IMMEDIATE
  UTL_LMS.FORMAT_MESSAGE('SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ''%s'' AND DATA_TYPE = ''CHAR''',V_TABLE_NAME)
  INTO V_REQUIRED_COLUMN_COUNT;

  SELECT 'SELECT ' ||LISTAGG('(SELECT COALESCE(MIN(1),0) FROM '||V_TABLE_NAME||' WHERE TRIM('||
                             COLUMN_NAME||') = ''T'' AND ROWNUM = 1)','+')
  WITHIN GROUP (ORDER BY COLUMN_ID) || ' FROM DUAL'
  INTO V_SQL_TEXT
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = V_TABLE_NAME
        AND DATA_TYPE = 'CHAR' GROUP BY TABLE_NAME;

  EXECUTE IMMEDIATE V_SQL_TEXT INTO V_OK_COLUMN_COUNT;

  IF V_OK_COLUMN_COUNT < V_REQUIRED_COLUMN_COUNT
  THEN
    DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('Required at least: %s columns to have 1+ T values but only found: %s',TO_CHAR(V_REQUIRED_COLUMN_COUNT),TO_CHAR(V_OK_COLUMN_COUNT)));
  ELSE
    DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('All: %s CHAR columns have at least one T value',TO_CHAR(V_REQUIRED_COLUMN_COUNT)));
  END IF;

END;
/

Result:

Required at least: 3 columns to have 1+ T values but only found: 2

Then add another row to get the last required T value:

INSERT INTO HETEROGENEOUS VALUES ('Deckard', 1, 'T', SYSTIMESTAMP, 'Megatron');

And run again:

All: 3 CHAR columns have at least one T value

The static SQL equivalent (if you already know you table/columns) is:

SELECT (SELECT COALESCE(MIN(1), 0) FROM HETEROGENEOUS
        WHERE TRIM(CHAR_COL_1) = 'T' AND ROWNUM = 1) +
       (SELECT COALESCE(MIN(1), 0) FROM HETEROGENEOUS
        WHERE TRIM(CHAR_COL_2) = 'T' AND ROWNUM = 1) +
       (SELECT COALESCE(MIN(1), 0) FROM HETEROGENEOUS
        WHERE TRIM(CHAR_COL_3) = 'T' AND ROWNUM = 1)
FROM DUAL;

If your requirement instead is to find ROWs where at least one CHAR column has a T value, the approach is the same, but the dynamic query is different.

This second example will find all the rows where at least one CHAR column has a value of T (and just print them):

DECLARE

  V_TABLE_NAME VARCHAR2(128) := 'HETEROGENEOUS';
  V_SQL_TEXT VARCHAR2(32000);
  TYPE REFCURSOR IS REF CURSOR;
  V_REFCURSOR REFCURSOR;
  V_ROWID VARCHAR2(64);
BEGIN

  SELECT 'SELECT ROWID FROM '||V_TABLE_NAME||' WHERE 1 = ANY ( '||LISTAGG('DECODE(TRIM('||COLUMN_NAME||'),''T'',1,0)  ',',') WITHIN GROUP (ORDER BY COLUMN_ID)||')'
  INTO V_SQL_TEXT
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = V_TABLE_NAME
        AND DATA_TYPE = 'CHAR'
  GROUP BY TABLE_NAME;

  OPEN V_REFCURSOR FOR V_SQL_TEXT;
  LOOP
    FETCH V_REFCURSOR INTO V_ROWID;
    EXIT WHEN V_REFCURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('RowId: %s',V_ROWID));
  END LOOP;
  CLOSE V_REFCURSOR;
END;
/

Running it gives the three rows that have a T in any CHAR column:

RowId: AAGKHPAFJAABL49AAB
RowId: AAGKHPAFJAABL49AAC
RowId: AAGKHPAFJAABL49AAD

Or alternatively get the single row that has NO T values in their CHAR columns, by switching from ANY to ALL:

WHERE 1 = ANY

WHERE 1 <> ALL 

Gives one row:

RowId: AAGKHPAFJAABL49AAA

The static eqivalent (if you already know your table and don't need to use data type) is:

SELECT ROWID
FROM HETEROGENEOUS
WHERE 1 = ANY (DECODE(TRIM(CHAR_COL_1), 'T', 1, 0),
               DECODE(TRIM(CHAR_COL_2), 'T', 1, 0),
               DECODE(TRIM(CHAR_COL_3), 'T', 1, 0));​