Details
-
Bug
-
Status: Closed
-
Critical
-
Resolution: Fixed
-
None
-
None
-
None
Description
1. table g_sqldopt.b2uwl04 has 106 columns
2. create table as ... return error 4023
SQL>create table myb2ul04 store by (SDEC9_UNIQ)
as (select * from g_sqldopt.b2uwl04);
-
-
- ERROR[4023] The degree of each row value constructor (106) must equal the degree of the target table column list (80). [2014-05-27 15:06:37]
-
SQL>select * from myb2ul04;
-
-
- ERROR[4082] Object TRAFODION.USR.MYB2UL04 does not exist or is inaccessible. [2014-05-27 15:06:37]
-
create schema g_sqldopt;
set schema g_sqldopt;
Create Table b2uwl04 (
sbin0_4 Integer default 3 not null,
time0_uniq Time not null,
varchar0_uniq VarChar(8) no default not null,
sdec0_100 Decimal(9) no default not null,
int0_dTOf6_4 Interval day to second(6) not null,
ts1_n100 Timestamp
heading 'ts1_n100 allowing nulls',
sdec1_20 Decimal(5) no default not null,
int1_yTOm_n100 Interval year(1) to month no default,
double1_2 Double Precision not null,
udec1_nuniq Decimal(4) unsigned ,
char2_2 Character(2) not null,
sbin2_nuniq Largeint ,
sdec2_500 Decimal(9) signed no default not null,
date2_uniq Date not null,
int2_dTOf6_n2 Interval day to second(6) no default,
real2_500 Real not null,
real3_n1000 Real ,
int3_yTOm_4 Interval year(1) to month no default not null,
date3_n2000 Date no default,
udec3_n100 Decimal(9) unsigned ,
ubin3_n2000 Numeric(4) unsigned ,
char3_4 Character(8) no default not null,
sdec4_n20 Decimal(4) no default,
int4_yTOm_uniq Interval year(5) to month not null,
sbin4_n1000 Smallint ,
time4_1000 Time no default not null,
char4_n10 Character(8) no default,
real4_2000 Real not null,
char5_n20 Character(8) ,
sdec5_10 Decimal(9) signed no default not null,
ubin5_n500 Numeric(9) unsigned no default,
real5_uniq Real not null,
dt5_yTOmin_n500 Timestamp(0) ,
int5_hTOs_500 Interval hour to second(0) no default not null,
int6_dTOf6_nuniq Interval day to second(6) no default,
sbin6_nuniq Largeint no default,
double6_n2 Float(23) ,
sdec6_4 Decimal(4) signed no default not null,
char6_n100 Character(8) no default,
date6_100 Date not null,
time7_uniq Time not null,
sbin7_n20 Smallint no default,
char7_500 Character(8) no default not null,
int7_hTOs_nuniq Interval hour(2) to second(0) ,
udec7_n10 Decimal(4) unsigned ,
real7_n4 Real ,
ubin8_10 Numeric(4) unsigned not null,
int8_y_n1000 Interval year(3) ,
date8_10 Date no default not null,
char8_n1000 Character(8) no default,
double8_n10 Double Precision no default,
sdec8_4 Decimal(9) unsigned not null,
sdec9_uniq Decimal(18) signed no default not null,
real9_n20 Real ,
time9_n4 Time ,
char9_100 Character(2) no default not null,
int9_dTOf6_2000 Interval day to second(6) no default not null,
ubin9_n4 Numeric(9) unsigned no default,
ubin10_n2 Numeric(4) unsigned no default,
char10_nuniq Character(8) ,
int10_d_uniq Interval day(6) not null,
ts10_n2 Timestamp ,
real10_100 Real not null,
udec10_uniq Decimal(9) unsigned no default not null,
udec11_2000 Decimal(9) unsigned no default not null,
int11_h_n10 Interval hour(1) no default,
sbin11_100 Integer not null,
time11_20 Time not null,
char11_uniq Character(8) not null,
double11_n100 Double Precision ,
real12_n20 Real ,
ubin12_2 Numeric(4) unsigned no default not null,
dt12_mTOh_1000 Timestamp(0) no default not null,
sdec12_n1000 Decimal(18) signed no default,
char12_n2000 Character(8) no default,
int12_yTOm_100 Interval year to month not null,
int13_yTOm_n1000 Interval year to month ,
udec13_500 Decimal(9) unsigned no default not null,
sbin13_n100 PIC S9(8)V9 COMP no default,
ts13_uniq Timestamp not null,
char13_1000 Character(8) not null,
real13_n1000 Real ,
sbin14_1000 Integer no default not null,
double14_nuniq Float(23) no default,
udec14_100 Decimal(4) unsigned not null,
char14_n500 Character(8) ,
int14_d_500 Interval day(3) no default not null,
ts14_n100 Timestamp no default,
dt15_mTOh_n100 Timestamp(0) no default,
double15_uniq Double Precision not null,
sbinneg15_nuniq Largeint ,
sdecneg15_100 Decimal(9) signed no default not null,
int15_dTOf6_n100 Interval day to second(6) no default,
char15_100 Character(8) not null,
dt16_m_n10 Date ,
int16_h_20 Interval hour no default not null,
ubin16_n10 Numeric(4) unsigned no default,
sdec16_uniq Decimal(18) signed not null,
char16_n20 Character(5) , – len = 2,4
real16_10 Real no default not null,
int17_y_n10 Interval year(1) no default,
dt17_yTOmin_uniq Timestamp(0) not null,
real17_n100 Real ,
sbin17_uniq Largeint no default not null,
– range: 0-149999
sdec17_nuniq Decimal(18) no default,
char17_2 Character(8) not null,
primary key ( sdec9_uniq ASC,
sdec0_100 DESC,
sdec1_20 ASC
)
)
store by primary key;
insert into b2uwl04 values (
3,
time '00:14:10',
'EKAALAAC',
88,
interval '0 00:00:03.375000' day to second(6),
timestamp '2100-01-01 00:00:56.250000',
12,
interval '7-04' year(1) to month,
1.0001,
850,
'AA',
4652,
152,
date '2112-09-27',
interval '0 00:00:00.000000' day to second(6),
10.052,
101.88,
interval '0-00' year(1) to month,
date '2101-08-12',
88,
588,
'AAAAAAAA',
3,
interval '151-11' year(5) to month,
823,
time '00:13:43',
'BDAAAAAA',
10223.0,
'CAAAAAAA',
0,
350,
1.085,
timestamp '2100-01-01 05:50:00',
interval '0:05:50' hour to second(0),
interval '0 01:27:13.500000' day to second(6),
4652,
1.0,
0,
'ACAAAAAA',
date '2100-02-22',
time '00:43:08',
8,
'FEAAFAAA',
interval '0:43:08' hour(2) to second(0),
8,
1.0,
3,
interval '823' year(3),
date '2100-01-04',
'DIAADAAA',
1.0003,
3,
2372,
10.0,
time '00:00:02',
'CA',
interval '0 00:15:56.250000' day to second(6),
2,
0,
'EKAALAAC',
interval '4652' day(6),
timestamp '2100-01-01 00:00:00.000000',
100.02,
4652,
588,
interval '8' hour(1),
88,
time '00:00:08',
'FDAABAAD',
1001.3000000000003,
1.0003,
1,
timestamp '2000-02-04 07:00:00',
823,
'DIAADAAB',
interval '1-11' year to month,
interval '70-10' year to month,
350,
5.0,
timestamp '2100-01-01 00:15:56.250000',
'DDAAFAAA',
10050.0,
652,
10652.000000000004,
52,
'EEAAIAAA',
interval '152' day(3),
timestamp '2100-01-01 00:00:58.500000',
timestamp '2000-01-04 16:00:00',
105.88000000000002,
-2588,
-88,
interval '0 00:01:39.000000' day to second(6),
'ANAAAAAA',
date '2000-04-01',
interval '3' hour,
3,
1823,
'BD',
1.0003,
interval '0' year(1),
timestamp '2100-01-01 14:10:00',
100.0,
77041,
850,
'AAAAAAAA'
);
insert into b2uwl04 values (
1,
time '01:05:08',
'AEAAFAAE',
88,
interval '0 00:00:01.125000' day to second(6),
timestamp '2100-01-01 00:00:09.000000',
14,
interval '7-04' year(1) to month,
1.0001,
3908,
'AA',
4074,
74,
date '2111-02-27',
interval '0 00:00:00.000000' day to second(6),
1.0074,
101.88,
interval '0-00' year(1) to month,
date '2101-08-12',
88,
588,
'AAAAAAAA',
17,
interval '408-01' year(5) to month,
897,
time '00:14:57',
'BCAAAAAA',
100.97,
'ADAAAAAA',
8,
408,
1090.8,
timestamp '2100-01-01 06:48:00',
interval '0:06:48' hour to second(0),
interval '0 01:16:23.250000' day to second(6),
4074,
1.0,
2,
'CYAAAAAA',
date '2100-03-16',
time '00:09:48',
8,
'AEAADAAA',
interval '0:09:48' hour(2) to second(0),
8,
1.0,
7,
interval '897' year(3),
date '2100-01-08',
'ECAAJAAA',
10.002,
1,
1553,
1.0008,
time '00:00:00',
'AI',
interval '0 00:35:46.500000' day to second(6),
0,
0,
'AEAAFAAE',
interval '4074' day(6),
timestamp '2100-01-01 00:00:00.000000',
100.24,
4074,
588,
interval '8' hour(1),
88,
time '00:00:08',
'AFAADAAD',
1001.3000000000003,
10.007,
1,
timestamp '2000-02-07 09:00:00',
897,
'ECAAJAAB',
interval '8-01' year to month,
interval '75-08' year to month,
408,
0.8,
timestamp '2100-01-01 01:13:16.500000',
'CDAAIAAA',
10108.0,
74,
10074.000000000002,
74,
'ACAAGAAA',
interval '74' day(3),
timestamp '2100-01-01 00:01:23.250000',
timestamp '2000-01-04 16:00:00',
1.0588,
-588,
-88,
interval '0 00:01:39.000000' day to second(6),
'ANAAAAAA',
date '2000-08-01',
interval '17' hour,
7,
4897,
'BCAA',
10.002,
interval '8' year(1),
timestamp '2100-01-03 17:08:00',
1.0008,
43570,
3908,
'AAAAAAAA'
);
insert into b2uwl04 values (
1,
time '00:56:32',
'EAAAJAAB',
88,
interval '0 00:00:01.125000' day to second(6),
timestamp '2100-01-01 00:01:43.500000',
6,
interval '7-04' year(1) to month,
1.0001,
3392,
'AA',
1166,
166,
date '2103-03-13',
interval '0 00:00:00.000000' day to second(6),
10.066,
101.88,
interval '0-00' year(1) to month,
date '2101-08-12',
88,
588,
'AAAAAAAA',
13,
interval '126-01' year(5) to month,
513,
time '00:08:33',
'BDAAAAAA',
1031.3,
'ACAAAAAA',
2,
392,
1039.2,
timestamp '2100-01-01 06:32:00',
interval '0:06:32' hour to second(0),
interval '0 00:21:51.750000' day to second(6),
1166,
1.0,
2,
'CQAAAAAA',
date '2100-03-08',
time '01:16:28',
8,
'DEAAHAAA',
interval '1:16:28' hour(2) to second(0),
8,
1.0,
3,
interval '513' year(3),
date '2100-01-04',
'BGAAFAAA',
1.0003,
1,
194,
10.002,
time '00:00:00',
'AR',
interval '0 00:26:06.000000' day to second(6),
0,
0,
'EAAAJAAB',
interval '1166' day(6),
timestamp '2100-01-01 00:00:00.000000',
100.16,
1166,
588,
interval '8' hour(1),
88,
time '00:00:08',
'DBAAMAAD',
1001.3000000000003,
10.003,
1,
timestamp '2000-01-22 09:00:00',
513,
'BGAAFAAB',
interval '1-01' year to month,
interval '32-08' year to month,
392,
9.2,
timestamp '2100-01-01 01:03:36.000000',
'EEAAMAAA',
10.192,
166,
10.166,
66,
'EGAAFAAA',
interval '166' day(3),
timestamp '2100-01-01 00:01:14.250000',
timestamp '2000-01-04 16:00:00',
10588.000000000004,
-4588,
-88,
interval '0 00:01:39.000000' day to second(6),
'ANAAAAAA',
date '2000-04-01',
interval '13' hour,
3,
1513,
'BD',
1.0003,
interval '2' year(1),
timestamp '2100-01-03 08:32:00',
1001.7,
144407,
3392,
'AAAAAAAA'
);
– test scripts
log t1log clear;
set schema usr;
drop table myb2ul04 cascade;
showddl g_sqldopt.b2uwl04;
create table myb2ul04 store by (SDEC9_UNIQ)
as (select * from g_sqldopt.b2uwl04);
select * from myb2ul04;
log off;
exit;