Selection with null fields

annamaria · #1 (**permalink**) 06-10-07, 04:03

In this query:

Select
field1, field2
from table1
left outer join
table2 where chdate = &AAAAMMGG
&AAAAMMGG means that it is a variable. It is a date I type whenever I run
the query.
Now I want to change that data as follows:

I want to select the most recent among: the date I indicate (if it is not null
in table2); if it is null I want to select the day before, and if even this is null
I want to select two days before, i.e.:
20070531 or 20070530 or 20070529 (choose the most recent that is not null). Maybe we could use the command MAX?

Thank you.
Anna - Verona (Italy)

Thank you.

Anna - Verona

a027412 · #2 (**permalink**) 06-10-07, 05:58

Try this
Select
field1, field2
from table1
left outer join
table2 where chdate <= &AAAAMMGG
AND chdate IS NOT NULL

annamaria · #3 (**permalink**) 06-10-07, 06:39

Quote:

Originally Posted by a027412

Try this
Select
field1, field2
from table1
left outer join
table2 where chdate <= &AAAAMMGG
AND chdate IS NOT NULL

With this formula, it selects all the dates preceeding or equal to AAAAMMGG that are not null. I want the query to select only one date: AAAMMGG or (if AAAAMMGG is null) the last date before AAAAMMGG that is not null.

Thank you.
Anna - Verona (Italy)

a027412 · #4 (**permalink**) 06-10-07, 07:04

Select
field1, field2
from table1
left outer join
table2 where chdate <= &AAAAMMGG
AND chdate IS NOT NULL
ORDER BY chdate DESC
FETCH FIRST 1 ROWS ONLY

Peter.Vanroose · #5 (**permalink**) 06-10-07, 14:17

Quote:

Originally Posted by annamaria

I want to select the most recent among: the date I indicate (if it is not null
in table2); if it is null I want to select the day before, and if even this is null
I want to select two days before, i.e.:
20070531 or 20070530 or 20070529 (choose the most recent that is not null). Maybe we could use the command MAX?

Try this:

Code:

SELECT <whatever> FROM <wherever>
WHERE chdate = (SELECT MAX(chdate) FROM <wherever>
                WHERE chdate <= &AAAAMMGG)

Only in the case when there would be no chdate before &AAAAMMGG, no rows will be shown. In all other cases, all rows with the most recent date not later than &AAAAMMGG will be shown. (The "FETCH FIRST ROW ONLY" solution will just pick one amongst those.)

annamaria · #6 (**permalink**) 06-11-07, 03:27

Quote:

Originally Posted by Peter.Vanroose

Try this:

Code:

SELECT <whatever> FROM <wherever>
WHERE chdate = (SELECT MAX(chdate) FROM <wherever>
                WHERE chdate <= &AAAAMMGG)

Only in the case when there would be no chdate before &AAAAMMGG, no rows will be shown. In all other cases, all rows with the most recent date not later than &AAAAMMGG will be shown. (The "FETCH FIRST ROW ONLY" solution will just pick one amongst those.)

Excuse me, Peter, I'm trying to apply your formula to my query with nested tables but I can't find the right commands:

SELECT ......
FROM
( SELECT * FROM TABLE1
WHERE ISCODE = 785
AND &DATE BETWEEN SECISS AND SECMAT
) as a
LEFT OUTER JOIN TABLE 2 AS C
ON ( (DATEB < &DATE AND DEND > &DATE) )
LEFT OUTER JOIN TABLE3 AS D
ON ((MAX(CHDATE) WHERE CHDATE <= &DATE) )
) as aaa) as bbb) as ccc) AS DDD) AS EEE) AS FFF) AS GGG) AS HHH

Thank you.

Anna - Verona (Italy)