I am creating a view that has 300 fields, but in Navigator, I get the error.
“…Message: [SQL0101] SQL statement too long or complex. Cause . . . . . : The SQL statement is longer than the limit allowed for length or complexity. The reason code is 4. …
4 - The total number of subselects referenced in an outer subselect is greater than 256….”
Basically, my view would consist of this code, repeated 30 times with slightly different field names.
...
Code:
SELECT M.MCN#, M.FG1 , (SELECT IDESC FROM TABLE_A WHERE IPROD = M.FG1) as FG1_IDESC , (SELECT IITYP FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_IITYP , (SELECT ICLAS FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_ICLAS , (SELECT IREF01 FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_IREF01 , (SELECT IREF02 FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_IREF02 , (SELECT IREF03 FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_IREF03 , (SELECT IREF04 FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_IREF04 , (SELECT IREF05 FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_IREF05 , (SELECT IPFDV FROM [COLOR=#000000][FONT=inherit][FONT=aliceregular][FONT=inherit][SIZE=13px]TABLE_A[/SIZE][/FONT][/FONT][/FONT][/COLOR]WHERE IPROD = M.FG1) as FG1_IPFDV . . (29 more blocks of similar code) . . FROM TABLE_B ….
I can create 2 views. with 150 fields each. If I want to make a 3rd view, combining those 2, do I need to specify all 300 fields in the
CREATE VIEW V_3 ( Fld1, Fld2, fld3 ..... fld298, fld299, fld300) as Select V_1.*, V-2.*
FROM V_1 JOIN V_2 on V_1.key = V_2.key
Is there a better way ?