1

I am creating a table by selecting from another. My understanding is that by doing this, the columns created should be of the same data type as the original source. This is not the case for a couple columns and it is driving me nuts. Two columns in particular. One is a Varchar2(4) and the other a Varchar2(1), but in the created table they both become Varchar2(100). Is there any case where this should happen?

My select query is fairly complicated in that there are 20 Unions, but they all pull from the same single table. There are also 28 columns, so I would rather have this work than create the table and populate it in two steps.

Are unions known to mess up this sort of script?

Chris Chutter
  • 11
  • 1
  • 3
    The VARCHAR2(100) isn't arbitrary. I would bet that one of the fields in your various unions is a VARCHAR2(100), and Oracle is creating the new table based on the largest size. I couldn't say for sure, though, without at least seeing the relevant portions of your select statement. – Acroyear Dec 11 '19 at 22:50

2 Answers2

1

I understand the preference to create and fill the table in one step. Besides being easier now, it will also be easier if there are any modifications. However at some point you might consider you are spending more time trying to fix this, then it would take to write the create table statement. (which you can likely have the database do for you from the existing version).

As far as why it is doing this, I would suggest that one of your unions has either a concat or other function applied to the field. If you find the offending column then you can cast it to insure the table is seen as you intend.

If you can't find which of the 20 select statements is the offending one, then remove half, and see if it creates the columns the way you intend. Then keep dividing the unions in half until you have identified the select statement that causes this. CAUTION: it might be more than one.

markwusinich
  • 108
  • 7
0

I figured out the problem you're both pretty close to correct. One of the later unions was casting the variables as

CAST(NULL AS VARCHAR2(100))

Didn't figure a NULL value would increase the size of the datatype, but I guess it makes sense.

Chris Chutter
  • 11
  • 1