[Optimizations] Hits DB2 to execute first the WITH tables

marcomsousa · #1 (**permalink**) 04-06-11, 11:35

Hello,

I have this sql.

Code:

With tmpTable as (SELECT * FROM xxx
             WHERE XXX
             GROUP BY  XXX
             HAVING  XXX),
 tmpTable_2 as (SELECT * FROM tmpDable
             WHERE XXX
             GROUP BY  XXX
             HAVING  XXX)

SELECT * 
FROM tmpTable_2 
JOIN XXXX

UNION ALL

SELECT * 
FROM tmpTable_2 
JOIN XXXX
JOIN YYYY

UNION ALL


SELECT * 
FROM tmpTable_2 
JOIN XXXX
JOIN YYYY
JOIN EEEE

But is really slow... the tmpTable and the tmpTable_2 returns both less that 30000 rows.. I think that the DB2 don't know that have to ejectute before the tmpTable_2 and then the JOIN in the select.

So, how to use the HINTS to tell the DB2 to first return the rows of tmpTables..
I allready try it with OPTIMIZE FOR 1 ROW inside the WITH but don't compile..
Can you tell how to optimize it?

Thank you

marcomsousa · #2 (**permalink**) 04-07-11, 06:23

Ok, I start to create one TEMPORARY TABLE and insert the with content.

and the the select from TEMPORARY TABLE.

I think thats is much more faster!

dav1mo · #3 (**permalink**) 04-07-11, 09:35

Why the temp tables? Is this data that does not reside in DB2 or are you populating them from some other select statement. If the latter, why go through the extra work of creating and inserting to temp table, then querying it as a tablescan? You could just put your entire SQL together and work on improving that performance. I have yet to see a scenario when using data that already resides in the database was done faster/better than just querying it in place. The original intent for temp tables was to bring in non-database data and be able to join it to your existing tables.
Dave Nance

marcomsousa · #4 (**permalink**) 04-07-11, 09:58

Quote:

Originally Posted by dav1mo

Why the temp tables? Is this data that does not reside in DB2 or are you populating them from some other select statement. If the latter, why go through the extra work of creating and inserting to temp table, then querying it as a tablescan? You could just put your entire SQL together and work on improving that performance. I have yet to see a scenario when using data that already resides in the database was done faster/better than just querying it in place. The original intent for temp tables was to bring in non-database data and be able to join it to your existing tables.
Dave Nance

I can't put my entire SQL together because I need the union ALL because all 3 SELECT are with diferent GROUP BY in the same columns.

I create the TEMP TABLE, because DB2 are filter first the JOIN TABLES, that have lots of millons of rows. DB2 don't know that the with tables have only 1000 rows, so with temp tables I'm force to DB2 first ejecute the with select.

tonkuma · #5 (**permalink**) 04-07-11, 12:29

Quote:

I can't put my entire SQL together because I need the union ALL because all 3 SELECT are with diferent GROUP BY in the same columns.

If it is the only reason to use union all, you may want to use GROUPING SETS in a subquery.

If you provided more concrete query, I might be able to show you some way to rewrite it.
Your query(showed in original post) was something strange for me.
For example: It would be syntax error to specify "SELECT * " and "GROUP BY ..." in a subquery, unless specifying all columns in GROUP BY clause.

newdb2db · #6 (**permalink**) 04-07-11, 12:43

Please try to replicate the query in proper way rather that SELECT *....I mean with the column name. That way, it is easy to understand and help to analyze the query.

Thanks.

marcomsousa · #7 (**permalink**) 04-08-11, 05:18

Quote:

Originally Posted by newdb2db

Please try to replicate the query in proper way rather that SELECT *....I mean with the column name. That way, it is easy to understand and help to analyze the query.

Thanks.

The first post was only an example of the real query:

Code:

WITH 
TABLA1 AS(SELECT FACEXPID AS EXPLOT, FACFECFACT AS FECHA, FACID AS FACTURN, FACESTADO AS ESTADO, FACPOCID AS CICLO, FACIMPORTE AS IMPORT,    
                FACIMPUEST AS IMPUEST, FACSOCPRO AS PROPIET, FACCNTTNUM AS CONTRATO, FACNUMFAC AS DOCUMEN, FBLECONSREG AS CONSREG     
        FROM FACTURA
        LEFT JOIN OPECARFAC ON FACID = OCFFACTURA    
        LEFT JOIN OPECARGEST ON OCFOPECART = OCGID AND OCGPCSID = 5108
        LEFT JOIN SESION ON OCGSESDEF = SESID    
        LEFT JOIN POLCICLO ON FACPOCID = POCID AND POCEXPID = 5108
        LEFT JOIN FACTURABLE ON FBLEPOCID = POCID AND FBLEFTOID = FACFTOID     
        LEFT JOIN FACTURACIO ON  FTOID = FACFTOID AND FTOEXPID = 5108
        WHERE FACEXPID = 5108 
          AND FACFECFACT <= '2011-04-04'    
          AND FACESTADO NOT IN (1,9)
        GROUP BY FACEXPID, FACFECFACT, FACID, FACESTADO, FACPOCID, FACIMPORTE,FACIMPUEST, FACSOCPRO, FACCNTTNUM, FACNUMFAC, FBLECONSREG 
        HAVING SUM(CASE WHEN SESFECHA <= '2011-04-04'  AND OCGOPERACI = 1 THEN 1                 
                        WHEN SESFECHA <= '2011-04-04'  AND OCGOPERACI = 2 THEN -1                 
                        WHEN SESFECHA <= '2011-04-04'  AND OCGOPERACI = 5 THEN 1                 
                        WHEN SESFECHA <= '2011-04-04'  AND OCGOPERACI = 6 THEN 1                 
                        WHEN SESFECHA <= '2011-04-04'  AND OCGOPERACI = 10 THEN -1                 
                  ELSE 0                 
                  END) <= 0), 
TABLA2 AS ( SELECT EXPLOT, FECHA, FACTURN, CICLO, IMPORT, IMPUEST, PROPIET, CONTRATO, DOCUMEN, CONSREG 
                      FROM TABLA1
                      LEFT JOIN RELFACTURA ON FACTURN = RFAORIGEN AND RFATIPREL = 1   
                      LEFT JOIN FACTURA ON RFARELAC = FACID 
                      WHERE ESTADO <>  6   
                             OR (ESTADO = 6 AND RFARELAC IS NOT NULL AND FACFECFACT > '2011-04-04')
                             )                             
                    

  SELECT EXPLOT,    ' ' AS PROPIETARIO, SUM(IMPORT) AS IMPORTE, SUM(IMPORT - IMPUEST) AS BASE, SUM(IMPUEST) AS IMPUESTOS, ' ' AS CONCEPTO,  CAST(NULL AS DECIMAL)  AS M3FACT,  CAST(NULL AS DECIMAL)  AS M3REG, SUM(IMPORT - IMPUEST) AS BASE_CPTO,    COUNT(DISTINCT CONTRATO) AS CONTRATOS, COUNT(DISTINCT DOCUMEN) AS DOCUMENTOS 
    FROM TABLA2 
    GROUP BY EXPLOT

  UNION ALL 

  SELECT EXPLOT,RTRIM(PRSNOMCPTO) AS PROPIETARIO,SUM(IMPORT) AS IMPORTE, SUM(IMPORT - IMPUEST) AS BASE, SUM(IMPUEST) AS IMPUESTOS,' ' AS CONCEPTO,  CAST(NULL AS DECIMAL)  AS M3FACT,  CAST(NULL AS DECIMAL)  AS M3REG,  CAST(NULL AS DECIMAL)  AS BASE_CPTO,  CAST(NULL AS DECIMAL)  AS CONTRATOS,  CAST(NULL AS DECIMAL)  AS DOCUMENTOS 
    FROM TABLA2 
   JOIN PERSONA ON PROPIET = PRSID 
    GROUP BY EXPLOT , PRSNOMCPTO 

  UNION ALL 

  SELECT EXPLOT,    RTRIM(PRSNOMCPTO) AS PROPIETARIO,  CAST(NULL AS DECIMAL)  AS IMPORTE,  CAST(NULL AS DECIMAL)  AS BASE,  CAST(NULL AS DECIMAL)  AS IMPUESTOS, RTRIM(TDDESC) AS CONCEPTO, SUM(CASE WHEN TSUBSNCONSUMO = 'S' THEN LINFACCANT END) AS M3FACT,   SUM(DISTINCT(CASE WHEN TSUBSNCONSUMO = 'S' THEN CONSREG END)) AS M3REG, SUM(LINFACIMPO) AS BASE_CPTO,    COUNT(DISTINCT CONTRATO) AS CONTRATOS, COUNT(DISTINCT DOCUMEN) AS DOCUMENTOS 
    FROM TABLA2    
    JOIN PERSONA ON PROPIET = PRSID    
    JOIN LINFACTURA ON FACTURN = LINFACID   
    JOIN TIPOSUBCON ON TSUBID=LINSCPTOID    
    JOIN TIPOCONCEP ON LINCPTOID = TCONID    
    JOIN TABLADESC ON TCONTXTID = TDTXTID AND TDIDICOD = 'es' 
    GROUP BY EXPLOT , PRSNOMCPTO, TDDESC ORDER BY PROPIETARIO DESC, CONCEPTO

I think that can't use the 'GROUPING SETS' because all SELECT have diferent source of the same column output.

Any help?

tonkuma · #8 (**permalink**) 04-08-11, 11:22

Because, number of corresponding rows in each join in final selects were not clear,
if (as a simplest case) I assumed that each one row of a table would be joined with one row of another table on each join
(for example, sum of IMPORTE of a EXPLOT in second subselect is equal to IMPORTE of same EXPLOT in first subquery in union all.),
the query would be rewritten to like the following example.

If not, I want to consider to change some joins to outer join(and add having clause to eliminate extra rows and slightly modify select list).

Code:

WITH 
TABLA1 AS (
...
)
,
TABLA2 AS (
...
)

SELECT
       EXPLOT
     , COALESCE( RTRIM(PRSNOMCPTO) , '') AS PROPIETARIO
     , COALESCE( RTRIM(TDDESC)     , '') AS CONCEPTO
     , SUM(IMPORT)           * NULLIF( GROUPING(TDDESC) , 0 ) AS IMPORTE
     , SUM(IMPORT - IMPUEST) * NULLIF( GROUPING(TDDESC) , 0 ) AS BASE
     , SUM(IMPUEST)          * NULLIF( GROUPING(TDDESC) , 0 ) AS IMPUESTOS
     , SUM(CASE
           WHEN TSUBSNCONSUMO = 'S' THEN
                LINFACCANT
           END
          ) * NULLIF( GROUPING(TDDESC) , 1 ) AS M3FACT

     , SUM(DISTINCT
           CASE
           WHEN TSUBSNCONSUMO = 'S' THEN
                CONSREG
           END
          ) * NULLIF( GROUPING(TDDESC) , 1 ) AS M3REG
     , CASE
       WHEN GROUPING(PRSNOMCPTO) = 1
        AND GROUPING(TDDESC)     = 1 THEN
            SUM(IMPORT - IMPUEST)
       WHEN GROUPING(TDDESC)     = 0 THEN
            SUM(LINFACIMPO)
       END  AS BASE_CPTO

     , CASE
       WHEN GROUPING(PRSNOMCPTO) = 1
        AND GROUPING(TDDESC)     = 1
        OR  GROUPING(TDDESC)     = 0 THEN
            COUNT(DISTINCT CONTRATO)
       END  AS CONTRATOS
     , CASE
       WHEN GROUPING(PRSNOMCPTO) = 1
        AND GROUPING(TDDESC)     = 1
        OR  GROUPING(TDDESC)     = 0 THEN
            COUNT(DISTINCT DOCUMEN)
       END  AS DOCUMENTOS
  FROM TABLA2 
  JOIN PERSONA
   ON  PROPIET   = PRSID
  JOIN LINFACTURA
   ON  FACTURN   = LINFACID
  JOIN TIPOSUBCON
   ON  TSUBID    = LINSCPTOID
  JOIN TIPOCONCEP
   ON  LINCPTOID = TCONID    
  JOIN TABLADESC
   ON  TCONTXTID = TDTXTID
   AND TDIDICOD  = 'es' 
 GROUP BY
       GROUPING SETS
       (  (EXPLOT)
        , (EXPLOT , PRSNOMCPTO) 
        , (EXPLOT , PRSNOMCPTO , TDDESC)
       )
 ORDER BY
       PROPIETARIO DESC
     , CONCEPTO
;

tonkuma · #9 (**permalink**) 04-09-11, 12:06

Another possibility of rewriting.

1) I thought TABLA1 and TABLA2 could be conbined into one CTE(say (new) TABLA2), like...

Code:

WITH 
TABLA2 AS (
SELECT FACEXPID    AS EXPLOT
     , FACFECFACT  AS FECHA
     , FACID       AS FACTURN
--     , FACESTADO   AS ESTADO
     , FACPOCID    AS CICLO
     , FACIMPORTE  AS IMPORT
     , FACIMPUEST  AS IMPUEST
     , FACSOCPRO   AS PROPIET
     , FACCNTTNUM  AS CONTRATO
     , FACNUMFAC   AS DOCUMEN
     , FBLECONSREG AS CONSREG
  FROM FACTURA    AS fac1
  LEFT JOIN
       OPECARFAC
   ON  FACID      = OCFFACTURA    
  LEFT JOIN
       OPECARGEST
   ON  OCFOPECART = OCGID
   AND OCGPCSID   = 5108
  LEFT JOIN
       SESION
   ON  OCGSESDEF  = SESID    
  LEFT JOIN
       POLCICLO
   ON  FACPOCID   = POCID
   AND POCEXPID   = 5108
  LEFT JOIN
       FACTURABLE
   ON  FBLEPOCID  = POCID
   AND FBLEFTOID  = FACFTOID     
  LEFT JOIN
       FACTURACIO
   ON  FTOID      = FACFTOID
   AND FTOEXPID   = 5108
 WHERE FACEXPID   = 5108 
   AND FACFECFACT <= '2011-04-04'
   AND FACESTADO NOT IN (1,9)
 GROUP BY
       FACEXPID
     , FACFECFACT
     , FACID
     , FACESTADO
     , FACPOCID
     , FACIMPORTE
     , FACIMPUEST
     , FACSOCPRO
     , FACCNTTNUM
     , FACNUMFAC
     , FBLECONSREG
 HAVING
       SUM(CASE
           WHEN SESFECHA <= '2011-04-04'
            AND OCGOPERACI IN (1 , 5 , 6) THEN
                +1
           WHEN SESFECHA <= '2011-04-04'
            AND OCGOPERACI IN (2 , 10)    THEN
                -1
           END
          ) <= 0
   AND
   (   FACESTADO <> 6
    OR
       FACESTADO =  6
       AND
       EXISTS
       (SELECT 0
          FROM RELFACTURA
          JOIN FACTURA    fac2
           ON  fac2.FACID      = RFARELAC
           AND fac2.FACFECFACT > '2011-04-04'
         WHERE RFAORIGEN = fac1.FACID
           AND RFATIPREL = 1
       )
   )
)

2) The meaning of the condition would be more clear, by rewriting first condition of HAVING clause, like ...

Code:

 HAVING
       COUNT(CASE
             WHEN SESFECHA <= '2011-04-04'
              AND OCGOPERACI IN (1 , 5 , 6) THEN
                  0
             END
            )
       <=
       COUNT(CASE
             WHEN SESFECHA <= '2011-04-04'
              AND OCGOPERACI IN (2 , 10)    THEN
                  0
             END
            )
   AND
   (   FACESTADO <> 6
    OR
    ...
    ...
   )

3) The second condition of HAVING clause may be moved into WHERE clause.

marcomsousa · #10 (**permalink**) 04-13-11, 09:09

tonkuma, thank you,

I just have one problem with the GROUPING SETS.

The IMPORTE,BASE and IMPUESTOS columns are sum wrong, because the
JOIN LINFACTURA ON FACTURN = LINFACID are duplicating the rows. So the SUM(IMPORT), and ... have all rows summed and isn't the original query.

tonkuma · #11 (**permalink**) 04-13-11, 13:17

How about this?
(I'm not sure.)

I reviewed and replaced all the code considering following my note.

Quote:

If not, I want to consider to change some joins to outer join(and add having clause to eliminate extra rows and slightly modify select list).

Code:

WITH 
...
...

SELECT grp1.EXPLOT
     , PROPIETARIO
     , MAX(IMPORTE)   * NULLIF( GROUPING(TDDESC) , 0 ) AS IMPORTE
     , MAX(BASE)      * NULLIF( GROUPING(TDDESC) , 0 ) AS BASE
     , MAX(IMPUESTOS) * NULLIF( GROUPING(TDDESC) , 0 ) AS IMPUESTOS
     , COALESCE( RTRIM(TDDESC)     , '' )              AS CONCEPTO
     , SUM(CASE
           WHEN TSUBSNCONSUMO = 'S' THEN
                LINFACCANT
           END
          ) * NULLIF( 1 , GROUPING(TDDESC) ) AS M3FACT
     , SUM(DISTINCT
           CASE
           WHEN TSUBSNCONSUMO = 'S' THEN
                CONSREG
           END
          ) * NULLIF( 1 , GROUPING(TDDESC) ) AS M3REG
     , CASE
       WHEN GROUPING(TDDESC) = 1
            MAX(BASE)
       WHEN GROUPING(TDDESC) = 0 THEN
            SUM(LINFACIMPO)
       END  AS BASE_CPTO
     , CASE
       WHEN GROUPING(TDDESC) = 0 THEN
            COUNT(DISTINCT CONTRATO)
       WHEN PROPIETARIO = ''     THEN
            MAX(CONTRATOS)
       END  AS CONTRATOS
     , CASE
       WHEN GROUPING(TDDESC) = 0 THEN
            COUNT(DISTINCT DOCUMEN)
       WHEN PROPIETARIO = ''     THEN
            MAX(DOCUMENTOS)
       END  AS DOCUMENTOS
  FROM (SELECT
               EXPLOT
             , COALESCE( RTRIM(PRSNOMCPTO) , '' ) AS PROPIETARIO
             , SUM(IMPORT)                        AS IMPORTE
             , SUM(IMPORT - IMPUEST)              AS BASE
             , SUM(IMPUEST)                       AS IMPUESTOS
             , COUNT(DISTINCT CONTRATO) * NULLIF( GROUPING(PRSNOMCPTO) , 0 ) AS CONTRATOS
             , COUNT(DISTINCT DOCUMEN)  * NULLIF( GROUPING(PRSNOMCPTO) , 0 ) AS DOCUMENTOS 
          FROM TABLA2 
          JOIN PERSONA
           ON  PROPIET   = PRSID
         GROUP BY
               GROUPING SETS
               (  (EXPLOT)
                , (EXPLOT , PRSNOMCPTO)
               ) 
       ) AS grp1
  JOIN TABLA2 t2
   ON  t2.EXPLOT = grp1.EXPLOT
  LEFT JOIN
       LINFACTURA
   ON  LINFACID  = FACTURN
   AND PROPIETARIO <> ''
  LEFT JOIN
       TIPOSUBCON
   ON  TSUBID    = LINSCPTOID    
  LEFT JOIN
       TIPOCONCEP
   ON  TCONID    = LINCPTOID
  LEFT JOIN
       TABLADESC
   ON  TDTXTID   = TCONTXTID
   AND TDIDICOD  = 'es' 
 GROUP BY
       GROUPING SETS
       (  (grp1.EXPLOT , PROPIETARIO) 
        , (grp1.EXPLOT , PROPIETARIO , TDDESC)
       )
 HAVING
       GROUPING(TDDESC) = 1
   OR  PROPIETARIO <> ''
 ORDER BY
       PROPIETARIO DESC
     , CONCEPTO
;

tonkuma · #12 (**permalink**) 04-16-11, 23:41

If counting for CONTRATOS and DOCUMENTOS was done only in outmost sub-select,
the query could be a little shorter and simplified like the following example.

But, it might not be so efficient than previous exmple,
because it would join and do grouping more rows in outmost sub-select.

Code:

WITH 
...
...

SELECT grp1.EXPLOT
     , PROPIETARIO
...
...
/*
     , CASE
       WHEN GROUPING(TDDESC) = 0 THEN
            COUNT(DISTINCT CONTRATO)
       WHEN PROPIETARIO = ''     THEN
            MAX(CONTRATOS)
       END  AS CONTRATOS
*/
     , CASE
       WHEN GROUPING(TDDESC) = 0
        OR  PROPIETARIO = ''     THEN
            COUNT(DISTINCT CONTRATO)
       END  AS CONTRATOS

/*
     , CASE
       WHEN GROUPING(TDDESC) = 0 THEN
            COUNT(DISTINCT DOCUMEN)
       WHEN PROPIETARIO = ''     THEN
            MAX(DOCUMENTOS)
       END  AS DOCUMENTOS
*/
     , CASE
       WHEN GROUPING(TDDESC) = 0
        OR  PROPIETARIO = ''     THEN
            COUNT(DISTINCT DOCUMEN)
       END  AS DOCUMENTOS

  FROM (SELECT
               EXPLOT
             , COALESCE( RTRIM(PRSNOMCPTO) , '' ) AS PROPIETARIO
             , SUM(IMPORT)                        AS IMPORTE
             , SUM(IMPORT - IMPUEST)              AS BASE
             , SUM(IMPUEST)                       AS IMPUESTOS
--             , COUNT(DISTINCT CONTRATO) * NULLIF( GROUPING(PRSNOMCPTO) , 0 ) AS CONTRATOS
--             , COUNT(DISTINCT DOCUMEN)  * NULLIF( GROUPING(PRSNOMCPTO) , 0 ) AS DOCUMENTOS
          FROM TABLA2 
          JOIN PERSONA
           ON  PROPIET   = PRSID
         GROUP BY
               GROUPING SETS
               (  (EXPLOT)
                , (EXPLOT , PRSNOMCPTO)
               ) 
       ) AS grp1
  JOIN TABLA2 t2
   ON  t2.EXPLOT = grp1.EXPLOT
  LEFT JOIN
       LINFACTURA
   ON  LINFACID  = FACTURN
--   AND PROPIETARIO <> ''
  LEFT JOIN
...
...
 GROUP BY
       GROUPING SETS
       (  (grp1.EXPLOT , PROPIETARIO) 
        , (grp1.EXPLOT , PROPIETARIO , TDDESC)
       )
 HAVING
       GROUPING(TDDESC) = 1
   OR  PROPIETARIO <> ''
 ORDER BY
       PROPIETARIO DESC
     , CONCEPTO
;

tonkuma · #13 (**permalink**) 04-27-11, 01:21

Although I want to investigate further more,
no response over one week and there is no DDL.

So, I need guess more over.

I guessed that first three characters of column name showed table, like
FAC: FACTURA
OCF: OPECARFAC
OCG: OPECARGEST
SES: SESION
POC: POLCICLO
FBL: FACTURABLE
FTL: FACTURACIO
RFA: RELFACTURA
PRS: PERSONA
LIN: LINFACTURA
TSU: TIPOSUBCON
TCO: TIPOCONCEP
TDx: TABLADESC

Because, select list of TABLA1 include only columns of FAC: FACTURA and FBL: FACTURABLE
and no aggregate function was used,
I guessed that no grouping was necessary in TABLA1.

Here is a rewrited example:
(TABLA1 and TABLA2 was combined to new TABLA2)

Code:

WITH 
TABLA2 AS (
SELECT FACEXPID    AS EXPLOT
     , FACFECFACT  AS FECHA
     , FACID       AS FACTURN
     , FACPOCID    AS CICLO
     , FACIMPORTE  AS IMPORT
     , FACIMPUEST  AS IMPUEST
     , FACSOCPRO   AS PROPIET
     , FACCNTTNUM  AS CONTRATO
     , FACNUMFAC   AS DOCUMEN
     , FBLECONSREG AS CONSREG
 FROM  FACTURA    AS fac1
 LEFT JOIN
       FACTURABLE AS fbl
   ON  FBLEPOCID  = FACPOCID
   AND FBLEFTOID  = FACFTOID
   AND EXISTS
       (SELECT 0
         FROM  POLCICLO
         WHERE FACPOCID = POCID
           AND POCEXPID = 5108
       )
 WHERE FACEXPID   =  5108 
   AND FACFECFACT <= '2011-04-04'
   AND FACESTADO NOT IN (1,9)
   AND EXISTS
       (SELECT 0
         FROM  OPECARFAC
         INNER JOIN
               OPECARGEST
           ON  OCFOPECART = OCGID
           AND OCGPCSID   = 5108
         INNER JOIN
               SESION
           ON  OCGSESDEF  =  SESID
           AND SESFECHA   <= '2011-04-04'
         WHERE OCFFACTURA = FACID
         HAVING
               SUM(CASE
                   WHEN OCGOPERACI IN (1 , 5 , 6) THEN
                        +1
                   WHEN OCGOPERACI IN (2 , 10)    THEN
                        -1
                   END
                  ) <= 0
       )
   AND
   (   FACESTADO <> 6
    OR
       FACESTADO =  6
       AND
       EXISTS
       (SELECT 0
         FROM  RELFACTURA
         INNER JOIN
               FACTURA    fac2
           ON  fac2.FACID      = RFARELAC
           AND fac2.FACFECFACT > '2011-04-04'
         WHERE RFAORIGEN = fac1.FACID
           AND RFATIPREL = 1
       )
   )
)

tonkuma · #14 (**permalink**) 05-17-11, 15:28

About 3 weeks pased since my last post without any response from marcomsousa.

I made three proposal in response to the following issue from marcomsousa.

Quote:

I just have one problem with the GROUPING SETS.

The IMPORTE,BASE and IMPUESTOS columns are sum wrong, because the
JOIN LINFACTURA ON FACTURN = LINFACID are duplicating the rows. So the SUM(IMPORT), and ... have all rows summed and isn't the original query.

I want to konw the results of my proposed rewriting of the query.
Wheather they worked or there were any issue on the rewritings?
Wheather they worked bettor or worse than original query?

Internet Services

Web Development

Digital Marketing

Consumer Tech