It has a system that several companies use, but the changes in the bank tables are not informed.
I would like to create a select that would not return an error when it did not find a field recently taken from a table?
Code:
SELECT
cd_cliente,
nm_cliente,
dt_imp_banco
FROM
dbamv.cliente
WHERE
dt_imp_banco IS NOT EXISTS;
Error message:
ERROR: column "dt_imp_banco" does not exist LINE 4: dt_imp_banco ^ SQL state: 42703 Character: 44
Based on this question of SOEN , it is possible, but not directly. It suggests checking if the column exists and then executing the select :
DECLARE
v_column_exists number := 0;
BEGIN
SELECT count(*) INTO v_column_exists
FROM user_tab_cols
WHERE column_name = 'dt_imp_banco' AND table_name = 'cliente';
IF (v_column_exists > 0) THEN
SELECT cd_cliente, nm_cliente, dt_imp_banco FROM dbamv.cliente;
END IF;
END;
edited
After the comments in this response the idea follows the same, check if the column exists before the query. However, as the need is for the select to be done regardless of whether the column exists (in case of no, the query is made by the other fields), the suggestion would be to create individual validations per column that may exist.
DECLARE
exist_dt_imp_banco number := 0;
BEGIN
SELECT count(*) INTO exist_dt_imp_banco
FROM user_tab_cols
WHERE column_name = 'dt_imp_banco' AND table_name = 'cliente';
SELECT
cd_cliente,
nm_cliente,
CASE exist_dt_imp_banco WHEN 0 THEN null ELSE dt_imp_banco END
FROM dbamv.cliente;
END;
So, if the column exists (in the example, exist_dt_imp_banco > 0 ), the value of the column in the database would be queried; otherwise it would bring null .
DETAIL: The point is that in cases where many columns need to be validated, the code would be too large (and perhaps costly).
Here is an example with three columns to validate.
DECLARE
exist_dt_imp_banco number := 0;
exist_dt_imp_banco_2 number := 0;
exist_dt_imp_banco_3 number := 0;
BEGIN
SELECT count(*) INTO exist_dt_imp_banco
FROM user_tab_cols
WHERE column_name = 'dt_imp_banco' AND table_name = 'cliente';
SELECT count(*) INTO exist_dt_imp_banco_2
FROM user_tab_cols
WHERE column_name = 'ADD_TMSdt_imp_banco_2' AND table_name = 'cliente';
SELECT count(*) INTO exist_dt_imp_banco_3
FROM user_tab_cols
WHERE column_name = 'dt_imp_banco_3' AND table_name = 'cliente';
SELECT
cd_cliente,
nm_cliente,
CASE exist_dt_imp_banco WHEN 0 THEN null ELSE dt_imp_banco END,
CASE exist_dt_imp_banco_2 WHEN 0 THEN null ELSE dt_imp_banco_2 END,
CASE exist_dt_imp_banco_3 WHEN 0 THEN null ELSE dt_imp_banco_3 END
FROM dbamv.cliente;
END;