[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

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
0
GurcanK
Asked:
GurcanK
  • 4
  • 3
3 Solutions
 
sdstuberCommented:
insert into TA (x,y,z,t) select a,b,c,(case when c=1 then 'XXX' else d end) from tb
0
 
slightwv (䄆 Netminder) Commented:
>>insert into TA (x,y,z,t) select a,b,c,(case when c=1 then 'XXX' else TB.d end)

you are missing the FROM TB after the select.

Just like the last two questions:  Looks good.

Just set up some test tables and try it.
0
 
sdstuberCommented:
insert into TA (x,y,z,t) select a,b,c, decode(c,1,'XXX',d) from tb
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
Missed something:
then 'XXX' else TB.d

Since 'XXX' is a string, TB.d will need to be a varcahr2 or similar string variable.  if it is a number or a date, you will likely get an error.  You will need to convert TB.d to a string datattype by using TO_CHAR and the appropriate formatting.
0
 
sdstuberCommented:
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
0
 
slightwv (䄆 Netminder) Commented:
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
0
 
GurcanKAuthor Commented:
Thanks all, fortunately data-types are the same.
0
 
sdstuberCommented:
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!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now