How to execute a select with a WHERE using a not-always-existing column

Question

Simple example: I have some (nearly) identical tables with personal data (age, name, weight, ...)

Now I have a simple, but long SELECT to find missing data:

Select ID
from personal_data_a
where 
born is null
or age < 1
or weight > 500
or (name is 'John' and surname is 'Doe')

Now the problem is: I have some personal_data tables where the column "surname" does not exit, but I want to use the same SQL-statement for all of them. So I have to check (inside the WHERE clause) that the last OR-condition is only used "IF the column surname exists".

Can it be done in a simple way?

You can use the IF EXISTS maybe, and did you look at this question: https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-a-sql-server-table — Iliass Nassibane, May 10 '21 at 10:15
You should fix that schema and have only *one* table for all the people. — sticky bit, May 10 '21 at 10:16
Realistically, no. You could have two different versions of the query in a PL/SQL block and query the data dictionary to determine which version to use. But then you'd also have to use dynamic SQL because the query block wouldn't compile if there was a static query that referenced a column that didn't exist. At that point, it would be (slightly) easier to just use dynamic SQL to assemble the query you want based on what columns exist. But a situation where you have a bunch of similar but not exactly identical tables in the first place generally indicates an architecture problem. — Justin Cave, May 10 '21 at 10:19
SQL relies on structured data. You have semi-structured data. You need to take steps to process your data in to structured data. Such as Littlefoot's answer. Either way, at some point in the process you need to know what columns exist in which table. — MatBailie, May 10 '21 at 10:31

score 1 · Answer 1 · answered May 10 '21 at 10:21

You should have all people in the same table.

If you can't do that for some reason, consider creating a view. Something like this:

CREATE OR REPLACE VIEW v_personal_data
AS
   SELECT id,
          born,
          name,
          surname,
          age,
          weight
     FROM personal_data_a
   UNION ALL
   SELECT id,
          born,
          name,
          NULL AS surname,         --> this table doesn't contain surname
          age,
          weight
     FROM personal_data_b;

and then

SELECT id
  FROM v_personal_data
 WHERE    born IS NULL
       OR age < 1
       OR (    name = 'John'
           AND (   surname = 'Doe'
                OR surname IS NULL))

MT0 · Accepted Answer · 2021-05-10T10:49:13.227

Can it be done in a simple way?

No, SQL statements work with static columns and the statements will raise an exception if you try to refer to a column that does not exist.

You will either:

need to have a different query for tables with the surname column and those without;
have to check in the data dictionary whether the table has the column or not and then use dynamic SQL to build your query; or
to build a VIEW of the tables which do not have that column and add the column to the view (or add a GENERATED surname column with a NULL value to the tables that are missing it) and use that instead.

score 0 · Answer 3 · answered May 12 '21 at 05:40

While dynamic predicates are usually best handled by the application or by custom PL/SQL objects that use dynamic SQL, you can solve this problem with a single SQL statement using DBMS_XMLGEN, XMLTABLE, and the data dictionary. The following code is not what I would call "simple", but it is simple in the sense that it does not require any schema changes.

--Get the ID column from a PERSONAL table.
--
--#4: Get the IDs from the XMLType.
select id
from
(
    --#3: Convert the XML to an XMLType.
    select xmltype(personal_xml) personal_xmltype
    from
    (
        --#2: Convert the SQL to XML.
        select dbms_xmlgen.getxml(v_sql) personal_xml
        from
        (
            --#1: Use data dictionary to create SQL statement that may or may not include
            -- the surname predicate.
            select max(replace(replace(
            q'[
                Select ID
                from #TABLE_NAME#
                where 
                born is null
                or age < 1
                or weight > 500
                or (name = 'John' #OPTIONAL_SURNAME_PREDICATE#)
            ]'
            , '#TABLE_NAME#', table_name)
            , '#OPTIONAL_SURNAME_PREDICATE#', case when column_name = 'SURNAME' then
                'and surname = ''Doe''' else null end)) v_sql
            from all_tab_columns
            --Change this literal to the desired table.
            where table_name = 'PERSONAL_DATA_A'
        )
    )
    where personal_xml is not null
)
cross join xmltable
(
    '/ROWSET/ROW'
    passing personal_xmltype
    columns
        id number path 'ID'
);

See this db<>fiddle for a runnable example.

Thanks Jon, but as you already mentioned this way is far beyond my level. — Pierre de la Verre, May 21 '21 at 12:58

How to execute a select with a WHERE using a not-always-existing column

3 Answers3