Use conditional to check the dates if it does not exist in a column search with the null value

Question

Use conditional to check the dates if it does not exist in a column search with the null value

Navigation

#1 by (0 votes)

0

I have the following tables in Oracle DB 11g

irrigation
----------------
militet_applied, 
close_date

rainfall
---------------
rain_index, 
reading_date

And I'd like to make a select similar to this:

select r.rain_index, 
       i.militet_applied
from irrigation i, rainfall r

But in my where clause I would like to do the following: - If r.reading_date = i.close_date brings me the (i.militet_applied) values, but also r.reading_date is equal to / does not have in column i.close_cycle , it brings null values (i.militet_applied ).

I am counting on you in this doubt.

sql oracle oracle11g

asked by anonymous 04.04.2017 / 09:48

1 answer

Apply Transition effect when changing the source of an Image control Workbench for PostgreSQL

score 0 · Accepted Answer

JosefoSad, although the question is confusing, following the lines you said, one option is this:

SELECT 
  R.RAIN_INDEX, 
  CASE 
    WHEN R.READING_DATE = I.CLOSE_CYCLE OR R.READING_DATE NOT IN (SELECT I2.CLOSE_CYCLE FROM IRRIGATION I2) THEN NULL
    ELSE I.MILITET_APPLIED END AS MILITET_APPLIED
FROM 
  RAINFALL R
  JOIN IRRIGATION I ON R.READING_DATE = I.CLOSE_DATE;

If you want the MILITET_APPLIED column to be null only when there is no match in the join (R.READING_DATE = I.CLOSE_DATE), use LEFT JOIN:

SELECT 
  R.RAIN_INDEX, 
  I.MILITET_APPLIED
FROM 
  RAINFALL R
  LEFT JOIN IRRIGATION I ON R.READING_DATE = I.CLOSE_DATE;