Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Unanswered: [Optimizations] Hits DB2 to execute first the WITH tables

    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

  2. #2
    Join Date
    Apr 2011
    Posts
    5
    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!

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  4. #4
    Join Date
    Apr 2011
    Posts
    5
    Quote Originally Posted by dav1mo View Post
    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Apr 2011
    Posts
    3
    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.

  7. #7
    Join Date
    Apr 2011
    Posts
    5
    Quote Originally Posted by newdb2db View Post
    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?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;
    Last edited by tonkuma; 04-09-11 at 12:49. Reason: Edit slightly format of code. (add a blank after "0" or "1" in NULLIF function)

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 04-09-11 at 14:11. Reason: Add 3)

  10. #10
    Join Date
    Apr 2011
    Posts
    5
    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.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    (I'm not sure.)

    I reviewed and replaced all the code considering following my note.
    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
    ;
    Last edited by tonkuma; 04-14-11 at 04:56. Reason: replaced all the code considering my note.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
           )
       )
    )
    Last edited by tonkuma; 04-27-11 at 04:13.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •