Discussions
Categories
- 195.9K All Categories
- 2.1K Data
- 208 Big Data Appliance
- 1.9K Data Science
- 447.2K Databases
- 220.8K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 23 Multilingual Engine
- 516 MySQL Community Space
- 463 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 461 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.8K SQL & PL/SQL
- 20.9K SQL Developer
- 292.4K Development
- 7 Developer Projects
- 121 Programming Languages
- 289.2K Development Tools
- 94 DevOps
- 3K QA/Testing
- 645.4K Java
- 22 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.8K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 14 Java Essentials
- 142 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 197 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 221 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
How to set double condition for set of fields? APEX
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.
Answers
-
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.
-
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
-
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.
