Forum Stats

  • 3,751,483 Users
  • 2,250,366 Discussions
  • 7,867,436 Comments

Discussions

How to set double condition for set of fields? APEX

Nicholas_R
Nicholas_R Member Posts: 12
edited Apr 15, 2019 9:45AM in APEX Discussions

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.

Tagged:

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,875 Red Diamond
    edited Apr 15, 2019 5:07AM
    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.codartI 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.

  • Nicholas_R
    Nicholas_R Member Posts: 12
    edited Apr 15, 2019 5:36AM

    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

  • Jorgelina1
    Jorgelina1 Member Posts: 185
    edited Apr 15, 2019 9:45AM

    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.