SQL not returning proper results 
Author Message
 SQL not returning proper results

Hi,
I have a table, ORGANIZATION, that is partially defined as:
Organization_Nbr  BIGINT NOT NULL (PK),
Organization_Type_Code  VARCHAR(10) (FK)

There are approximately 2800 rows in this table.  53 of the rows have an
Organization_Type_Code of '001', 14 rows have an Organization_Type_Code of
'014'.  The rest of the type codes are currently NULL.

I issued an SQL statement of
"SELECT Organization_Nbr
from ORGANIZATION
WHERE  Organization_Type_Code <> '001';

My expected results are every row that does not have a type code of '001'.
Instead, I get the 14 rows with a type code of '014' and no rows where the
type code is NULL.

This particular query was performing as expected approximately 2 weeks ago
and, to my knowledge, nothing has changed with the configuration of the
database.  I have run this from the command center and through the command
line with the same results.
The version of DB2 is 7.2 running fixpack 5 on Redhat Linux installed on a
Dell server.

Any ideas as to what may be wrong/what I could try to diagnose and fix would
be greatly appreciated.

Thank you.



Wed, 11 Aug 2004 21:26:43 GMT
 SQL not returning proper results

Can't you just put

SELECT Organization_Nbr
 from ORGANIZATION
 WHERE  Organization_Type_Code <> '001'
 or Organization_Type_Code Is Null

Sammy !!


Quote:
> Hi,
> I have a table, ORGANIZATION, that is partially defined as:
> Organization_Nbr  BIGINT NOT NULL (PK),
> Organization_Type_Code  VARCHAR(10) (FK)

> There are approximately 2800 rows in this table.  53 of the rows have an
> Organization_Type_Code of '001', 14 rows have an Organization_Type_Code of
> '014'.  The rest of the type codes are currently NULL.

> I issued an SQL statement of
> "SELECT Organization_Nbr
> from ORGANIZATION
> WHERE  Organization_Type_Code <> '001';

> My expected results are every row that does not have a type code of '001'.
> Instead, I get the 14 rows with a type code of '014' and no rows where the
> type code is NULL.

> This particular query was performing as expected approximately 2 weeks ago
> and, to my knowledge, nothing has changed with the configuration of the
> database.  I have run this from the command center and through the command
> line with the same results.
> The version of DB2 is 7.2 running fixpack 5 on Redhat Linux installed on a
> Dell server.

> Any ideas as to what may be wrong/what I could try to diagnose and fix
would
> be greatly appreciated.

> Thank you.



Thu, 12 Aug 2004 06:06:42 GMT
 SQL not returning proper results

Quote:
> "SELECT Organization_Nbr
> from ORGANIZATION
> WHERE  Organization_Type_Code <> '001';

> My expected results are every row that does not have a type code of '001'.
> Instead, I get the 14 rows with a type code of '014' and no rows where the
> type code is NULL.

That is the correct result, in accordance with the SQL standard.
Suggest you read up on predicates and nulls.  You would get the same
result with Oracle, SQL Server etc.

Jeremy Rickard
United Systems (UK) Ltd.
http://www.unisystems.co.uk



Thu, 12 Aug 2004 08:28:13 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. sum() grouping function not returning proper results

2. Comparison Operator LIKE not giving proper results in SQL string

3. ADO/ODBC not returning proper values

4. ADO/ODBC not returning proper values

5. Beginner's course to *PROPER* slashing (for HJR, not important, not directly Oracle related)

6. Not returning a result set ????? why from java

7. Crazy Cursor Problem - results not returned

8. URGENT: CONTAINS is not returning the relevant results

9. why my function does not return concatenated result?

10. Recordset not returning anticipated result set.

11. Stored Procedure Does Not Return Results

12. CONTAINS predicate not returning results....


 
Powered by phpBB® Forum Software