Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2010
    Posts
    40

    Unanswered: help with sequel query..Urgent please

    Hi All,

    I am running db2 on 9.7 fixpack 5. Have a requirement to select the highest value of the date from the result of a query below

    SELECT A.commid,
    R.repairid,
    A.repair_date,
    R.repair_date
    FROM db2inst1.add_commercial A
    Inner Join db2inst1.add_repair R
    ON A.commid = R.commid

    I rewrote the query as below

    with hdate ( commid,
    repairid,
    repair_date
    ) as (SELECT
    A.commid,
    R.repairid,
    COALESCE( A.repair_date , R.repair_date ) AS repair_date
    from add_commercial A inner join add_repair R on A.commid = R.commid
    )
    select commid, repairid, max(repair_date ) from hdate;

    so if the result of the first query is

    1 2 20120101
    2 2 20120202
    3 3 20120303

    I need results to be displayed as

    3 3 20120303

    Is there a better way of writing this sequel without using the coalesce function and temp table. The development team doesnot want to cache the data in temp table and coalesce is really taking a long time since these tables are big and it is doing a full table scan even though there are indexes on appropriate columns. can somebody help me write one single query for this requirement. Will truly appreciate it

    Thanks

    Smritidb2

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by smritidb2 View Post
    Hi All,

    I am running db2 on 9.7 fixpack 5. Have a requirement to select the highest value of the date from the result of a query below

    SELECT A.commid,
    R.repairid,
    A.repair_date,
    R.repair_date
    FROM db2inst1.add_commercial A
    Inner Join db2inst1.add_repair R
    ON A.commid = R.commid

    I rewrote the query as below

    with hdate ( commid,
    repairid,
    repair_date
    ) as (SELECT
    A.commid,
    R.repairid,
    COALESCE( A.repair_date , R.repair_date ) AS repair_date
    from add_commercial A inner join add_repair R on A.commid = R.commid
    )
    select commid, repairid, max(repair_date ) from hdate;

    so if the result of the first query is

    1 2 20120101
    2 2 20120202
    3 3 20120303

    I need results to be displayed as

    3 3 20120303

    Is there a better way of writing this sequel without using the coalesce function and temp table. The development team doesnot want to cache the data in temp table and coalesce is really taking a long time since these tables are big and it is doing a full table scan even though there are indexes on appropriate columns. can somebody help me write one single query for this requirement. Will truly appreciate it

    Thanks

    Smritidb2
    Code:
    SELECT  A.commid, 
                   R.repairid, 
                   A.repair_date, 
                   R.repair_date
    FROM  db2inst1.add_commercial A
    Join db2inst1.add_repair R
        ON A.commid = R.commid
    order by COALESCE( A.repair_date , R.repair_date ) desc
    fetch first 1 rows only
    optimize for 1 rows
    Since you don't have a where predicate there is nothing weird with db2 choosing a table scan for one of the tables
    --
    Lennart

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT A.commid,
    R.repairid,
    A.repair_date,
    R.repair_date

    FROM db2inst1.add_commercial A
    Inner Join db2inst1.add_repair R
    ON A.commid = R.commid

    I rewrote the query as below

    with hdate ( commid,
    repairid,
    repair_date
    ) as (SELECT
    A.commid,
    R.repairid,
    COALESCE( A.repair_date , R.repair_date ) AS repair_date
    from add_commercial A inner join add_repair R on A.commid = R.commid
    )
    select commid, repairid, max(repair_date ) from hdate;

    so if the result of the first query is

    1 2 20120101
    2 2 20120202
    3 3 20120303

    I need results to be displayed as

    3 3 20120303
    But, I think your first query returned two repair_date(A.repair_date and R.repair_date).
    Which order of repair_date do you want?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In other words,
    do you want which of the followings?
    (a) MAX( COALESCE( A.repair_date , R.repair_date ) )
    (b) GREATEST( MAX(A.repair_date) , MAX(R.repair_date) )
    (c) MAX(A.repair_date)
    (d) MAX(R.repair_date)

    In general,
    (b) >= (a)
    (b) >= (c)
    (b) >= (d)

    If all A.repair_date were NOT NULL,
    (a) = (c)

    If some A.repair_date were NULL,
    (a) >= (c)

    In summary,
    (b) >= (a) >= (c) or (b) >= (a) = (c)
    (b) >= (d)
    Last edited by tonkuma; 04-21-12 at 09:17. Reason: Add "summary".

  5. #5
    Join Date
    Nov 2010
    Posts
    40
    Hi Tonkuma and lelle,

    THanks for your answers. Tonkuma I need the max of the result set out of what is returned from both tables.
    so MAX( COALESCE( A.repair_date , R.repair_date ) ) would be the one

    Lelle how does "optimize for 1 rows" work?

    thanks

    smritidb2

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    smritidb2,

    I was afraid that if you might be misunderstood what I want to say.

    It depends wheather A.repair_date was nullable in the result set of joining two tables.


    If A.repair_date was nullable(in the result set of joining two tables),
    MAX( COALESCE( A.repair_date , R.repair_date ) ) >= MAX(A.repair_date)

    Because, NULL values of A.repair_date were replaced by R.repair_date
    and some of replaced R.repair_date might be larger than MAX(A.repair_date).


    But, if all A.repair_date were NOT NULL(in the result set of joining two tables),
    MAX( COALESCE( A.repair_date , R.repair_date ) ) = MAX(A.repair_date)

    Because, none of A.repair_date were replaced by R.repair_date.
    Then you can use MAX(A.repair_date) instead of MAX( COALESCE(... , ...) ).
    Last edited by tonkuma; 04-21-12 at 10:01.

  7. #7
    Join Date
    Nov 2010
    Posts
    40
    In both tables the repair_date column is nullable. So in that case I should use this
    MAX( COALESCE( A.repair_date , R.repair_date ) ) >= MAX(A.repair_date)
    trying out with this code

    Thanks

    Smritidb2

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please answer again.

    Iwrote:
    (a) MAX( COALESCE( A.repair_date , R.repair_date ) )
    (b) GREATEST( MAX(A.repair_date) , MAX(R.repair_date) )
    (c) MAX(A.repair_date)
    (d) MAX(R.repair_date)

    In general,
    (b) >= (a)
    Are you realy want to
    (a) MAX( COALESCE( A.repair_date , R.repair_date ) )
    rather than
    (b) GREATEST( MAX(A.repair_date) , MAX(R.repair_date) )
    ?

  9. #9
    Join Date
    Nov 2010
    Posts
    40
    Hi tonkuma I am not sure what the "greatest" does but I need the max of the two values returned by the query given to me by Lennart. When I run his query I get max(a.repair_date) and max(r.repair_date) out of which max of the max(r.repair_date) is a higher values. For example below

    Result : 3 3 20120303 20120304

    I need my query to result into

    3 3 20120304

    Can I achieve it by MAX( COALESCE( A.repair_date , R.repair_date ) )

    I checked for nulls in my query by saying where A.repair_date is not null

    Thanks

    Smritidb2

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see this very small example which result was...
    (b) > (a) > (c)
    (b) = (d)

    I think that you want to get (b) rather than (a).
    Is it right?

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      add_commercial
    (commid , repair_date) AS (
    VALUES
      ( 1 , DATE('2012-02-02') )
    , ( 2 , CAST(null AS DATE) )
    , ( 3 , DATE('2012-03-03') )
    , ( 4 , DATE('2012-04-04') )
    , ( 5 , DATE('2012-08-08') )
    , ( 7 , CAST(null AS DATE) )
    )
    , add_repair
    (commid , repair_date) AS (
    VALUES
      ( 1 , DATE('2012-06-06') )
    , ( 2 , DATE('2012-05-05') )
    , ( 3 , DATE('2012-02-02') )
    , ( 4 , CAST(null AS DATE) )
    , ( 6 , DATE('2012-07-07') )
    , ( 7 , CAST(null AS DATE) )
    )
    SELECT MAX( COALESCE( A.repair_date , R.repair_date ) )    AS "(a)"
         , GREATEST( MAX(A.repair_date) , MAX(R.repair_date) ) AS "(b)"
         , MAX(A.repair_date)                                  AS "(c)"
         , MAX(R.repair_date)                                  AS "(d)"
     FROM  add_commercial A
     INNER JOIN
           add_repair     R
      ON   A.commid = R.commid
    ;
    ------------------------------------------------------------------------------
    
    (a)        (b)        (c)        (d)       
    ---------- ---------- ---------- ----------
    2012-05-05 2012-06-06 2012-04-04 2012-06-06
    
      1 record(s) selected.

    Joined result before grouping:
    Code:
    SELECT A.commid
         , A.repair_date AS "A.repair_date"
         , R.repair_date AS "R.repair_date"
     FROM  add_commercial A
     INNER JOIN
           add_repair     R
      ON   A.commid = R.commid
    ;
    ------------------------------------------------------------------------------
    
    COMMID      A.repair_date R.repair_date
    ----------- ------------- -------------
              1 2012-02-02    2012-06-06   
              2 -             2012-05-05   
              3 2012-03-03    2012-02-02   
              4 2012-04-04    -            
              7 -             -            
    
      5 record(s) selected.
    Last edited by tonkuma; 04-21-12 at 12:02. Reason: Add "(b) = (d)"

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If take MAX independently without using join,
    the results might be different.

    Using same data as Example 1, the results were...
    (e) > (b)
    (f) > (b)
    where
    (e) := MAX(A.repair_date) without join
    (f) := MAX(R.repair_date) without join
    (b) := GREATEST( MAX(A.repair_date) , MAX(R.repair_date) ) of joined result


    Example 2:
    Code:
    SELECT MAX(A.repair_date) AS "(e)"
     FROM  add_commercial A
    ;
    ------------------------------------------------------------------------------
    
    (e)       
    ----------
    2012-08-08
    
      1 record(s) selected.
    
    
    ------------------------------------------------------------------------------
    SELECT MAX(R.repair_date) AS "(f)"
     FROM  add_repair     R
    ;
    ------------------------------------------------------------------------------
    
    (f)       
    ----------
    2012-07-07
    
      1 record(s) selected.

  12. #12
    Join Date
    Nov 2010
    Posts
    40
    Hi Tonkuma,

    GREATEST( MAX(A.repair_date) , MAX(R.repair_date) ) AS "(b)"
    This gave me the right answer

    Thank you so much

    truly truly appreciate it

    Smritidb2

  13. #13
    Join Date
    Nov 2010
    Posts
    40
    SELECT A.commid
    , R.repairid
    , GREATEST( MAX(A.repair_date) , MAX(R.repair_date) )
    FROM add_commercial A
    INNER JOIN
    add_repair R
    ON A.commid = R.commid
    ;

    is giving me SQL0119N. I dont want to use the temp table approach

    Thanks
    Smritidb2
    Last edited by smritidb2; 04-21-12 at 13:44.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    GREATEST( MAX(A.repair_date) , MAX(R.repair_date) ) AS "(b)"
    This gave me the right answer
    Then my idea was...
    (Not tested)

    (1) Make 4 indexes.
    Some might already exist.
    (2nd and 4th indexes might be correspond to primary key.)
    Code:
    CREATE INDEX add_commercial__repair_date_desc_commid
    ON add_commercial(repair_date DESC , commid)
    ;
    
    CREATE INDEX add_commercial__commid
    ON add_commercial(commid)
    ;
    
    CREATE INDEX add_repair__repair_date_desc_commid
    ON add_repair(repair_date DESC , commid)
    ;
    
    CREATE INDEX add_repair__commid
    ON add_repair(commid)
    ;

    (2) Then use this query.

    Example 3:
    Note: Without 4 indexes, this might be worse than join.
    Code:
    SELECT GREATEST(A.max_repair_date , R.max_repair_date) AS max_repair_date
     FROM (SELECT MAX(repair_date) AS max_repair_date
            FROM  add_commercial A
            WHERE EXISTS
                 (SELECT 0
                   FROM  add_repair     R
                   WHERE A.commid = R.commid
                 )
          ) A
         ,(SELECT MAX(repair_date) AS max_repair_date
            FROM  add_repair     R
            WHERE EXISTS
                 (SELECT 0
                   FROM  add_commercial A
                   WHERE A.commid = R.commid
                 )
          ) R

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by smritidb2 View Post
    SELECT A.commid
    , R.repairid
    , GREATEST( MAX(A.repair_date) , MAX(R.repair_date) )
    FROM add_commercial A
    INNER JOIN
    add_repair R
    ON A.commid = R.commid
    ;

    is giving me SQL0119N. I dont want to use the temp table approach

    Thanks
    Smritidb2
    Remove A.commid and R.repairid from the result(select list).

Posting Permissions

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