asked on

How to use case in insert into select statement?

Dear Experts,

Is below statement is true or can you offer an alternative?

Table TA (x,y,z,t)
Table TB (a,b,c,d)

insert into TA (x,y,z,t) select a,b,c,(case when c=1 then 'XXX' else TB.d end)

Please be careful that if c=1 then assign constant value 'XXX' else assign value of column d)

BR

Sean Stuber

insert into TA (x,y,z,t) select a,b,c,(case when c=1 then 'XXX' else d end) from tb

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

actually, since 'XXX' is a string, and it's the first return result in the CASE, it's determining the data type of the rest of the case results, so anything else will be implicitly converted to text (char)

so, you probably won't get an error, but you might get funny results if your data converts into a format you don't want.

So, TO_CHAR with an explicit format for non-text data is a good idea.

Same applies to the DECODE alternative

slightwv (䄆 Netminder)

Version: 11.2.0.2:

SQL> select case when 1=1 then 'XXX' else 1 end from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

SQL> select case when 1=1 then 'XXX' else to_char(1) end from dual;
XXX

GurcanK

ASKER

Thanks all, fortunately data-types are the same.

Sean Stuber

interesting - I stand corrected. Thanks!

I know I've seen the first element determine data types before. Maybe it was a bug in older versions.

Does work with decode though

SELECT DECODE(1, 1, 'XXX', 1) FROM DUAL;

and thanks again!