Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Unanswered: Remove null rows from table

    Hi,
    I have a very strange problem
    I have a table say a that has values

    table a

    startdate enddate period
    27/03/2008 22/04/2008 1
    10/05/2008 01/06/2008 2
    23/05/2008 05/06/2008 3

    I have another table b from which a pick up code and year

    YEAR PERIOD CODE START_DATE END_DATE
    2008 1 a 26/03/2008 22/04/2008
    2008 1 b 09/04/2008 06/05/2008
    2008 2 a 23/04/2008 20/05/2008
    2008 2 b 07/05/2008 03/06/2008
    2008 3 a 21/05/2008 17/06/2008
    2008 3 b 04/06/2008 01/07/2008

    The problem is that when i try to join the above offercodes to get the respective code and year for table 1
    I get something like this
    YEAR CODE
    --------------- ---------------------
    2008 a
    - -
    - -
    2008 b
    2008 a
    - -

    I need to remove the NULL rows.I am using CASE expressions for getting the values.
    Can anybody help?
    I cannot share the query so if anybody could help me out without seeing the query it would be great!!

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    You have to think, just a little beat:

    Code:
    select date('10/05/2008'), date('01/06/2008')
    from sysibm.sysdummy1
    Result:
    2008-10-05 2008-01-06
    we have Start period > End period

    Lenny

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed lifzgud is using local date format dd/mm/yyyy.
    (Searching by "Date and time formats by territory code" in Information Center, I found some territory is using that format.)
    But, I want to recommend to use ISO format to share with peoples in other countries.

    Anyway, this would remove NULL rows from your result.
    Code:
    SELECT *
      FROM (your query
            /* I cannot share the query */
           ) q
     WHERE year IS NOT NULL
       OR  code IS NOT NULL

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I could reproduce your result with NULL rows by using CASE expressions, like this:

    Changed: data type of(start_date and end_date) in sample data
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /**************************************************
     Start of sample data
    **************************************************/
     a(start_date, end_date, period) AS (
    SELECT DATE(start_date)
         , DATE(end_date)
         , period
    FROM (
    VALUES
     ('27.03.2008', '22.04.2008', 1)
    ,('10.05.2008', '01.06.2008', 2)
    ,('23.05.2008', '05.06.2008', 3)
    ) q(start_date, end_date, period)
    )
    ,b(year, period, code, start_date, end_date) AS (
    SELECT year, period, code
         , DATE(start_date)
         , DATE(end_date)
    FROM (
    VALUES
     (2008, 1, 'a', '26.03.2008', '22.04.2008')
    ,(2008, 1, 'b', '09.04.2008', '06.05.2008')
    ,(2008, 2, 'a', '23.04.2008', '20.05.2008')
    ,(2008, 2, 'b', '07.05.2008', '03.06.2008')
    ,(2008, 3, 'a', '21.05.2008', '17.06.2008')
    ,(2008, 3, 'b', '04.06.2008', '01.07.2008')
    ) q(year, period, code, start_date, end_date)
    )
    /**************************************************
     End of sample data
    **************************************************/
    SELECT CASE
           WHEN a.start_date >= b.start_date
            AND a.end_date   <= b.end_date   THEN
                b.year
           END AS year
         , CASE
           WHEN a.start_date >= b.start_date
            AND a.end_date   <= b.end_date   THEN
                b.code
           END AS code
      FROM a
      INNER JOIN
           b
       ON  b.period = a.period
     ORDER BY
           a.period
         , b.code;
    ------------------------------------------------------------------------------
    
    YEAR        CODE
    ----------- ----
           2008 a   
              - -   
              - -   
           2008 b   
           2008 a   
              - -   
    
      6 record(s) selected.
    But, I thought that it is too complex.
    Join with additional conditions would result without NULL rows, like this:
    Code:
    /* same data as previous example */
    SELECT b.year
         , b.code
      FROM a
      INNER JOIN
           b
       ON  b.period = a.period
       AND a.start_date >= b.start_date
       AND a.end_date   <= b.end_date
     ORDER BY
           a.period
         , b.code;
    ------------------------------------------------------------------------------
    
    YEAR        CODE
    ----------- ----
           2008 a   
           2008 b   
           2008 a   
    
      3 record(s) selected.
    Last edited by tonkuma; 11-11-09 at 13:31.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    I guessed lifzgud is using local date format dd/mm/yyyy.
    (Searching by "Date and time formats by territory code" in Information Center, I found some territory is using that format.)
    But, I want to recommend to use ISO format to share with peoples in other countries.

    Anyway, this would remove NULL rows from your result.
    Code:
    SELECT *
      FROM (your query
            /* I cannot share the query */
           ) q
     WHERE year IS NOT NULL
       OR  code IS NOT NULL
    How I understand he's mixed formats.

    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The format of the values of start_date/end_date are
    aa/bb/cccc
    where 01 <= aa <= 27, 03 <= bb <= 07 and cccc = 2008.

    That is the reason that I guessed the format of columns may be "dd/mm/yyyy".

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by tonkuma View Post
    The format of the values of start_date/end_date are
    aa/bb/cccc
    where 01 <= aa <= 27, 03 <= bb <= 07 and cccc = 2008.

    That is the reason that I guessed the format of columns may be "dd/mm/yyyy".
    Try to run this...

    Code:
    select date('10/05/2008'), date('01/06/2008'),
    date('27/03/2008'), date('22/04/2008'),
    date('23/05/2008'), date('05/06/2008')
    from sysibm.sysdummy1
    ...compare to ...

    Code:
    select date('10/05/2008'), date('01/06/2008') 
    from sysibm.sysdummy1
    ...and you will understand he's working with date as with string.

    That's why it take a place.

    Lenny

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I already wrote:
    I guessed lifzgud is using local date format dd/mm/yyyy.
    (Searching by "Date and time formats by territory code" in Information Center, I found some territory is using that format.)
    And I changed sample data '10/05/2008' and '01/06/2008' to '10.05.2008' and '01.06.2008' in my queries.
    So, they were interpreted as May 10th, 2008 and June 1st, 2008.

    If you want to execute
    Code:
    select date('10/05/2008'), date('01/06/2008'),
    date('27/03/2008'), date('22/04/2008'),
    date('23/05/2008'), date('05/06/2008')
    from sysibm.sysdummy1
    without error,
    you should create database USING CODESET codeset TERRITORY territory which support date format "dd/mm/yyyy".
    France and Germany would be examples of such territory.

    Please see:
    "Date and time formats by territory code"
    and
    "Supported territory codes and code pages"

    in
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    tonkuma, we do not discuss about your query, which is correct, but about original query which is not such good as yours

    Lenny

Posting Permissions

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