Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011

    Unanswered: Need help with SQL query

    Consider the following table

    TABLE test
    col A
    col B
    col C
    col D
    col E
    col F

    Currently I have two queries (in a very simplified form)

    select *
    from test
    where A = :a
    and B = :b
    and D > :date

    If row not found then
    select *
    from test t1, test t2
    where t1.A = :a
    and t1.c = t2.e
    and t2.B = :b
    and t1.D > :date
    and t2.D > :date

    Is it possible to combine the SQL's and also is there an efficient way to do this.

    Thank you

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    Do the result set of both queries have the same sequence of columns and data types for the columns? If not, you'll have to align both. Then you can use a UNION ALL like this, for example:
    <select 1>
    <select 2> WHERE ... AND NOT EXISTS ( <select 1> )
    You can also push the <select 1> into a common table expression if you worry that DB2 may not detect the identical query block and just evaluate it once (or if the query block has some non-deterministic predicates).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    I tried to reduce the number of references of table "test", mainly because of my fun/cuoriosity.

    Two conditions can be added to query 2, because it is executed when query 1 returned no row.
    query 2:
    SELECT *
     FROM  test t1
         , test t2
     WHERE t1.a =  :a
       AND t1.d >  :date
       AND t1.b <> :b
       AND t2.a <> :a
       AND t2.e =  t1.c
       AND t2.b =  :b
       AND t2.d >  :date

    This example might be equivalent to Stolze's example.

    Not tested. I don't know if this is more efficient than Stolze's example.
    SELECT a , b , c , d , e , f
     FROM (SELECT COALESCE(t2.a , t1.a) AS a
                , COALESCE(t2.b , t1.b) AS b
                , COALESCE(t2.c , t1.c) AS c
                , COALESCE(t2.d , t1.d) AS d
                , COALESCE(t2.e , t1.e) AS e
                , COALESCE(t2.f , t1.f) AS f
                , RANK()
                     OVER( ORDER BY CASE t1.b
                                    WHEN :b THEN 1
                                    ELSE         2
                         ) AS rank_b
            FROM  test t1
            LEFT  OUTER JOIN
                  test t2
             ON   t1.b <> :b
              AND t2.a <> :a
              AND t2.e =  t1.c
              AND t2.b =  :b
              AND t2.d >  :date
            WHERE t1.a =  :a
              AND t1.d >  :date
              AND :b IN (t1.b , t2.b)
           /*     :b IN (t1.b , t2.b) may be replaced by
             (    t1.b = :b
              OR  t2.b IS NOT NULL
          ) s
     WHERE rank_b = 1
    If there were some constraints/restrictions in data, the query might be simplified.

    For example: if both of query 1 and query 2 retuned at most one row,
    the subquery and a RANK function might be unnecessary and be replaced by ORDER BY t1.b FETCH FIRST 1 ROW ONLY.

Posting Permissions

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