CODEWITHSUNDEEP
sqloracle-databaseselect
Pierre de la Verre
Pierre de la Verre

Reputation: 87

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

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?

Upvotes: 2

Views: 83

Answers (3)

Jon Heller
Jon Heller

Reputation: 36817

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.

Upvotes: 0

MT0
MT0

Reputation: 167981

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.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142733

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))

Upvotes: 1

Related Questions