How to return empty value in column?

Asked

Viewed 59 times

0

Basically the logic should be the following. Return the Acs (aircraft) that has the component RCF6709 installed (case 1) or not installed (case 0). See:

  1. Retrieve active aircraft on AC_Master;

  2. Whereas there should be 1 pn RCF6709 by aircraft, I made the case to tell 1 (OK) or 0 (Is not installed);

  3. I believe the error is on the line I put WHERE A.PN IN ('RCF6709'), because today the query is returning everything that is installed (case 1). But when I extract the Bruno report, I identify that there are 5 Acs (aircraft) that do not have this pn installed. 

SELECT B.AC,
A.PN,
COUNT (DISTINCT (A.AC)) AS "Nº OF CONTROLS",
B.AC_TYPE ||'-'|| B.AC_SERIES AS "TYPE/SERIES",
(CASE WHEN COUNT (DISTINCT (A.AC)) = 1 THEN 1 ELSE 0 END) AS "CHECK"

FROM ODB.PLANNING A
LEFT JOIN ODB.AC_MASTER B
ON A.AC = B.AC

WHERE A.PN IN ('RCF6709')

AND B.AC_TYPE ||'-'|| B.AC_SERIES = 'ATR72-600'

AND B.STATUS = 'ACTIVE'

GROUP BY B.AC,
B.AC_TYPE ||'-'|| B.AC_SERIES,
A.PN
sql

  • Without knowing the structure complicates but I think you should reverse your order of joins FROM ODB.AC_MASTER B LEFT JOIN ODB.PLANNING

1 answer

0


I managed to solve, taking out WHERE A.PN IN ('RCF6709') and including it in LEFT JOIN as :

LEFT JOIN ODB.PLANNING A ON A.AC = B.AC AND A.PN = 'RCF6709

SELECT B.AC,
  A.PN,
  COUNT (DISTINCT A.AC) AS "Nº OF CONTROLS",
  B.AC_TYPE ||'-'|| B.AC_SERIES AS "TYPE/SERIES",
  CASE WHEN COUNT (DISTINCT A.AC) = 1 THEN 1 ELSE 0 END AS "CHECK"
FROM ODB.AC_MASTER B
LEFT JOIN ODB.PLANNING A
ON A.AC = B.AC
AND A.PN = 'RCF6709'
WHERE B.AC_TYPE ||'-'|| B.AC_SERIES = 'ATR72-600'
AND B.STATUS = 'ACTIVE'
GROUP BY B.AC,
  B.AC_TYPE ||'-'|| B.AC_SERIES,
  A.PN

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.