SQL Select statement - default field value when field doesn't exist

Question

I'm wondering whether there is a way to display some default value for select statement where queries field doesn't exist.

For instance,

SELECT t.name, t.type, t.price, t.brand FROM some_table t;

If the 'brand' field doesn't exist in the some_table I would like this statement to display 'brand' as 'not available'.

Eventually I want to create a view from that select statement. I'm just curious whether there is a way to do that in PL/SQL.

EDIT: To avoid confusion, I want the statement to compile and work when the 'brand' column doesn't exist in the table.

Column name has to be known at compile time. One workaround is to use dynamic-sql — Lukasz Szozda
– Lukasz Szozda, Commented Nov 24, 2015 at 16:20
Just to make sure you understood @lad2025's comment correctly, do you want a default value for when the value is null or do you want to query the fields name, type, price, brand from a table that cannot be determined at runtime? — Julien Blanchard
– Julien Blanchard, Commented Nov 24, 2015 at 16:26
@Grentley I'm curious, how can the structure be known without knowing if a column exist? It should either know that it exists, or know that it doesn't exist. — Julien Blanchard
– Julien Blanchard, Commented Nov 24, 2015 at 16:41
@CM2K That is for SQL Server. The data dictionary tables one would query in Oracle are completely different. Also T-SQL =/= PL/SQL. — Shannon Severance
– Shannon Severance, Commented Nov 24, 2015 at 17:28

Tiago Oliveira de Freitas · Accepted Answer · 2015-11-24 17:01:49Z

5

You can use COALESCE, change null by not available

SELECT t.name, t.type, t.price, COALESCE(t.brand,'not available') AS brand FROM some_table t;

COALESCE is sql standard, but i dont know if Oracle have it.

EDIT:

I think you have to check the field exist in table first, someting like:

Select count(*) into v_column_exists
from user_tab_cols
where column_name = 'ADD_TMS'
  and table_name = 'EMP';

If 1 then EXIST else NOT EXIST, after create the view based on the result.

1:

SELECT t.name, t.type, t.price, t.brand FROM some_table t;

2:

SELECT t.name, t.type, t.price, 'not available' AS brand FROM some_table t;

But i cant see the right way to use this in view.

edited Nov 24, 2015 at 17:01

answered Nov 24, 2015 at 16:26

Tiago Oliveira de Freitas

5462 gold badges7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lukasz Szozda Over a year ago

What will you get when table does not have column brand? Oracle has NVL by the way

Tiago Oliveira de Freitas Over a year ago

@lad2025 Sorry, now i understand the problem, delete the answer ?

Lukasz Szozda Over a year ago

Wait for OP comment. His question can be ambiguous

Grentley Over a year ago

I get a compile error. I want this statement to work when the t.brand column doesn't exist.

Charlie K Over a year ago

Is the problem that the table structure is unknown at runtime: i.e. It may have 4 data fields or it may have 5 data fields? OR Is the problem that "brand" might not have data in the field (that is brand is NULL on certain records)?? Your statements appear to be on both sides -- can you please clarify??

|

Julien Blanchard · Accepted Answer · 2015-11-24 17:06:37Z

2

Unless you go with some really heavy and clunky meta-data gathering where you simply query the master tables to get the data and then unpivot it all into rows, you cannot query a column that doesn't exist because the compiler will start hooking up his stuff and won't find the column.

You can bypass that by using dynamic sql, but then you'll simply have a runtime error instead since you're still querying a column that doesn't exist.

This means that your dynamic SQL will have to exclude the column if it's not in that table, at which point you're better simply removing the column from the static SQL. The only point where dynamic SQL would truly be better is if you have to query like 30+ tables and that you know what you're doing.

So basically ,why do you need to query columns that don't exist? In your case if it's only to be able to preserve an obsolete view, you'd be better to simply maintain your view when it requires updating.

edited Nov 24, 2015 at 17:06

answered Nov 24, 2015 at 16:44

Julien Blanchard

8155 silver badges18 bronze badges

2 Comments

Boneist Over a year ago

I think the idea would be that as part of the dynamic sql, you'd check for the presence of the column and then either add it into the select list, or use the "Not available" literal value in its place, with a suitable alias.

Julien Blanchard Over a year ago

Yeah, my answer was misleading, I rephrased it so it should be better.

Avrajit Roy · Accepted Answer · 2015-11-25 06:24:38Z

I have just seen the question above. It seems very weird design or requirement. I am posting a code snippet which may suffice your problem but ideally this should not be like this.

--So i get chance to look into the question asked simple way to get a workaround for your problem is to fetch out the columns list from table
var p_lst refcursor;
SET serveroutput ON;
DECLARE
  lv_sql LONG;
  lv_tab_name VARCHAR2(100);
  lv_col_chk  VARCHAR2(1000 CHAR);
BEGIN
  FOR I IN
  (SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = 'AVROY' AND TABLE_NAME = 'EMP'
  )
  LOOP
    lv_tab_name:=I.TABLE_NAME;
    lv_sql     :=lv_sql||','||i.column_name;
  END LOOP;
  lv_sql:='SELECT '||SUBSTR(lv_sql,2,LENGTH(lv_sql));
  dbms_output.put_line(lv_sql);
  lv_col_chk:=INSTR(UPPER(lv_sql),'BRAND',1);
  dbms_output.put_line(lv_col_chk);
  IF lv_col_chk = 0 THEN
    lv_sql     :=SUBSTR(lv_sql,1,LENGTH(lv_sql))||', ''Not_available'' as Brand_col  FROM '||lv_tab_name;
    dbms_output.put_line(LV_SQL);
  ELSE
    lv_sql:=SUBSTR(lv_sql,1,LENGTH(lv_sql))||' FROM '||lv_tab_name;
    dbms_output.put_line(LV_SQL);
  END IF;
  OPEN :p_lst FOR lv_sql;
END;
PRINT p_lst;

Jeff Beagley · Accepted Answer · 2015-11-24 16:27:09Z

1

SELECT t.name, t.type, t.price, NVL(t.brand,"Not available") FROM some_table t;

answered Nov 24, 2015 at 16:27

Jeff Beagley

1,02511 silver badges22 bronze badges

Collectives™ on Stack Overflow

SQL Select statement - default field value when field doesn't exist

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related