Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Unanswered: Selection with null fields

    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

  2. #2
    Join Date
    Jun 2007
    Posts
    2
    Try this
    Select
    field1, field2
    from table1
    left outer join
    table2 where chdate <= &AAAAMMGG
    AND chdate IS NOT NULL

  3. #3
    Join Date
    Apr 2007
    Posts
    51

    Selection with nulls

    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)

  4. #4
    Join Date
    Jun 2007
    Posts
    2
    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

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2007
    Posts
    51
    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)

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    ...
    LEFT OUTER JOIN TABLE3 AS D
    ON ((MAX(CHDATE) WHERE CHDATE <= &DATE) )
    )
    This can never work: you cannot have a WHERE without a SELECT and a FROM.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •