Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433

    Unanswered: @@rowcount for DB2

    DB2 9.5 LUW....

    In Sybase i hava a SP which after checking @@rowcount will return a resultset
    However if @@rowcount =0 Then return some other resultset

    How can be the same be implemented in DB2. I dont want to do a count first .. compare it for rows > 0 and then do If else Condn.
    (As the query is expensive and would require firing same query twice for true condition )

    I even tried a no rows found condn handler, but seems like variable doesnt get populated if no rows are found in a
    cusror with return to client !

    Any other way to handle it.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In Sybase i hava a SP which after checking @@rowcount will return a resultset
    However if @@rowcount =0 Then return some other resultset
    Code:
    SELECT COALESCE(q.col1, d.col1) AS col1
         , COALESCE(q.col2, d.col2) AS col2
         , COALESCE(q.col3, d.col3) AS col3
         .....
      FROM (VALUES (default-col1, default-col2, default-col3, ..... ) )
             AS d(col1, col2, col3, .....)
      LEFT OUTER JOIN
           (your select ) AS q
       ON  0=0
    An example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT empno, firstnme, workdept, edlevel, hiredate
      FROM employee
     WHERE workdept = 'D21';
    ------------------------------------------------------------------------------
    
    EMPNO  FIRSTNME     WORKDEPT EDLEVEL HIREDATE  
    ------ ------------ -------- ------- ----------
    000070 EVA          D21           16 2005-09-30
    000230 JAMES        D21           14 1996-11-21
    000240 SALVATORE    D21           17 2004-12-05
    000250 DANIEL       D21           15 1999-10-30
    000260 SYBIL        D21           16 2005-09-11
    000270 MARIA        D21           15 2006-09-30
    200240 ROBERT       D21           17 2004-12-05
    
      7 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT COALESCE(q.empno,    d.empno   ) AS empno
         , COALESCE(q.firstnme, d.firstnme) AS firstnme
         , COALESCE(q.workdept, d.workdept) AS workdept
         , COALESCE(q.edlevel,  d.edlevel ) AS edlevel
         , COALESCE(q.hiredate, d.hiredate) AS hiredate
      FROM (VALUES ('000000', '', 'XXX', CAST(99 AS SMALLINT), CAST(NULL AS DATE) ) )
             AS d(empno, firstnme, workdept, edlevel, hiredate)
      LEFT OUTER JOIN
           (SELECT empno, firstnme, workdept, edlevel, hiredate
              FROM employee
             WHERE workdept = 'D21'
           )  AS q
       ON  0=0;
    ------------------------------------------------------------------------------
    
    EMPNO  FIRSTNME     WORKDEPT EDLEVEL HIREDATE  
    ------ ------------ -------- ------- ----------
    000070 EVA          D21           16 2005-09-30
    000230 JAMES        D21           14 1996-11-21
    000240 SALVATORE    D21           17 2004-12-05
    000250 DANIEL       D21           15 1999-10-30
    000260 SYBIL        D21           16 2005-09-11
    000270 MARIA        D21           15 2006-09-30
    200240 ROBERT       D21           17 2004-12-05
    
      7 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT COALESCE(q.empno,    d.empno   ) AS empno
         , COALESCE(q.firstnme, d.firstnme) AS firstnme
         , COALESCE(q.workdept, d.workdept) AS workdept
         , COALESCE(q.edlevel,  d.edlevel ) AS edlevel
         , COALESCE(q.hiredate, d.hiredate) AS hiredate
      FROM (VALUES ('000000', '', 'XXX', CAST(99 AS SMALLINT), CAST(NULL AS DATE) ) )
             AS d(empno, firstnme, workdept, edlevel, hiredate)
      LEFT OUTER JOIN
           (SELECT empno, firstnme, workdept, edlevel, hiredate
              FROM employee
             WHERE workdept = 'D22'
           )  AS q
       ON  0=0;
    ------------------------------------------------------------------------------
    
    EMPNO  FIRSTNME     WORKDEPT EDLEVEL HIREDATE  
    ------ ------------ -------- ------- ----------
    000000              XXX           99 -         
    
      1 record(s) selected.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Where does the row count come from? I.e. what is being counted there?

    I think there is no way around executing whichever query you have to decide which result set is to be returned. Sybase must do the same, too. What you can do, however, is to change your condition like this:
    Code:
    IF EXISTS ( SELECT ... ) THEN
        OPEN first_cursor CURSOR;
    ELSE
        OPEN other_cursor CURSOR;
    END IF;
    The additional EXISTS gives the DB2 engine an early-out, i.e. it doesn't have to count all rows - as soon as the first row is found, the result of the condition is known.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    Code:
    WITH a AS (
    first query
    )
    SELECT * FROM a
    UNION ALL
    /* Start of second query */
    SELECT .....
      FROM .....
     WHERE (.....)
    /* End of second query */
       AND NOT EXISTS
           (SELECT * FROM a)
    ;

Posting Permissions

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