| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
06-15-09, 07:30
|
|
Registered User
|
|
Join Date: Jul 2006
Location: Pune , India
Posts: 433
|
|
|
@@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
|
|
06-15-09, 09:13
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
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.
|
|
06-15-09, 09:36
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
|
|
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
|
|
06-15-09, 10:48
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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)
;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
@@rowcount for DB2
