Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00939 generated when creating a view in 10g (Oracle 10g; Windows Server 2003)
ORA-00939 generated when creating a view in 10g [message #284726] Fri, 30 November 2007 12:46 Go to next message
jlkinchantilly
Messages: 1
Registered: November 2007
Junior Member
When creating the same view in Oracle 9i, I have no problem at all. The create statement is:

CREATE OR REPLACE VIEW SM_COMPONENT_AUDIT_V
AS
SELECT a.DEVICE_ID, a.EVENT_TYPE_ID, a.EVENT_DTM, a.EVENT_SEQUENCE_NBR, m.REGISTER_NBR, m.SORT_ORDER,
CASE
WHEN m.REGISTER_COLUMN_NAME = 'VAL_001' THEN a.VAL_001
WHEN m.REGISTER_COLUMN_NAME = 'VAL_002' THEN a.VAL_002
WHEN m.REGISTER_COLUMN_NAME = 'VAL_003' THEN a.VAL_003
WHEN m.REGISTER_COLUMN_NAME = 'VAL_004' THEN a.VAL_004
WHEN m.REGISTER_COLUMN_NAME = 'VAL_005' THEN a.VAL_005
WHEN m.REGISTER_COLUMN_NAME = 'VAL_006' THEN a.VAL_006
WHEN m.REGISTER_COLUMN_NAME = 'VAL_007' THEN a.VAL_007
WHEN m.REGISTER_COLUMN_NAME = 'VAL_008' THEN a.VAL_008
WHEN m.REGISTER_COLUMN_NAME = 'VAL_009' THEN a.VAL_009
WHEN m.REGISTER_COLUMN_NAME = 'VAL_010' THEN a.VAL_010
WHEN m.REGISTER_COLUMN_NAME = 'VAL_011' THEN a.VAL_011
WHEN m.REGISTER_COLUMN_NAME = 'VAL_012' THEN a.VAL_012
WHEN m.REGISTER_COLUMN_NAME = 'VAL_013' THEN a.VAL_013
WHEN m.REGISTER_COLUMN_NAME = 'VAL_014' THEN a.VAL_014
WHEN m.REGISTER_COLUMN_NAME = 'VAL_015' THEN a.VAL_015
WHEN m.REGISTER_COLUMN_NAME = 'VAL_016' THEN a.VAL_016
WHEN m.REGISTER_COLUMN_NAME = 'VAL_017' THEN a.VAL_017
WHEN m.REGISTER_COLUMN_NAME = 'VAL_018' THEN a.VAL_018
WHEN m.REGISTER_COLUMN_NAME = 'VAL_019' THEN a.VAL_019
WHEN m.REGISTER_COLUMN_NAME = 'VAL_020' THEN a.VAL_020
WHEN m.REGISTER_COLUMN_NAME = 'VAL_021' THEN a.VAL_021
WHEN m.REGISTER_COLUMN_NAME = 'VAL_022' THEN a.VAL_022
WHEN m.REGISTER_COLUMN_NAME = 'VAL_023' THEN a.VAL_023
WHEN m.REGISTER_COLUMN_NAME = 'VAL_024' THEN a.VAL_024
WHEN m.REGISTER_COLUMN_NAME = 'VAL_025' THEN a.VAL_025
WHEN m.REGISTER_COLUMN_NAME = 'VAL_026' THEN a.VAL_026
WHEN m.REGISTER_COLUMN_NAME = 'VAL_027' THEN a.VAL_027
WHEN m.REGISTER_COLUMN_NAME = 'VAL_028' THEN a.VAL_028
WHEN m.REGISTER_COLUMN_NAME = 'VAL_029' THEN a.VAL_029
WHEN m.REGISTER_COLUMN_NAME = 'VAL_030' THEN a.VAL_030
WHEN m.REGISTER_COLUMN_NAME = 'VAL_031' THEN a.VAL_031
WHEN m.REGISTER_COLUMN_NAME = 'VAL_032' THEN a.VAL_032
WHEN m.REGISTER_COLUMN_NAME = 'VAL_033' THEN a.VAL_033
WHEN m.REGISTER_COLUMN_NAME = 'VAL_034' THEN a.VAL_034
WHEN m.REGISTER_COLUMN_NAME = 'VAL_035' THEN a.VAL_035
WHEN m.REGISTER_COLUMN_NAME = 'VAL_036' THEN a.VAL_036
WHEN m.REGISTER_COLUMN_NAME = 'VAL_037' THEN a.VAL_037
WHEN m.REGISTER_COLUMN_NAME = 'VAL_038' THEN a.VAL_038
WHEN m.REGISTER_COLUMN_NAME = 'VAL_039' THEN a.VAL_039
WHEN m.REGISTER_COLUMN_NAME = 'VAL_040' THEN a.VAL_040
WHEN m.REGISTER_COLUMN_NAME = 'VAL_041' THEN a.VAL_041
WHEN m.REGISTER_COLUMN_NAME = 'VAL_042' THEN a.VAL_042
WHEN m.REGISTER_COLUMN_NAME = 'VAL_043' THEN a.VAL_043
WHEN m.REGISTER_COLUMN_NAME = 'VAL_044' THEN a.VAL_044
WHEN m.REGISTER_COLUMN_NAME = 'VAL_045' THEN a.VAL_045
WHEN m.REGISTER_COLUMN_NAME = 'VAL_046' THEN a.VAL_046
WHEN m.REGISTER_COLUMN_NAME = 'VAL_047' THEN a.VAL_047
WHEN m.REGISTER_COLUMN_NAME = 'VAL_048' THEN a.VAL_048
WHEN m.REGISTER_COLUMN_NAME = 'VAL_049' THEN a.VAL_049
WHEN m.REGISTER_COLUMN_NAME = 'VAL_050' THEN a.VAL_050
WHEN m.REGISTER_COLUMN_NAME = 'VAL_051' THEN a.VAL_051
WHEN m.REGISTER_COLUMN_NAME = 'VAL_052' THEN a.VAL_052
WHEN m.REGISTER_COLUMN_NAME = 'VAL_053' THEN a.VAL_053
WHEN m.REGISTER_COLUMN_NAME = 'VAL_054' THEN a.VAL_054
WHEN m.REGISTER_COLUMN_NAME = 'VAL_055' THEN a.VAL_055
WHEN m.REGISTER_COLUMN_NAME = 'VAL_056' THEN a.VAL_056
WHEN m.REGISTER_COLUMN_NAME = 'VAL_057' THEN a.VAL_057
WHEN m.REGISTER_COLUMN_NAME = 'VAL_058' THEN a.VAL_058
WHEN m.REGISTER_COLUMN_NAME = 'VAL_059' THEN a.VAL_059
WHEN m.REGISTER_COLUMN_NAME = 'VAL_060' THEN a.VAL_060
WHEN m.REGISTER_COLUMN_NAME = 'VAL_061' THEN a.VAL_061
WHEN m.REGISTER_COLUMN_NAME = 'VAL_062' THEN a.VAL_062
WHEN m.REGISTER_COLUMN_NAME = 'VAL_063' THEN a.VAL_063
WHEN m.REGISTER_COLUMN_NAME = 'VAL_064' THEN a.VAL_064
WHEN m.REGISTER_COLUMN_NAME = 'VAL_065' THEN a.VAL_065
WHEN m.REGISTER_COLUMN_NAME = 'VAL_066' THEN a.VAL_066
WHEN m.REGISTER_COLUMN_NAME = 'VAL_067' THEN a.VAL_067
WHEN m.REGISTER_COLUMN_NAME = 'VAL_068' THEN a.VAL_068
WHEN m.REGISTER_COLUMN_NAME = 'VAL_069' THEN a.VAL_069
WHEN m.REGISTER_COLUMN_NAME = 'VAL_070' THEN a.VAL_070
WHEN m.REGISTER_COLUMN_NAME = 'VAL_071' THEN a.VAL_071
WHEN m.REGISTER_COLUMN_NAME = 'VAL_072' THEN a.VAL_072
WHEN m.REGISTER_COLUMN_NAME = 'VAL_073' THEN a.VAL_073
WHEN m.REGISTER_COLUMN_NAME = 'VAL_074' THEN a.VAL_074
WHEN m.REGISTER_COLUMN_NAME = 'VAL_075' THEN a.VAL_075
WHEN m.REGISTER_COLUMN_NAME = 'VAL_076' THEN a.VAL_076
WHEN m.REGISTER_COLUMN_NAME = 'VAL_077' THEN a.VAL_077
WHEN m.REGISTER_COLUMN_NAME = 'VAL_078' THEN a.VAL_078
WHEN m.REGISTER_COLUMN_NAME = 'VAL_079' THEN a.VAL_079
WHEN m.REGISTER_COLUMN_NAME = 'VAL_080' THEN a.VAL_080
WHEN m.REGISTER_COLUMN_NAME = 'VAL_081' THEN a.VAL_081
WHEN m.REGISTER_COLUMN_NAME = 'VAL_082' THEN a.VAL_082
WHEN m.REGISTER_COLUMN_NAME = 'VAL_083' THEN a.VAL_083
WHEN m.REGISTER_COLUMN_NAME = 'VAL_084' THEN a.VAL_084
WHEN m.REGISTER_COLUMN_NAME = 'VAL_085' THEN a.VAL_085
WHEN m.REGISTER_COLUMN_NAME = 'VAL_086' THEN a.VAL_086
WHEN m.REGISTER_COLUMN_NAME = 'VAL_087' THEN a.VAL_087
WHEN m.REGISTER_COLUMN_NAME = 'VAL_088' THEN a.VAL_088
WHEN m.REGISTER_COLUMN_NAME = 'VAL_089' THEN a.VAL_089
WHEN m.REGISTER_COLUMN_NAME = 'VAL_090' THEN a.VAL_090
WHEN m.REGISTER_COLUMN_NAME = 'VAL_091' THEN a.VAL_091
WHEN m.REGISTER_COLUMN_NAME = 'VAL_092' THEN a.VAL_092
WHEN m.REGISTER_COLUMN_NAME = 'VAL_093' THEN a.VAL_093
WHEN m.REGISTER_COLUMN_NAME = 'VAL_094' THEN a.VAL_094
WHEN m.REGISTER_COLUMN_NAME = 'VAL_095' THEN a.VAL_095
WHEN m.REGISTER_COLUMN_NAME = 'VAL_096' THEN a.VAL_096
WHEN m.REGISTER_COLUMN_NAME = 'VAL_097' THEN a.VAL_097
WHEN m.REGISTER_COLUMN_NAME = 'VAL_098' THEN a.VAL_098
WHEN m.REGISTER_COLUMN_NAME = 'VAL_099' THEN a.VAL_099
WHEN m.REGISTER_COLUMN_NAME = 'VAL_100' THEN a.VAL_100
WHEN m.REGISTER_COLUMN_NAME = 'VAL_101' THEN a.VAL_101
WHEN m.REGISTER_COLUMN_NAME = 'VAL_102' THEN a.VAL_102
WHEN m.REGISTER_COLUMN_NAME = 'VAL_103' THEN a.VAL_103
WHEN m.REGISTER_COLUMN_NAME = 'VAL_104' THEN a.VAL_104
WHEN m.REGISTER_COLUMN_NAME = 'VAL_105' THEN a.VAL_105
WHEN m.REGISTER_COLUMN_NAME = 'VAL_106' THEN a.VAL_106
WHEN m.REGISTER_COLUMN_NAME = 'VAL_107' THEN a.VAL_107
WHEN m.REGISTER_COLUMN_NAME = 'VAL_108' THEN a.VAL_108
WHEN m.REGISTER_COLUMN_NAME = 'VAL_109' THEN a.VAL_109
WHEN m.REGISTER_COLUMN_NAME = 'VAL_110' THEN a.VAL_110
WHEN m.REGISTER_COLUMN_NAME = 'VAL_111' THEN a.VAL_111
WHEN m.REGISTER_COLUMN_NAME = 'VAL_112' THEN a.VAL_112
WHEN m.REGISTER_COLUMN_NAME = 'VAL_113' THEN a.VAL_113
WHEN m.REGISTER_COLUMN_NAME = 'VAL_114' THEN a.VAL_114
WHEN m.REGISTER_COLUMN_NAME = 'VAL_115' THEN a.VAL_115
WHEN m.REGISTER_COLUMN_NAME = 'VAL_116' THEN a.VAL_116
WHEN m.REGISTER_COLUMN_NAME = 'VAL_117' THEN a.VAL_117
WHEN m.REGISTER_COLUMN_NAME = 'VAL_118' THEN a.VAL_118
WHEN m.REGISTER_COLUMN_NAME = 'VAL_119' THEN a.VAL_119
WHEN m.REGISTER_COLUMN_NAME = 'VAL_120' THEN a.VAL_120
WHEN m.REGISTER_COLUMN_NAME = 'VAL_121' THEN a.VAL_121
WHEN m.REGISTER_COLUMN_NAME = 'VAL_122' THEN a.VAL_122
WHEN m.REGISTER_COLUMN_NAME = 'VAL_123' THEN a.VAL_123
WHEN m.REGISTER_COLUMN_NAME = 'VAL_124' THEN a.VAL_124
WHEN m.REGISTER_COLUMN_NAME = 'VAL_125' THEN a.VAL_125
WHEN m.REGISTER_COLUMN_NAME = 'VAL_126' THEN a.VAL_126
WHEN m.REGISTER_COLUMN_NAME = 'VAL_127' THEN a.VAL_127
WHEN m.REGISTER_COLUMN_NAME = 'VAL_128' THEN a.VAL_128
WHEN m.REGISTER_COLUMN_NAME = 'VAL_129' THEN a.VAL_129
WHEN m.REGISTER_COLUMN_NAME = 'VAL_130' THEN a.VAL_130
WHEN m.REGISTER_COLUMN_NAME = 'VAL_131' THEN a.VAL_131
WHEN m.REGISTER_COLUMN_NAME = 'VAL_132' THEN a.VAL_132
WHEN m.REGISTER_COLUMN_NAME = 'VAL_133' THEN a.VAL_133
WHEN m.REGISTER_COLUMN_NAME = 'VAL_134' THEN a.VAL_134
WHEN m.REGISTER_COLUMN_NAME = 'VAL_135' THEN a.VAL_135
WHEN m.REGISTER_COLUMN_NAME = 'VAL_136' THEN a.VAL_136
WHEN m.REGISTER_COLUMN_NAME = 'VAL_137' THEN a.VAL_137
WHEN m.REGISTER_COLUMN_NAME = 'VAL_138' THEN a.VAL_138
WHEN m.REGISTER_COLUMN_NAME = 'VAL_139' THEN a.VAL_139
WHEN m.REGISTER_COLUMN_NAME = 'VAL_140' THEN a.VAL_140
WHEN m.REGISTER_COLUMN_NAME = 'VAL_141' THEN a.VAL_141
WHEN m.REGISTER_COLUMN_NAME = 'VAL_142' THEN a.VAL_142
WHEN m.REGISTER_COLUMN_NAME = 'VAL_143' THEN a.VAL_143
WHEN m.REGISTER_COLUMN_NAME = 'VAL_144' THEN a.VAL_144
WHEN m.REGISTER_COLUMN_NAME = 'VAL_145' THEN a.VAL_145
WHEN m.REGISTER_COLUMN_NAME = 'VAL_146' THEN a.VAL_146
WHEN m.REGISTER_COLUMN_NAME = 'VAL_147' THEN a.VAL_147
WHEN m.REGISTER_COLUMN_NAME = 'VAL_148' THEN a.VAL_148
WHEN m.REGISTER_COLUMN_NAME = 'VAL_149' THEN a.VAL_149
WHEN m.REGISTER_COLUMN_NAME = 'VAL_150' THEN a.VAL_150
WHEN m.REGISTER_COLUMN_NAME = 'VAL_151' THEN a.VAL_151
WHEN m.REGISTER_COLUMN_NAME = 'VAL_152' THEN a.VAL_152
WHEN m.REGISTER_COLUMN_NAME = 'VAL_153' THEN a.VAL_153
WHEN m.REGISTER_COLUMN_NAME = 'VAL_154' THEN a.VAL_154
WHEN m.REGISTER_COLUMN_NAME = 'VAL_155' THEN a.VAL_155
WHEN m.REGISTER_COLUMN_NAME = 'VAL_156' THEN a.VAL_156
WHEN m.REGISTER_COLUMN_NAME = 'VAL_157' THEN a.VAL_157
WHEN m.REGISTER_COLUMN_NAME = 'VAL_158' THEN a.VAL_158
WHEN m.REGISTER_COLUMN_NAME = 'VAL_159' THEN a.VAL_159
WHEN m.REGISTER_COLUMN_NAME = 'VAL_160' THEN a.VAL_160
WHEN m.REGISTER_COLUMN_NAME = 'VAL_161' THEN a.VAL_161
WHEN m.REGISTER_COLUMN_NAME = 'VAL_162' THEN a.VAL_162
WHEN m.REGISTER_COLUMN_NAME = 'VAL_163' THEN a.VAL_163
WHEN m.REGISTER_COLUMN_NAME = 'VAL_164' THEN a.VAL_164
WHEN m.REGISTER_COLUMN_NAME = 'VAL_165' THEN a.VAL_165
WHEN m.REGISTER_COLUMN_NAME = 'VAL_166' THEN a.VAL_166
WHEN m.REGISTER_COLUMN_NAME = 'VAL_167' THEN a.VAL_167
WHEN m.REGISTER_COLUMN_NAME = 'VAL_168' THEN a.VAL_168
WHEN m.REGISTER_COLUMN_NAME = 'VAL_169' THEN a.VAL_169
WHEN m.REGISTER_COLUMN_NAME = 'VAL_170' THEN a.VAL_170
WHEN m.REGISTER_COLUMN_NAME = 'VAL_171' THEN a.VAL_171
WHEN m.REGISTER_COLUMN_NAME = 'VAL_172' THEN a.VAL_172
WHEN m.REGISTER_COLUMN_NAME = 'VAL_173' THEN a.VAL_173
WHEN m.REGISTER_COLUMN_NAME = 'VAL_174' THEN a.VAL_174
WHEN m.REGISTER_COLUMN_NAME = 'VAL_175' THEN a.VAL_175
WHEN m.REGISTER_COLUMN_NAME = 'VAL_176' THEN a.VAL_176
WHEN m.REGISTER_COLUMN_NAME = 'VAL_177' THEN a.VAL_177
WHEN m.REGISTER_COLUMN_NAME = 'VAL_178' THEN a.VAL_178
WHEN m.REGISTER_COLUMN_NAME = 'VAL_179' THEN a.VAL_179
WHEN m.REGISTER_COLUMN_NAME = 'VAL_180' THEN a.VAL_180
WHEN m.REGISTER_COLUMN_NAME = 'VAL_181' THEN a.VAL_181
WHEN m.REGISTER_COLUMN_NAME = 'VAL_182' THEN a.VAL_182
WHEN m.REGISTER_COLUMN_NAME = 'VAL_183' THEN a.VAL_183
WHEN m.REGISTER_COLUMN_NAME = 'VAL_184' THEN a.VAL_184
WHEN m.REGISTER_COLUMN_NAME = 'VAL_185' THEN a.VAL_185
WHEN m.REGISTER_COLUMN_NAME = 'VAL_186' THEN a.VAL_186
WHEN m.REGISTER_COLUMN_NAME = 'VAL_187' THEN a.VAL_187
WHEN m.REGISTER_COLUMN_NAME = 'VAL_188' THEN a.VAL_188
WHEN m.REGISTER_COLUMN_NAME = 'VAL_189' THEN a.VAL_189
WHEN m.REGISTER_COLUMN_NAME = 'VAL_190' THEN a.VAL_190
WHEN m.REGISTER_COLUMN_NAME = 'VAL_191' THEN a.VAL_191
WHEN m.REGISTER_COLUMN_NAME = 'VAL_192' THEN a.VAL_192
WHEN m.REGISTER_COLUMN_NAME = 'VAL_193' THEN a.VAL_193
WHEN m.REGISTER_COLUMN_NAME = 'VAL_194' THEN a.VAL_194
WHEN m.REGISTER_COLUMN_NAME = 'VAL_195' THEN a.VAL_195
WHEN m.REGISTER_COLUMN_NAME = 'VAL_196' THEN a.VAL_196
WHEN m.REGISTER_COLUMN_NAME = 'VAL_197' THEN a.VAL_197
WHEN m.REGISTER_COLUMN_NAME = 'VAL_198' THEN a.VAL_198
WHEN m.REGISTER_COLUMN_NAME = 'VAL_199' THEN a.VAL_199
WHEN m.REGISTER_COLUMN_NAME = 'VAL_200' THEN a.VAL_200
WHEN m.REGISTER_COLUMN_NAME = 'VAL_201' THEN a.VAL_201
WHEN m.REGISTER_COLUMN_NAME = 'VAL_202' THEN a.VAL_202
WHEN m.REGISTER_COLUMN_NAME = 'VAL_203' THEN a.VAL_203
WHEN m.REGISTER_COLUMN_NAME = 'VAL_204' THEN a.VAL_204
WHEN m.REGISTER_COLUMN_NAME = 'VAL_205' THEN a.VAL_205
WHEN m.REGISTER_COLUMN_NAME = 'VAL_206' THEN a.VAL_206
WHEN m.REGISTER_COLUMN_NAME = 'VAL_207' THEN a.VAL_207
WHEN m.REGISTER_COLUMN_NAME = 'VAL_208' THEN a.VAL_208
WHEN m.REGISTER_COLUMN_NAME = 'VAL_209' THEN a.VAL_209
WHEN m.REGISTER_COLUMN_NAME = 'VAL_210' THEN a.VAL_210
WHEN m.REGISTER_COLUMN_NAME = 'VAL_211' THEN a.VAL_211
WHEN m.REGISTER_COLUMN_NAME = 'VAL_212' THEN a.VAL_212
WHEN m.REGISTER_COLUMN_NAME = 'VAL_213' THEN a.VAL_213
WHEN m.REGISTER_COLUMN_NAME = 'VAL_214' THEN a.VAL_214
WHEN m.REGISTER_COLUMN_NAME = 'VAL_215' THEN a.VAL_215
WHEN m.REGISTER_COLUMN_NAME = 'VAL_216' THEN a.VAL_216
WHEN m.REGISTER_COLUMN_NAME = 'VAL_217' THEN a.VAL_217
WHEN m.REGISTER_COLUMN_NAME = 'VAL_218' THEN a.VAL_218
WHEN m.REGISTER_COLUMN_NAME = 'VAL_219' THEN a.VAL_219
WHEN m.REGISTER_COLUMN_NAME = 'VAL_220' THEN a.VAL_220
WHEN m.REGISTER_COLUMN_NAME = 'VAL_221' THEN a.VAL_221
WHEN m.REGISTER_COLUMN_NAME = 'VAL_222' THEN a.VAL_222
WHEN m.REGISTER_COLUMN_NAME = 'VAL_223' THEN a.VAL_223
WHEN m.REGISTER_COLUMN_NAME = 'VAL_224' THEN a.VAL_224
WHEN m.REGISTER_COLUMN_NAME = 'VAL_225' THEN a.VAL_225
WHEN m.REGISTER_COLUMN_NAME = 'VAL_226' THEN a.VAL_226
WHEN m.REGISTER_COLUMN_NAME = 'VAL_227' THEN a.VAL_227
WHEN m.REGISTER_COLUMN_NAME = 'VAL_228' THEN a.VAL_228
WHEN m.REGISTER_COLUMN_NAME = 'VAL_229' THEN a.VAL_229
WHEN m.REGISTER_COLUMN_NAME = 'VAL_230' THEN a.VAL_230
WHEN m.REGISTER_COLUMN_NAME = 'VAL_231' THEN a.VAL_231
WHEN m.REGISTER_COLUMN_NAME = 'VAL_232' THEN a.VAL_232
WHEN m.REGISTER_COLUMN_NAME = 'VAL_233' THEN a.VAL_233
WHEN m.REGISTER_COLUMN_NAME = 'VAL_234' THEN a.VAL_234
WHEN m.REGISTER_COLUMN_NAME = 'VAL_235' THEN a.VAL_235
WHEN m.REGISTER_COLUMN_NAME = 'VAL_236' THEN a.VAL_236
WHEN m.REGISTER_COLUMN_NAME = 'VAL_237' THEN a.VAL_237
WHEN m.REGISTER_COLUMN_NAME = 'VAL_238' THEN a.VAL_238
WHEN m.REGISTER_COLUMN_NAME = 'VAL_239' THEN a.VAL_239
WHEN m.REGISTER_COLUMN_NAME = 'VAL_240' THEN a.VAL_240
WHEN m.REGISTER_COLUMN_NAME = 'VAL_241' THEN a.VAL_241
WHEN m.REGISTER_COLUMN_NAME = 'VAL_242' THEN a.VAL_242
WHEN m.REGISTER_COLUMN_NAME = 'VAL_243' THEN a.VAL_243
WHEN m.REGISTER_COLUMN_NAME = 'VAL_244' THEN a.VAL_244
WHEN m.REGISTER_COLUMN_NAME = 'VAL_245' THEN a.VAL_245
WHEN m.REGISTER_COLUMN_NAME = 'VAL_246' THEN a.VAL_246
WHEN m.REGISTER_COLUMN_NAME = 'VAL_247' THEN a.VAL_247
WHEN m.REGISTER_COLUMN_NAME = 'VAL_248' THEN a.VAL_248
WHEN m.REGISTER_COLUMN_NAME = 'VAL_249' THEN a.VAL_249
WHEN m.REGISTER_COLUMN_NAME = 'VAL_250' THEN a.VAL_250
WHEN m.REGISTER_COLUMN_NAME = 'VAL_251' THEN a.VAL_251
WHEN m.REGISTER_COLUMN_NAME = 'VAL_252' THEN a.VAL_252
WHEN m.REGISTER_COLUMN_NAME = 'VAL_253' THEN a.VAL_253
WHEN m.REGISTER_COLUMN_NAME = 'VAL_254' THEN a.VAL_254
WHEN m.REGISTER_COLUMN_NAME = 'VAL_255' THEN a.VAL_255
WHEN m.REGISTER_COLUMN_NAME = 'VAL_256' THEN a.VAL_256
WHEN m.REGISTER_COLUMN_NAME = 'VAL_256' THEN a.VAL_256
END REG_VALUE
FROM DEVICE d, COMPONENT_AUDIT a, COUNTER_ID_TO_REGISTER_MAP m
WHERE d.DEVICE_ID = a.DEVICE_ID AND m.DEVICE_TYPE_ID = d.DEVICE_TYPE_ID AND m.ACTIVE_FLAG = 1
/
Re: ORA-00939 generated when creating a view in 10g [message #284731 is a reply to message #284726] Fri, 30 November 2007 13:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
CASE accepts 255 when statements.

That it excepted more in 9.X was classified as a bug and removed in 10.X.

You will have to nest the CASE statements in some way.
Re: ORA-00939 generated when creating a view in 10g [message #284796 is a reply to message #284731] Sat, 01 December 2007 03:26 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, would UNION (ALL) help? For example, first SELECT could include CASE from 001 - 200 and another SELECT CASEs from 201 - 256; something like
CREATE OR REPLACE VIEW SM_COMPONENT_AUDIT_V
AS
SELECT a.DEVICE_ID, a.EVENT_TYPE_ID, a.EVENT_DTM, a.EVENT_SEQUENCE_NBR,
       m.REGISTER_NBR, m.SORT_ORDER,
CASE 
  WHEN m.REGISTER_COLUMN_NAME = 'VAL_001' THEN a.VAL_001
  WHEN m.REGISTER_COLUMN_NAME = 'VAL_002' THEN a.VAL_002
  ...
  WHEN m.REGISTER_COLUMN_NAME = 'VAL_200' THEN a.VAL_200
FROM ...
WHERE ...

UNION ALL

SELECT a.DEVICE_ID, a.EVENT_TYPE_ID, a.EVENT_DTM, a.EVENT_SEQUENCE_NBR, 
       m.REGISTER_NBR, m.SORT_ORDER,
CASE 
  WHEN m.REGISTER_COLUMN_NAME = 'VAL_201' THEN a.VAL_201
  WHEN m.REGISTER_COLUMN_NAME = 'VAL_202' THEN a.VAL_202
  ...
  WHEN m.REGISTER_COLUMN_NAME = 'VAL_256' THEN a.VAL_256
FROM ...
WHERE ...
Re: ORA-00939 generated when creating a view in 10g [message #284798 is a reply to message #284796] Sat, 01 December 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thomas' answer is excellent.
CASE field
  WHEN 1 THEN 
  WHEN 2 THEN
  WHEN 3 THEN
  WHEN 4 THEN
  ELSE
END 

is equivalent to (for instance):
CASE field
  WHEN 1 THEN
  WHEN 2 THEN
  ELSE 
    CASE field
      WHEN 3 THEN
      WHEN 4 THEN
      ELSE
    END
END

Then none of the case exceeds the limit.

Regards
Michel
Re: ORA-00939 generated when creating a view in 10g [message #284800 is a reply to message #284726] Sat, 01 December 2007 03:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Of course, you do know that the cause of this is a flaw in you design, right? This should never have been solved by creating a zillion columns..
Previous Topic: plsql table...pls help..really urgent
Next Topic: Bulk insert
Goto Forum:
  


Current Time: Tue Dec 06 04:49:18 CST 2016

Total time taken to generate the page: 0.13304 seconds