SQL Returns no results

Jane Leyba

SQL Returns no results
A fellow DBA is trying to understand why the following SQL returns zero
results:

TREND_STATUS table contains the following data:

CY PRD CMP
2003 03 611
2003 02 611
2003 03 612
2003 02 613

SELECT
CASE WHEN A.CMP IS NULL THEN B.CMP
ELSE 0
END AS CMP
FROM
TREND_STATUS A
FULL OUTER JOIN
TREND_STATUS B
ON
A.CMP = B.CMP
WHERE
A.CY = '2003' AND A.PRD = '03'
AND
B.CY = '2003' AND B.PRD = '02' ;

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bill L. Blaney

Re: SQL Returns no results
(in response to Suresh Sane)
None of the rows can ever satisfy the WHERE clause. Did you mean the
following?
...
WHERE
A.CY = '2003' AND A.PRD = '03'
OR
B.CY = '2003' AND B.PRD = '02' ;

Bill Blaney
(443) 540-4407


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jane Leyba
Sent: Friday, December 19, 2003 11:43 AM
To: [login to unmask email]
Subject: SQL Returns no results


A fellow DBA is trying to understand why the following SQL returns zero
results:

TREND_STATUS table contains the following data:

CY PRD CMP
2003 03 611
2003 02 611
2003 03 612
2003 02 613

SELECT
CASE WHEN A.CMP IS NULL THEN B.CMP
ELSE 0
END AS CMP
FROM
TREND_STATUS A
FULL OUTER JOIN
TREND_STATUS B
ON
A.CMP = B.CMP
WHERE
A.CY = '2003' AND A.PRD = '03'
AND
B.CY = '2003' AND B.PRD = '02' ;

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: SQL Returns no results
(in response to Jane Leyba)
Jane,

The WHERE predicates make this an inner join and obviously PRD cannot be 02
and 03 at the same time! Use a Nested Table Expression.

Thanks,
Suresh


>From: Jane Leyba <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: SQL Returns no results
>Date: Fri, 19 Dec 2003 10:42:35 -0600
>
>A fellow DBA is trying to understand why the following SQL returns zero
>results:
>
>TREND_STATUS table contains the following data:
>
>CY PRD CMP
>2003 03 611
>2003 02 611
>2003 03 612
>2003 02 613
>
>SELECT
> CASE WHEN A.CMP IS NULL THEN B.CMP
> ELSE 0
> END AS CMP
>FROM
> TREND_STATUS A
>FULL OUTER JOIN
> TREND_STATUS B
>ON
> A.CMP = B.CMP
>WHERE
> A.CY = '2003' AND A.PRD = '03'
>AND
> B.CY = '2003' AND B.PRD = '02' ;
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Grab our best dial-up Internet access offer: 6 months @$9.95/month.
http://join.msn.com/?page=dept/dialup

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Vaughan

Re: SQL Returns no results
(in response to Bill L. Blaney)
Actually PRD can be 02 and 03 at the same time since they are on two different tables - note the "A" and "B" (ok, technically the same table, but DB2 is treating them as different tables since it is joined to itself). However, it's true that this is being changed to an inner join due to the "where".
When you say it "returns zero results", do you really mean no rows returned (as is +100 SQLCODE) or do you mean it returns rows that all contain "0" for CMP? When I look at the query it appears that it should return rows (1 row actually since there is a "02" and "03" row for "611"), but the actual value returned wlll be "0" due to the case statement. Since this is being converted to an inner join, A.CMP will never contain nulls so the case statement will always come out to "0".

Thanks,
Mike.
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Suresh Sane
Sent: Friday, December 19, 2003 11:14 AM
To: [login to unmask email]
Subject: Re: SQL Returns no results


Jane,

The WHERE predicates make this an inner join and obviously PRD cannot be 02
and 03 at the same time! Use a Nested Table Expression.

Thanks,
Suresh


>From: Jane Leyba <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: SQL Returns no results
>Date: Fri, 19 Dec 2003 10:42:35 -0600
>
>A fellow DBA is trying to understand why the following SQL returns zero
>results:
>
>TREND_STATUS table contains the following data:
>
>CY PRD CMP
>2003 03 611
>2003 02 611
>2003 03 612
>2003 02 613
>
>SELECT
> CASE WHEN A.CMP IS NULL THEN B.CMP
> ELSE 0
> END AS CMP
>FROM
> TREND_STATUS A
>FULL OUTER JOIN
> TREND_STATUS B
>ON
> A.CMP = B.CMP
>WHERE
> A.CY = '2003' AND A.PRD = '03'
>AND
> B.CY = '2003' AND B.PRD = '02' ;
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Grab our best dial-up Internet access offer: 6 months @$9.95/month.
http://join.msn.com/?page=dept/dialup

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tony Provenzola

Re: SQL Returns no results
(in response to Mike Vaughan)
The problem is not with the logic. It's doing a JOIN. Both A.PRD and B.PRD would coexist, and the following row should satisfy the selection criteria:

A.CY A.PRD A.CMP B.CY B.PRD B.CMP
2003 03 611 2003 02 611

Try the same query using SYSTABLES:

SELECT
CASE WHEN A.CREATOR IS NULL THEN B.CREATOR
ELSE '0'
END AS CREATOR
FROM
SYSIBM.SYSTABLES A
FULL OUTER JOIN
SYSIBM.SYSTABLES B
ON
A.CREATOR = B.CREATOR
WHERE
A.TSNAME = 'SYSDBASE' AND A.NAME = 'SYSINDEXES'
AND
B.TSNAME = 'SYSDBASE' AND B.NAME = 'SYSKEYS' ;

Tony Provenzola
Nike Database Services
Venturi Technology Partners, Consulting
Phone * 503-532-0772
Fax * 503-532-3223
Email * [login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Blaney, Bill L.
Sent: Friday, December 19, 2003 9:08 AM
To: [login to unmask email]
Subject: Re: SQL Returns no results


None of the rows can ever satisfy the WHERE clause. Did you mean the
following?
...
WHERE
A.CY = '2003' AND A.PRD = '03'
OR
B.CY = '2003' AND B.PRD = '02' ;

Bill Blaney
(443) 540-4407


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jane Leyba
Sent: Friday, December 19, 2003 11:43 AM
To: [login to unmask email]
Subject: SQL Returns no results


A fellow DBA is trying to understand why the following SQL returns zero
results:

TREND_STATUS table contains the following data:

CY PRD CMP
2003 03 611
2003 02 611
2003 03 612
2003 02 613

SELECT
CASE WHEN A.CMP IS NULL THEN B.CMP
ELSE 0
END AS CMP
FROM
TREND_STATUS A
FULL OUTER JOIN
TREND_STATUS B
ON
A.CMP = B.CMP
WHERE
A.CY = '2003' AND A.PRD = '03'
AND
B.CY = '2003' AND B.PRD = '02' ;

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: SQL Returns no results
(in response to Tony Provenzola)
Jane,

I have tested your provided full outer join statement.
It produced result exactly like Mike Vaughn said.
It produced 1 result: the join of the first row and the
second row of the table.
The displayed 1 row output is: "0".(because CMP "611"
is not "null".

Agus Kwee
Themis Training
http://www.themisinc.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: SQL Returns no results
(in response to Agus Kwee)
Jayne,
You may have already gotten to this, but, I think, the SQL should be written as:
SELECT
CASE WHEN A.CMP IS NULL THEN B.CMP
ELSE 0
END AS CMP
FROM
TREND_STATUS A
FULL OUTER JOIN
TREND_STATUS B
ON
A.CMP = B.CMP
AND
B.CY = '2003' AND B.PRD = '02' ;
WHERE
A.CY = '2003' AND A.PRD = '03'



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jane Leyba
Sent: Friday, December 19, 2003 11:43 AM
To: [login to unmask email]
Subject: SQL Returns no results


A fellow DBA is trying to understand why the following SQL returns zero
results:

TREND_STATUS table contains the following data:

CY PRD CMP
2003 03 611
2003 02 611
2003 03 612
2003 02 613

SELECT
CASE WHEN A.CMP IS NULL THEN B.CMP
ELSE 0
END AS CMP
FROM
TREND_STATUS A
FULL OUTER JOIN
TREND_STATUS B
ON
A.CMP = B.CMP
WHERE
A.CY = '2003' AND A.PRD = '03'
AND
B.CY = '2003' AND B.PRD = '02' ;

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idugorg/index.cfm



Dave Nance
First Health Services, Corp.
(804)527-6841

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: SQL Returns no results
(in response to Dave Nance)
In my opinion, this SQL should return one row, the combination of the
first two rows. The other possible combinations are discarded due to the
WHERE clauses; the NULL values coming out of the outer join do not qualify.
In fact, the WHERE clauses make this join an inner join.

But for the moment I can see no reason why the combination of the first
two rows is not returned. Could you please tell us the data types of
the columns? Maybe there could be a problem (VARCHARs with trailing blanks or
something of that kind).

Well, another problem: the CASE expression

CASE WHEN A.CMP IS NULL THEN B.CMP
ELSE 0
END AS CMP

returns 0, if A.CMP is NOT NULL, which is true in this
case. So you should get one row with value 0, like this:

CMP
------
0

Maybe the CASE expression should be replaced by

COALESCE (A.CMP, B.CMP, 0)

that is, return the first of the three arguments,
which is not null.

In this case, you will get one row with CMP = 611 (if there are no
hidden errors which I don't see in the moment), like this:

CMP
------
611

Regards

Bernd



Am Fre, 19 Dez 2003 schrieben Sie:
> A fellow DBA is trying to understand why the following SQL returns zero
> results:
>
> TREND_STATUS table contains the following data:
>
> CY PRD CMP
> 2003 03 611
> 2003 02 611
> 2003 03 612
> 2003 02 613
>
> SELECT
> CASE WHEN A.CMP IS NULL THEN B.CMP
> ELSE 0
> END AS CMP
> FROM
> TREND_STATUS A
> FULL OUTER JOIN
> TREND_STATUS B
> ON
> A.CMP = B.CMP
> WHERE
> A.CY = '2003' AND A.PRD = '03'
> AND
> B.CY = '2003' AND B.PRD = '02' ;
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: SQL Returns no results
(in response to Bernd Oppolzer)
Hi,

The reason that no rows qualify is as follows:

The on clause (without the where clause) would have the following result (as Bernd says the same as an inner join).

Row A.CY A.PRD A.CMP B.CY B.PRD B.CMP
1 2003 03 611 2003 03 611
2 2003 03 611 2003 03 612
3 2003 02 611 2003 02 611
4 2003 02 611 2003 02 613
5 2003 03 612 2003 03 611
6 2003 03 612 2003 03 612
7 2003 02 613 2003 02 611
8 2003 02 613 2003 02 613

Now the first part of the where clause "A.CY = '2003' AND A.PRD = '03'" eliminates rows 3,4,7&8
and the seccond part of the where clause "B.CY = '2003' AND B.PRD = '02'" eliminates rows 1,2,5&6. Since they were ANDed togeather, that eliminates them all.

Hope this helps,

Tink

Bernd Oppolzer <[login to unmask email]> wrote:
In my opinion, this SQL should return one row, the combination of the
first two rows. The other possible combinations are discarded due to the
WHERE clauses; the NULL values coming out of the outer join do not qualify.
In fact, the WHERE clauses make this join an inner join.

But for the moment I can see no reason why the combination of the first
two rows is not returned. Could you please tell us the data types of
the columns? Maybe there could be a problem (VARCHARs with trailing blanks or
something of that kind).

Well, another problem: the CASE expression

CASE WHEN A.CMP IS NULL THEN B.CMP
ELSE 0
END AS CMP

returns 0, if A.CMP is NOT NULL, which is true in this
case. So you should get one row with value 0, like this:

CMP
------
0

Maybe the CASE expression should be replaced by

COALESCE (A.CMP, B.CMP, 0)

that is, return the first of the three arguments,
which is not null.

In this case, you will get one row with CMP = 611 (if there are no
hidden errors which I don't see in the moment), like this:

CMP
------
611

Regards

Bernd



Am Fre, 19 Dez 2003 schrieben Sie:
> A fellow DBA is trying to understand why the following SQL returns zero
> results:
>
> TREND_STATUS table contains the following data:
>
> CY PRD CMP
> 2003 03 611
> 2003 02 611
> 2003 03 612
> 2003 02 613
>
> SELECT
> CASE WHEN A.CMP IS NULL THEN B.CMP
> ELSE 0
> END AS CMP
> FROM
> TREND_STATUS A
> FULL OUTER JOIN
> TREND_STATUS B
> ON
> A.CMP = B.CMP
> WHERE
> A.CY = '2003' AND A.PRD = '03'
> AND
> B.CY = '2003' AND B.PRD = '02' ;
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

B.L. "Tink" Tysor
Bayard Lee Tysor, Inc
(401)965-2688
www.BLTysor.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm