Genexus

Hola,

 

Trabajo con GXEV1, U3, Java/Web Db2 for iSeries y tengo el siguietne problema.

 

En una transaccion con formulas bastentes complejas (sumatoria de varios sum de niveles inferiores), el error no da al abrir la transaccion sino mas bien al utilizar los atributos en un listado, especificametne el error es que trata de buscar al atributo formula como un atributo fisico en la tabla que lo contiene.

 

Buscando mas a fondo, encontre que el generador me crea una sentencia muy compleja y presumo que esa complejidad provoca la caida.

 

SELECT T1.TDOCCOD, T1.T01NDOC, T1.T01NM2, T1.T01NM1, T1.T01AP1, T1.T01FCA, T1.T01APC, T1.T01AP2, T1.T01USR, T1.T01RSO, COALESCE( T2.M2BATAC, 0) AS M2BATAC, COALESCE( T8.m2batpc, 0) AS m2batpc, COALESCE( T9.M2BATPN, 0) AS M2BATPN, COALESCE( T11.M2BATPYN, 0) AS M2BATPYN, COALESCE( T12.M2BATA2, 0) AS M2BATA2, COALESCE( T12.M2BATA1, 0) AS M2BATA1, COALESCE( T13.M2BATIP1, 0) AS M2BATIP1, COALESCE( T13.M2BATIP2, 0) AS M2BATIP2, COALESCE( T14.M2BAOA2, 0) AS M2BAOA2, COALESCE( T15.M2BAOA1, 0) AS M2BAOA1, COALESCE( T16.M2BAIN1, 0) AS M2BAIN1, COALESCE( T17.M2BAIN2, 0) AS M2BAIN2, COALESCE( T18.M2BATE2, 0) AS M2BATE2, COALESCE( T19.M2BATE1, 0) AS M2BATE1, COALESCE( T10.M2BATPAS, 0) AS M2BATPAS, COALESCE( T13.M2BAACT, 0) AS M2BAACT, COALESCE( T7.M2BATCC, 0) AS M2BATCC, COALESCE( T6.M2BATRO, 0) AS M2BATRO, COALESCE( T5.M2BATMA, 0) AS M2BATMA, COALESCE( T4.M2BATTA, 0) AS M2BATTA, COALESCE( T3.M2BATIN, 0) AS M2BATIN
    FROM ((((((((((((((((((CLWT01 T1 LEFT JOIN (SELECT SUM(M2AC1MTG) AS M2BATAC, TDOCCOD, T01NDOC
                                                        FROM CLWM2AC1 GROUP BY TDOCCOD, T01NDOC ) T2 ON T2.TDOCCOD = T1.TDOCCOD AND T2.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC2MTG) AS M2BATIN
                                                        FROM CLWM2AC2 GROUP BY TDOCCOD, T01NDOC ) T3 ON T3.TDOCCOD = T1.TDOCCOD AND T3.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC3MTG) AS M2BATTA
                                                        FROM CLWM2AC3 GROUP BY TDOCCOD, T01NDOC ) T4 ON T4.TDOCCOD = T1.TDOCCOD AND T4.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC4MTG) AS M2BATMA
                                                        FROM CLWM2AC4 GROUP BY TDOCCOD, T01NDOC ) T5 ON T5.TDOCCOD = T1.TDOCCOD AND T5.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC6MTG) AS M2BATRO
                                                        FROM CLWM2AC6 GROUP BY TDOCCOD, T01NDOC ) T6 ON T6.TDOCCOD = T1.TDOCCOD AND T6.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC7MTG) AS M2BATCC
                                                        FROM CLWM2AC7 GROUP BY TDOCCOD, T01NDOC ) T7 ON T7.TDOCCOD = T1.TDOCCOD AND T7.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T21.M2PA1TOT, 0)) AS m2batpc, T20.TDOCCOD, T20.T01NDOC
                                                        FROM (CLWM2PA1 T20 LEFT JOIN (SELECT SUM(M2PA1MTG) AS M2PA1TOT, TDOCCOD, T01NDOC, M2PA1NRO
                                                        FROM CLWM2PA2 GROUP BY TDOCCOD, T01NDOC, M2PA1NRO ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC AND T21.M2PA1NRO = T20.M2PA1NRO) GROUP BY T20.TDOCCOD, T20.T01NDOC ) T8 ON T8.TDOCCOD = T1.TDOCCOD AND T8.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T21.M2PA2TOT, 0)) AS M2BATPN, T20.TDOCCOD, T20.T01NDOC
                                                        FROM (CLWM2PA3 T20 LEFT JOIN (SELECT SUM(M2PA2MTG) AS M2PA2TOT, TDOCCOD, T01NDOC, M2PA2NRO
                                                        FROM CLWM2PA4 GROUP BY TDOCCOD, T01NDOC, M2PA2NRO ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC AND T21.M2PA2NRO = T20.M2PA2NRO) GROUP BY T20.TDOCCOD, T20.T01NDOC ) T9 ON T9.TDOCCOD = T1.TDOCCOD AND T9.T01NDOC = T1.T01NDOC) INNER JOIN (SELECT COALESCE( T22.m2batpc, 0) + COALESCE( T21.M2BATPN, 0) AS M2BATPAS, T20.TDOCCOD, T20.T01NDOC FROM ((CLWT01 T20 LEFT JOIN (SELECT SUM(COALESCE( T24.M2PA2TOT, 0)) AS M2BATPN, T23.TDOCCOD, T23.T01NDOC FROM (CLWM2PA3 T23 LEFT JOIN (SELECT SUM(M2PA2MTG) AS M2PA2TOT, TDOCCOD, T01NDOC, M2PA2NRO FROM CLWM2PA4 GROUP BY TDOCCOD, T01NDOC, M2PA2NRO ) T24 ON T24.TDOCCOD = T23.TDOCCOD AND T24.T01NDOC = T23.T01NDOC AND T24.M2PA2NRO = T23.M2PA2NRO) GROUP BY T23.TDOCCOD, T23.T01NDOC ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T24.M2PA1TOT, 0)) AS m2batpc, T23.TDOCCOD, T23.T01NDOC FROM (CLWM2PA1 T23 LEFT JOIN (SELECT SUM(M2PA1MTG) AS M2PA1TOT, TDOCCOD, T01NDOC, M2PA1NRO FROM CLWM2PA2 GROUP BY TDOCCOD, T01NDOC, M2PA1NRO ) T24 ON T24.TDOCCOD = T23.TDOCCOD AND T24.T01NDOC = T23.T01NDOC AND T24.M2PA1NRO = T23.M2PA1NRO) GROUP BY T23.TDOCCOD, T23.T01NDOC ) T22 ON T22.TDOCCOD = T20.TDOCCOD AND T22.T01NDOC = T20.T01NDOC) ) T10 ON T10.TDOCCOD = T1.TDOCCOD AND T10.T01NDOC = T1.T01NDOC) INNER JOIN (SELECT COALESCE( COALESCE( T21.M2BATPAS, 0), 0) + COALESCE( T22.M2BAACT, 0) - COALESCE( T21.M2BATPAS, 0) AS M2BATPYN, T20.TDOCCOD, T20.T01NDOC FROM ((CLWT01 T20 INNER JOIN (SELECT COALESCE( T25.m2batpc, 0) + COALESCE( T24.M2BATPN, 0) AS M2BATPAS, T23.TDOCCOD, T23.T01NDOC FROM ((CLWT01 T23 LEFT JOIN (SELECT SUM(COALESCE( T27.M2PA2TOT, 0)) AS M2BATPN, T26.TDOCCOD, T26.T01NDOC FROM (CLWM2PA3 T26 LEFT JOIN (SELECT SUM(M2PA2MTG) AS M2PA2TOT, TDOCCOD, T01NDOC, M2PA2NRO FROM CLWM2PA4 GROUP BY TDOCCOD, T01NDOC, M2PA2NRO ) T27 ON T27.TDOCCOD = T26.TDOCCOD AND T27.T01NDOC = T26.T01NDOC AND T27.M2PA2NRO = T26.M2PA2NRO) GROUP BY T26.TDOCCOD, T26.T01NDOC ) T24 ON T24.TDOCCOD = T23.TDOCCOD AND T24.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T27.M2PA1TOT, 0)) AS m2batpc, T26.TDOCCOD, T26.T01NDOC FROM (CLWM2PA1 T26 LEFT JOIN (SELECT SUM(M2PA1MTG) AS M2PA1TOT, TDOCCOD, T01NDOC, M2PA1NRO FROM CLWM2PA2 GROUP BY TDOCCOD, T01NDOC, M2PA1NRO ) T27 ON T27.TDOCCOD = T26.TDOCCOD AND T27.T01NDOC = T26.T01NDOC AND T27.M2PA1NRO = T26.M2PA1NRO) GROUP BY T26.TDOCCOD, T26.T01NDOC ) T25 ON T25.TDOCCOD = T23.TDOCCOD AND T25.T01NDOC = T23.T01NDOC) ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC) INNER JOIN CLWT01 T22 ON T22.TDOCCOD = T20.TDOCCOD AND T22.T01NDOC = T20.T01NDOC) ) T11 ON T11.TDOCCOD = T1.TDOCCOD AND T11.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT SUM(M2IN1IO2) AS M2BATA2, TDOCCOD, T01NDOC, SUM(M2IN1IO1) AS M2BATA1 FROM CLWM2IN1 GROUP BY TDOCCOD, T01NDOC ) T12 ON T12.TDOCCOD = T1.TDOCCOD AND T12.T01NDOC = T1.T01NDOC) INNER JOIN (SELECT ( COALESCE( T21.M2BAIP2, 0)) + ( COALESCE( T22.M2BAOA2, 0)) AS M2BATIP2, T20.TDOCCOD, T20.T01NDOC, ( COALESCE( T23.M2BAIP1, 0)) + ( COALESCE( T24.M2BAOA1, 0)) AS M2BATIP1, ( COALESCE( COALESCE( T25.M2BATIN, 0), 0)) + ( COALESCE( COALESCE( T26.M2BATTA, 0), 0)) + ( COALESCE( COALESCE( T27.M2BATMA, 0), 0)) + ( COALESCE( COALESCE( T28.M2BATRO, 0), 0)) + ( COALESCE( COALESCE( T29.M2BATCC, 0), 0)) + ( COALESCE( COALESCE( T30.M2BATAC, 0), 0)) AS M2BAACT FROM ((((((((((CLWT01 T20 LEFT JOIN (SELECT SUM(M2IN2IP2) AS M2BAIP2, TDOCCOD, T01NDOC FROM CLWM2IN2 GROUP BY TDOCCOD, T01NDOC ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT SUM(M2IN3IP2) AS M2BAOA2, TDOCCOD, T01NDOC FROM CLWM2IN3 GROUP BY TDOCCOD, T01NDOC ) T22 ON T22.TDOCCOD = T20.TDOCCOD AND T22.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT SUM(M2IN2IP1) AS M2BAIP1, TDOCCOD, T01NDOC FROM CLWM2IN2 GROUP BY TDOCCOD, T01NDOC ) T23 ON T23.TDOCCOD = T20.TDOCCOD AND T23.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT SUM(M2IN3IP1) AS M2BAOA1, TDOCCOD, T01NDOC FROM CLWM2IN3 GROUP BY TDOCCOD, T01NDOC ) T24 ON T24.TDOCCOD = T20.TDOCCOD AND T24.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC2MTG) AS M2BATIN FROM CLWM2AC2 GROUP BY TDOCCOD, T01NDOC ) T25 ON T25.TDOCCOD = T20.TDOCCOD AND T25.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC3MTG) AS M2BATTA FROM CLWM2AC3 GROUP BY TDOCCOD, T01NDOC ) T26 ON T26.TDOCCOD = T20.TDOCCOD AND T26.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC4MTG) AS M2BATMA FROM CLWM2AC4 GROUP BY TDOCCOD, T01NDOC ) T27 ON T27.TDOCCOD = T20.TDOCCOD AND T27.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC6MTG) AS M2BATRO FROM CLWM2AC6 GROUP BY TDOCCOD, T01NDOC ) T28 ON T28.TDOCCOD = T20.TDOCCOD AND T28.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC7MTG) AS M2BATCC FROM CLWM2AC7 GROUP BY TDOCCOD, T01NDOC ) T29 ON T29.TDOCCOD = T20.TDOCCOD AND T29.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT SUM(M2AC1MTG) AS M2BATAC, TDOCCOD, T01NDOC
                                                        FROM CLWM2AC1 GROUP BY TDOCCOD, T01NDOC ) T30 ON T30.TDOCCOD = T20.TDOCCOD AND T30.T01NDOC = T20.T01NDOC) ) T13 ON T13.TDOCCOD = T1.TDOCCOD AND T13.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT SUM(M2IN3IP2) AS M2BAOA2, TDOCCOD, T01NDOC FROM CLWM2IN3 GROUP BY TDOCCOD, T01NDOC ) T14 ON T14.TDOCCOD = T1.TDOCCOD AND T14.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT SUM(M2IN3IP1) AS M2BAOA1, TDOCCOD, T01NDOC FROM CLWM2IN3 GROUP BY TDOCCOD, T01NDOC ) T15 ON T15.TDOCCOD = T1.TDOCCOD AND T15.T01NDOC = T1.T01NDOC) INNER JOIN (SELECT ( COALESCE( COALESCE( T21.M2BATIP1, 0), 0)) - ( COALESCE( T22.M2BATE1, 0)) AS M2BAIN1, T20.TDOCCOD, T20.T01NDOC FROM ((CLWT01 T20 INNER JOIN CLWT01 T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T24.M2EG1TC1, 0)) AS M2BATE1, T23.TDOCCOD, T23.T01NDOC FROM (CLWM2EG1 T23 LEFT JOIN (SELECT SUM(M2EG1CP1) AS M2EG1TC1, TDOCCOD, T01NDOC, M2EG1NRO FROM CLWM2EG2 GROUP BY TDOCCOD, T01NDOC, M2EG1NRO ) T24 ON T24.TDOCCOD = T23.TDOCCOD AND T24.T01NDOC = T23.T01NDOC AND T24.M2EG1NRO = T23.M2EG1NRO) GROUP BY T23.TDOCCOD, T23.T01NDOC ) T22 ON T22.TDOCCOD = T20.TDOCCOD AND T22.T01NDOC = T20.T01NDOC) ) T16 ON T16.TDOCCOD = T1.TDOCCOD AND T16.T01NDOC = T1.T01NDOC) INNER JOIN (SELECT ( COALESCE( COALESCE( T21.M2BATIP2, 0), 0)) - ( COALESCE( T22.M2BATE2, 0)) AS M2BAIN2, T20.TDOCCOD, T20.T01NDOC FROM ((CLWT01 T20 INNER JOIN (SELECT ( COALESCE( T24.M2BAIP2, 0)) + ( COALESCE( T25.M2BAOA2, 0)) AS M2BATIP2, T23.TDOCCOD, T23.T01NDOC, ( COALESCE( T26.M2BAIP1, 0)) + ( COALESCE( T27.M2BAOA1, 0)) AS M2BATIP1, ( COALESCE( COALESCE( T28.M2BATIN, 0), 0)) + ( COALESCE( COALESCE( T29.M2BATTA, 0), 0)) + ( COALESCE( COALESCE( T30.M2BATMA, 0), 0)) + ( COALESCE( COALESCE( T31.M2BATRO, 0), 0)) + ( COALESCE( COALESCE( T32.M2BATCC, 0), 0)) + ( COALESCE( COALESCE( T33.M2BATAC, 0), 0)) AS M2BAACT FROM ((((((((((CLWT01 T23 LEFT JOIN (SELECT SUM(M2IN2IP2) AS M2BAIP2, TDOCCOD, T01NDOC FROM CLWM2IN2 GROUP BY TDOCCOD, T01NDOC ) T24 ON T24.TDOCCOD = T23.TDOCCOD AND T24.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT SUM(M2IN3IP2) AS M2BAOA2, TDOCCOD, T01NDOC FROM CLWM2IN3 GROUP BY TDOCCOD, T01NDOC ) T25 ON T25.TDOCCOD = T23.TDOCCOD AND T25.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT SUM(M2IN2IP1) AS M2BAIP1, TDOCCOD, T01NDOC FROM CLWM2IN2 GROUP BY TDOCCOD, T01NDOC ) T26 ON T26.TDOCCOD = T23.TDOCCOD AND T26.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT SUM(M2IN3IP1) AS M2BAOA1, TDOCCOD, T01NDOC FROM CLWM2IN3 GROUP BY TDOCCOD, T01NDOC ) T27 ON T27.TDOCCOD = T23.TDOCCOD AND T27.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC2MTG) AS M2BATIN FROM CLWM2AC2 GROUP BY TDOCCOD, T01NDOC ) T28 ON T28.TDOCCOD = T23.TDOCCOD AND T28.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC3MTG) AS M2BATTA FROM CLWM2AC3 GROUP BY TDOCCOD, T01NDOC ) T29 ON T29.TDOCCOD = T23.TDOCCOD AND T29.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC4MTG) AS M2BATMA FROM CLWM2AC4 GROUP BY TDOCCOD, T01NDOC ) T30 ON T30.TDOCCOD = T23.TDOCCOD AND T30.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC6MTG) AS M2BATRO FROM CLWM2AC6 GROUP BY TDOCCOD, T01NDOC ) T31 ON T31.TDOCCOD = T23.TDOCCOD AND T31.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT TDOCCOD, T01NDOC, SUM(M2AC7MTG) AS M2BATCC FROM CLWM2AC7 GROUP BY TDOCCOD, T01NDOC ) T32 ON T32.TDOCCOD = T23.TDOCCOD AND T32.T01NDOC = T23.T01NDOC) LEFT JOIN (SELECT SUM(M2AC1MTG) AS M2BATAC, TDOCCOD, T01NDOC FROM CLWM2AC1 GROUP BY TDOCCOD, T01NDOC ) T33 ON T33.TDOCCOD = T23.TDOCCOD AND T33.T01NDOC = T23.T01NDOC) ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T24.M2EG1TC2, 0)) AS M2BATE2, T23.TDOCCOD, T23.T01NDOC FROM (CLWM2EG1 T23 LEFT JOIN (SELECT SUM(M2EG1CP2) AS M2EG1TC2, TDOCCOD, T01NDOC, M2EG1NRO FROM CLWM2EG2 GROUP BY TDOCCOD, T01NDOC, M2EG1NRO ) T24 ON T24.TDOCCOD = T23.TDOCCOD AND T24.T01NDOC = T23.T01NDOC AND T24.M2EG1NRO = T23.M2EG1NRO) GROUP BY T23.TDOCCOD, T23.T01NDOC ) T22 ON T22.TDOCCOD = T20.TDOCCOD AND T22.T01NDOC = T20.T01NDOC) ) T17 ON T17.TDOCCOD = T1.TDOCCOD AND T17.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T21.M2EG1TC2, 0)) AS M2BATE2, T20.TDOCCOD, T20.T01NDOC FROM (CLWM2EG1 T20 LEFT JOIN (SELECT SUM(M2EG1CP2) AS M2EG1TC2, TDOCCOD, T01NDOC, M2EG1NRO FROM CLWM2EG2 GROUP BY TDOCCOD, T01NDOC, M2EG1NRO ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC AND T21.M2EG1NRO = T20.M2EG1NRO) GROUP BY T20.TDOCCOD, T20.T01NDOC ) T18 ON T18.TDOCCOD = T1.TDOCCOD AND T18.T01NDOC = T1.T01NDOC) LEFT JOIN (SELECT SUM(COALESCE( T21.M2EG1TC1, 0)) AS M2BATE1, T20.TDOCCOD, T20.T01NDOC FROM (CLWM2EG1 T20 LEFT JOIN (SELECT SUM(M2EG1CP1) AS M2EG1TC1, TDOCCOD, T01NDOC, M2EG1NRO FROM CLWM2EG2 GROUP BY TDOCCOD, T01NDOC, M2EG1NRO ) T21 ON T21.TDOCCOD = T20.TDOCCOD AND T21.T01NDOC = T20.T01NDOC AND T21.M2EG1NRO = T20.M2EG1NRO) GROUP BY T20.TDOCCOD, T20.T01NDOC ) T19 ON T19.TDOCCOD = T1.TDOCCOD AND T19.T01NDOC = T1.T01NDOC) WHERE T1.TDOCCOD = ? and T1.T01NDOC = ? ORDER BY T1.TDOCCOD, T1.T01NDOC  FOR FETCH ONLY

 

Intente cambiar la propiedad Join Management a Client, pero el error sigue.

 

Donde se puede desactivar la optimizacion de los sum?

Edgar Jimenez Demestri
Consultor Genexus