View With 300 Fields. Only 256 Subselects Allowed

[MFisher]

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.

SELECT M.MCN#, M.FG1 ,

(SELECT IDESC FROM TABLE_A WHERE IPROD = M.FG1) as FG1_IDESC ,
(SELECT IITYP FROM TABLE_AWHERE IPROD = M.FG1) as FG1_IITYP ,
(SELECT ICLAS FROM TABLE_AWHERE IPROD = M.FG1) as FG1_ICLAS ,
(SELECT IREF01 FROM TABLE_AWHERE IPROD = M.FG1) as FG1_IREF01 ,
(SELECT IREF02 FROM TABLE_AWHERE IPROD = M.FG1) as FG1_IREF02 ,
(SELECT IREF03 FROM TABLE_AWHERE IPROD = M.FG1) as FG1_IREF03 ,
(SELECT IREF04 FROM TABLE_AWHERE IPROD = M.FG1) as FG1_IREF04 ,
(SELECT IREF05 FROM TABLE_AWHERE IPROD = M.FG1) as FG1_IREF05 ,
(SELECT IPFDV FROM TABLE_AWHERE 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

 [LEFT][COLOR=#333333][FONT=inherit]CREATE VIEW V_3 ( Fld1, Fld2, fld3 ..... fld298, fld299, fld300) as Select V_1.*, V-2.*[/FONT][/COLOR][/LEFT]
    [LEFT][COLOR=#333333][FONT=inherit]FROM V_1 JOIN V_2 on V_1.key = V_2.key[/FONT][/COLOR][/LEFT]

Is there a better way ?

[Scott M]

Instead of subselects it looks like you could do a join to Table_A instead. It isn't saying that many fields are too many but all the subselects.

[B.Hauser]

With Joining the performance will be much better, than for executing 30 or more sub-selects on the same table!
Something like this:

Create or Replace View
as
Select M.MCN#, M.FG1,
       IDESC  As FG1_IDESC,
       IITYP  As FG1_IITYP,       
       ICLAS  As FG1_ICLAS,
       IREF01 As FG1_IREF01,
       IREF02 As FG1_IREF02,
       IREF03 As FG1_IREF03,
       IREF04 As FG1_IREF04,
       IREF05 As FG1_IREF05,
       IPFDV  As FG1_IPFDV ,
          --(29 more blocks of similar code)
   From TABLE_B M
        Join TABLE_A
           On IPROD = M.FG1;

Birgitta

[MFisher]

With Joining the performance will be much better, than for executing 30 or more sub-selects on the same table!
Something like this:

Create or Replace View
as
Select M.MCN#, M.FG1,
IDESC As FG1_IDESC,
IITYP As FG1_IITYP,
ICLAS As FG1_ICLAS,
IREF01 As FG1_IREF01,
IREF02 As FG1_IREF02,
IREF03 As FG1_IREF03,
IREF04 As FG1_IREF04,
IREF05 As FG1_IREF05,
IPFDV As FG1_IPFDV ,
--(29 more blocks of similar code)
From TABLE_B M
Join TABLE_A
On IPROD = M.FG1;

Birgitta

Yes, I re-wrote it as you suggested. Much improved. thanks !