The usage of the case statement in the select statement, and examples of the use of insert and select and case

 

 

 

SQL> select tag_name, tp_name, value from ins_dynamic; TAG_NAME TP_NAME VALUE ---------------- --------------- -------------- a NoString 1b NoString 2

 

 

 

 

 

SQL> select (case when value = '1' then' value = 1' when value = '2' then' value = 2' else 'other value' end) tag_name1 from ins_dynamic;

 

 

TAG_NAME1

--------------------

Value = 1

Value = 2

 

 

 

 

SQL> insert into ins_dynamic (tag_name, tp_name) Select 'zzzz. '| tag_name, (case when value = '1' then' value = 1' when value = '2' then' value = 2' else 'other value' end) from ins_dynamic;

 

Two rows have been created.

SQL> select tag_name, tp_name, value from ins_dynamic; TAG_NAME TP_NAME VALUE ---------------- --------------- -------------- a NoString 1b NoString 2zzz. a value = 1 NoStringzzz. B value = 2 NoString finally write a vigorous DELETE FROM dict_column_info; INSERT INTO dict_column_info (table_name, column_name, data_type, data_length, is_nullable, is_key) SELECT t1.table _ name, t1.column _ name, (case when t1.data _ type = 'varchar2' then 1 when t1.data _ type = 'date' then 2 when t1.data _ type = 'number' AND t1.data _ precision = 22 AND t1.data _ scale = 6 then 3 when t1.data _ type = 'number' AND t1.data _ precision = 10 AND t1.data _ scale = 0 then 4 else 0 end ), (case when t1.data _ type = 'varchar2' then t1.data _ length when t1.data _ type = 'date' then 4 when t1.data _ type = 'number' AND t1.data _ precision = 22 AND t1.data _ scale = 6 then 4 when t1.data _ type = 'number' AND t1.data _ precision = 10 AND t1.data _ scale = 0 then 4 else 0 end ), (case when t1.nullable = 'y' then 1 when t1.nullable = 'n' then 0 else-1 end), (case when (t1.table _ name, t1.column _ name) IN (SELECT t2.table _ name, t2.column _ name FROM all_cons_columns t2 where t2.owner = 'iscs 'AND t2.constraint _ name IN (SELECT t3.constraint _ name FROM nation t3 where t3.owner = 'iscs' AND t3.constraint _ type = 'P ')) then 1 else 0 end) from user_tab_columns t1;