Unexpected query results - UDB 7.1 OS/2 
Author Message
 Unexpected query results - UDB 7.1 OS/2

I just finished installing FixPak WR21241 and am still getting
unexpected results from the following queries that were run from a file.
The returned row for the first three queries conforms to the data
content but the last two queries do not return what was expected. Each
of the tables contains one row. The fourth query returned a null where
the value '000' was expected and the fifth query didn't return anything!
Any ideas what is happening?

db2level output:
DB21085I  Instance "DB2" uses DB2 code release "SQL07010" with level
identifier "02030105" and informational tokens "DB2 v7.1.0.28",
"n010216" and "WR21241".

QUERY 1:
select a.upsessid from userpriv a where a.upsessid = 'test'

UPSESSID
-------------------------------
test

  1 record(s) selected.

QUERY 2:
select a.upsessid, b.sctykey from userpriv a left outer join usernsig b
on b.sessid = a.upsessid where a.upsessid = 'test' and b.sctykey =
'aaaaaaaa'

UPSESSID                        SCTYKEY
------------------------------- --------
test                            aaaaaaaa

  1 record(s) selected.

QUERY 3:
select a.upsessid, (select coalesce (sctykey,'000') as sctykey from
usernsig b where b.sessid = a.upsessid and b.sctykey = 'aaaaaaaa' ) from
userpriv a where a.upsessid = 'test'

UPSESSID                        SCTYKEY
------------------------------- --------
test                            aaaaaaaa

  1 record(s) selected.

QUERY 4:
select a.upsessid, (select coalesce (sctykey,'000') as sctykey from
usernsig b where b.sessid = a.upsessid and b.sctykey = 'bbbbbbbb' ) from
userpriv a where a.upsessid = 'test'

UPSESSID                        SCTYKEY
------------------------------- --------
test                            -

  1 record(s) selected.

QUERY 5:
select a.upsessid, b.sctykey from userpriv a left outer join usernsig b
on b.sessid = a.upsessid where a.upsessid = 'test' and b.sctykey =
'bbbbbbbb'

UPSESSID                        SCTYKEY
------------------------------- --------

  0 record(s) selected.



Tue, 09 Sep 2003 14:14:18 GMT
 Unexpected query results - UDB 7.1 OS/2

Quote:

> I just finished installing FixPak WR21241 and am still getting
> unexpected results from the following queries that were run from a file.
> The returned row for the first three queries conforms to the data
> content but the last two queries do not return what was expected. Each
> of the tables contains one row. The fourth query returned a null where
> the value '000' was expected and the fifth query didn't return anything!
> Any ideas what is happening?
[...]

> QUERY 4:
> select a.upsessid, (select coalesce (sctykey,'000') as sctykey from
> usernsig b where b.sessid = a.upsessid and b.sctykey = 'bbbbbbbb' ) from
> userpriv a where a.upsessid = 'test'

> UPSESSID                        SCTYKEY
> ------------------------------- --------
> test                            -

>   1 record(s) selected.

> QUERY 5:
> select a.upsessid, b.sctykey from userpriv a left outer join usernsig b
> on b.sessid = a.upsessid where a.upsessid = 'test' and b.sctykey =
> 'bbbbbbbb'

> UPSESSID                        SCTYKEY
> ------------------------------- --------

>   0 record(s) selected.

Do you have a row in the table "usernsig" whose 'sctykey' value is equal to
'bbbbbbbb'? (You don't show anything like that in the queries 1 thru 3.) If
not, the results returned are correct.

The null value in query 4 comes from the following subselect:
SELECT COALESCE ( sctykey, '000' )
FROM   usernsig
WHERE  ... AND styckey = 'bbbbbbbb'

So if there is no row fulfilling the where clause, the overall result of
that subselect must be the null value. I assume what you rather want to do
is the following:

SELECT a.upsessid, COALESCE ( ( SELECT sctykey
                                FROM   usernsig b
                                WHERE  b.sessid = a.upsessid AND
                                       b.sctykey = 'bbbbbbbb' ), '000' )
FROM   userpriv a
WHERE  a.upsessid = 'test'

(Note the different placement of the COALESCE function.) Also, you have to
be careful that your subselect does not return more than 1 row. If it does,
you will get a runtime error.

And query 5 is doing a LEFT outer join. That means w/o a row in sctykey that
contains 'bbbbbbbb', no row from that table will go into the join due to the
where clause. Then it doesn't matter what you have on the table on the
left-hand side of the join (whether matching rows exist or not), the result
has no rows. Maybe you want to do a RIGHT outer join there?

--
Knut Stolze
DB2 UDB Spatial Extender
IBM Silicon Valley Lab



Tue, 09 Sep 2003 15:50:07 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. View access from COBOL batch program in DB2 UDB Version 7.1 for Z/OS

2. Error -902 when migrating from UDB 5.0 (OS/2) to 7.1 (Win2K)

3. UDB 7.1 on OS/390 vs UDB 7.1 on other platforms?

4. Best way to query UDB 7.1 from SQL Server 2000

5. UDB v5.2 vs UDB OS/390 v6

6. Segmentation Fault with db2 library in Red Hat Linux 7.3 with UDB 7.1 Enterprise

7. DB2 UDB 7.1 fp3 AIX 4.3.3 db2 command processor and crontab

8. Question on DB2 UDB 7.1 Performance Tuning Guide Redbook companion diskette

9. Select from Linux UDB 7.1 fails using iSeries interactive SQL

10. Is DB2 UDB 7.1 for W2k fixpack available?

11. Multiple versions of UDB Administration client (6.1 and 7.1 client)


 
Powered by phpBB® Forum Software