CODEWITHSUNDEEP
oracle-database
Revious
Revious

Reputation: 8146

Oracle: one query to output different where condition result

I've got the following query which output plots 3 rows and 3 columns. For each "Owner" I want to extract on the same line the "PDF" in common and the "PDF" that doesn't exist on the second table.

Do you know other elegant and faster way to show in the same query output the result of different where conditions?

WITH temp
     AS (SELECT T.OWNER,
                (SELECT FLH_PUNTO_EROGAZIONE
                   FROM DUAL
                  WHERE EXISTS
                           (SELECT 1
                              FROM NETATEMP.BACKLOG_NOBILL_STORICO p
                             WHERE p.FLH_PUNTO_EROGAZIONE = t.FLH_PUNTO_EROGAZIONE))
                   "Pdf in comune",
                (SELECT FLH_PUNTO_EROGAZIONE
                   FROM DUAL
                  WHERE NOT EXISTS
                           (SELECT 1
                              FROM NETATEMP.BACKLOG_NOBILL_STORICO p
                             WHERE p.FLH_PUNTO_EROGAZIONE = t.FLH_PUNTO_EROGAZIONE))
                   "Pdf NON in comune"
           FROM NETATEMP.TMP_BACKLOG_NOBILLING2013_UFF t)
  SELECT owner, COUNT (DISTINCT "Pdf in comune") "Pdf in comune", COUNT (DISTINCT "Pdf NON in comune") "Pdf NON in comune"
    FROM temp
GROUP BY OWNER

Upvotes: 0

Views: 319

Answers (2)

Emmanuel
Emmanuel

Reputation: 14209

I used an external join, I hoped I understood your need:

select OWNER, sum(common) "Pdf in comune", count(*) - sum(common) "Pdf NON in comune"
from
(
  select t.OWNER, p.FLH_PUNTO_EROGAZIONE,
         decode(t.FLH_PUNTO_EROGAZIONE, null, 0, 1) common
  from NETATEMP.TMP_BACKLOG_NOBILLING2013_UFF t,
       NETATEMP.BACKLOG_NOBILL_STORICO p
  where p.FLH_PUNTO_EROGAZIONE (+) = t.FLH_PUNTO_EROGAZIONE
)
group by OWNER

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26343

If I understand your existing query, you're trying to show the following:

  • Column 1: Owner
  • Column 2: 1 if the TMP_BACKLOG_NOBILLING2013_UFF.FLH_PUNTO_EROGAZIONE value exists in the BACKLOG_NOBILL_STORICO table or 0 if it doesn't exist
  • Column 3: the opposite of column 2

You can do this a lot more efficiently with an outer join:

SELECT
  T.OWNER,
  MAX(CASE
     WHEN P.FLH_PUNTO_EROGAZIONE IS NOT NULL THEN 1
     ELSE 0 END) AS "Pdf in comune",
  MAX(CASE
     WHEN P.FLH_PUNTO_EROGAZIONE IS NULL THEN 1
     ELSE 0 END) AS "Pdf NON in comune"
FROM TMP_BACKLOG_NOBILLING2013_UFF T
LEFT JOIN BACKLOG_NOBILL_STORICO P
     ON T.FLH_PUNTO_EROGAZIONE = P.FLH_PUNTO_EROGAZIONE
GROUP BY T.OWNER

When you left join, any TMP_BACKLOG_NOBILLING2013_UFF row that doesn't have a match in BACKLOG_NOBILL_STORICO will have NULL values for the BACKLOG_NOBILL_STORICO columns. The CASE and MAX use this to get to the 1 or 0.

Upvotes: 0

Related Questions