3 Replies Latest reply on Apr 15, 2019 1:45 PM by Jorgelina

    How to set double condition for set of fields? APEX

    Nicholas_R

      Hi, I need to set condition for the result of SQL select in APEX.

       

       

      with    report_3 as (

              -- First query 

              SELECT r3.codart, SUM(r3.valnet) AS Sum_v1

              FROM mgmv r3

              WHERE (r3.codcma IN ('X ','Y','Z', 'A', 'B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart NOT LIKE 'FOR%' AND r3.codart NOT LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

              OR(r3.codcma IN ('A') AND r3.codcma NOT IN ('B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart LIKE 'FOR%' AND r3.codart LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

             GROUP BY r3.codart

      )

            , report_4 as (

             --  Second query

              SELECT r4.codart, SUM(r4.qtamov) AS Sum_qt

              FROM mgmv r4

              WHERE (r4.codcma IN ('X ','Y','Z', 'A', 'B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart NOT LIKE 'FOR%' AND r4.codart NOT LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

              OR(r4.codcma IN ('A') AND r4.codcma NOT IN ('B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart LIKE 'FOR%' AND r4.codart LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

              GROUP BY r4.codart

      )

       

      SELECT DISTINCT

            r3.codart

          , Sum_qt

          , Sum_v1

          , TRUNC(case when Sum_qt <> 0 then Sum_v1/Sum_qt+0 end, 5) as Cs_md

        

      FROM

            report_3 r3

            LEFT OUTER JOIN report_4 r4 ON r3.codart = r4.codart

       

       

       

      I need to result FOR1 [...] and DEN1 [...] when they are in A but not in B. But I have the products, with other codes, in A e in B.

        • 1. Re: How to set double condition for set of fields? APEX
          fac586

          Nicholas_R wrote:

           

          Hi, I need to set condition for the result of SQL select in APEX.

           

           

          with report_3 as (

          -- First query

          SELECT r3.codart, SUM(r3.valnet) AS Sum_v1

          FROM mgmv r3

          WHERE (r3.codcma IN ('X ','Y','Z', 'A', 'B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart NOT LIKE 'FOR%' AND r3.codart NOT LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

          OR(r3.codcma IN ('A') AND r3.codcma NOT IN ('B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart LIKE 'FOR%' AND r3.codart LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

          GROUP BY r3.codart

          )

          , report_4 as (

          -- Second query

          SELECT r4.codart, SUM(r4.qtamov) AS Sum_qt

          FROM mgmv r4

          WHERE (r4.codcma IN ('X ','Y','Z', 'A', 'B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart NOT LIKE 'FOR%' AND r4.codart NOT LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

          OR(r4.codcma IN ('A') AND r4.codcma NOT IN ('B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart LIKE 'FOR%' AND r4.codart LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

          GROUP BY r4.codart

          )

           

          SELECT DISTINCT

          r3.codart

          , Sum_qt

          , Sum_v1

          , TRUNC(case when Sum_qt <> 0 then Sum_v1/Sum_qt+0 end, 5) as Cs_md

           

          FROM

          report_3 r3

          LEFT OUTER JOIN report_4 r4 ON r3.codart = r4.codart

           

           

           

          I need to result FOR1 [...] and DEN1 [...] when they are in A but not in B. But I have the products, with other codes, in A e in B.

          Unclear. Please expand and clarify the requirements.

          • 2. Re: How to set double condition for set of fields? APEX
            Nicholas_R

            with report_3 as (

            -- First query

            SELECT r3.codart, SUM(r3.valnet) AS Sum_v1

            FROM mgmv r3

            WHERE (r3.codcma IN ('X ','Y','Z', 'A', 'B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart NOT LIKE 'FOR%' AND r3.codart NOT LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

            OR(r3.codcma IN ('A') AND r3.codcma NOT IN ('B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart LIKE 'FOR%' AND r3.codart LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

            GROUP BY r3.codart

            )

            , report_4 as (

            -- Second query

            SELECT r4.codart, SUM(r4.qtamov) AS Sum_qt

            FROM mgmv r4

            WHERE (r4.codcma IN ('X ','Y','Z', 'A', 'B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart NOT LIKE 'FOR%' AND r4.codart NOT LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

            OR(r4.codcma IN ('A') AND r4.codcma NOT IN ('B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart LIKE 'FOR%' AND r4.codart LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

            GROUP BY r4.codart

            )

             

            SELECT DISTINCT

            r3.codart

            , Sum_qt

            , Sum_v1

            , TRUNC(case when Sum_qt <> 0 then Sum_v1/Sum_qt+0 end, 5) as Cs_md

             

            FROM

            report_3 r3

            LEFT OUTER JOIN report_4 r4 ON r3.codart = r4.codart

            • 3. Re: How to set double condition for set of fields? APEX
              Jorgelina

              Hi...

              I do not think that what is clear, is are you expecting to get from your queries.

              I think that a possible "resulset" from your 1st query, and 2nd query... and what would you like to achieve as the end result would be a lot better so we understand what the requirement is...

              Regards.