6 Replies Latest reply: Jun 24, 2015 4:30 PM by Chris Villa RSS

    Set Analysis SubQuery

    Chris Villa

      Hi

      I have a disadvantage developing the following scrip Set Analysis.

      I have 2 subquery to obtain the same result

       

      The 1st form:

       

      SELECT SUM(A.SALDO-B.SALDO)

      FROM (

      SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

      WHERE PERIODO='201502' AND CREESTID='02'

      GROUP BY ASOID

      ) A

      INNER JOIN(

      SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

      WHERE PERIODO='201503' AND CREESTID='02'

      GROUP BY ASOID

      ) B

      ON A.ASOID=B.ASOID

       

      ___________________________________________

      The 2nd form:

       

      SELECT SUM(SALDO) FROM

      (SELECT A.ASOID,SUM(B.SALDO-A.SALDO) SALDO

      FROM

      (SELECT ASOID, SUM(SALDO_CAPITAL ) SALDO

      FROM CRE_INVENTARIO

      WHERE CREESTID='02' AND PERIODO='201503'

      GROUP BY ASOID) A

      INNER JOIN

      (SELECT ASOID, SUM(SALDO_CAPITAL ) SALDO

      FROM CRE_INVENTARIO

      WHERE CREESTID='02' AND PERIODO='201502'

      GROUP BY ASOID) B

      ON A.ASOID=B.ASOID

      GROUP BY A.ASOID

      )

      _________________________



      CAN SOMEONE SOLVE THIS CASE IN SET ANALYSIS??

       

       

      Thanks for helping!

      Regards

       

      Chris

        • Re: Set Analysis SubQuery
          Ramon Covarrubias

          yes this can be done in set analysis, assuming your fields have the same name

           

          sum({<CREESTID={'02'},PERIODO={'201503'}>}SALDO)-sum({<CREESTID={'02'},PERIODO={'201502'}>}SALDO)


          here is a little more on set analysis

          A Primer on Set Analysis

          Set Analysis: syntaxes, examples

          • Re: Set Analysis SubQuery
            Ashish Tuti

            please try this expression:

             

            sum({<

              ASOID = p({< period = {'201502'}, CREESTID= {'02'}>}ASOID) * p({< period = {'201503'}, CREESTID= {'02'}>}ASOID),

              period = {'201502'},

              CREESTID= {'02'}   >}SALDO_CAPITAL

             

            )

            -

            sum({<

              ASOID = p({< period = {'201502'}, CREESTID= {'02'}>}ASOID) * p({< period = {'201503'}, CREESTID= {'02'}>}ASOID),

              period = {'201503'},

              CREESTID= {'02'}   >}SALDO_CAPITAL

            )

              • Re: Set Analysis SubQuery
                Chris Villa

                Great looks good shared syntax, however after its implementation does not generate the same result as the SQL query.

                I was reviewing the results and the difference is that the result of the expression in analysis generates the result set

                SELECT SUM (A.SALDO) - SUM (B.SALDO)

                FROM (

                SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                WHERE PERIODO='201502' AND CREESTID='02'

                GROUP BY ASOID

                ) A

                INNER JOIN(

                SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                WHERE PERIODO='201503' AND CREESTID='02'

                GROUP BY ASOID

                ) B

                ON A.ASOID=B.ASOID

                 

                 

                While the expectation is that the syntax dispose of the result set

                 

                SELECT SUM (A.SALDO-B.SALDO)

                FROM (

                SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                WHERE PERIODO='201502' AND CREESTID='02'

                GROUP BY ASOID

                ) A

                INNER JOIN(

                SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                WHERE PERIODO='201503' AND CREESTID='02'

                GROUP BY ASOID

                ) B

                ON A.ASOID=B.ASOID

                • Re: Set Analysis SubQuery
                  Chris Villa

                  Great looks good shared syntax, however after its implementation does not generate the same result as the SQL query.

                  I was reviewing the results and the difference is that the result of the expression in analysis generates the result set

                  SELECT SUM (A.SALDO) - SUM (B.SALDO)

                  FROM (

                  SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                  WHERE PERIODO='201502' AND CREESTID='02'

                  GROUP BY ASOID

                  ) A

                  INNER JOIN(

                  SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                  WHERE PERIODO='201503' AND CREESTID='02'

                  GROUP BY ASOID

                  ) B

                  ON A.ASOID=B.ASOID

                   

                   

                  While the expectation is that the syntax dispose of the result set

                   

                  SELECT SUM (A.SALDO-B.SALDO)

                  FROM (

                  SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                  WHERE PERIODO='201502' AND CREESTID='02'

                  GROUP BY ASOID

                  ) A

                  INNER JOIN(

                  SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

                  WHERE PERIODO='201503' AND CREESTID='02'

                  GROUP BY ASOID

                  ) B

                  ON A.ASOID=B.ASOID