Select not returning results from testing db

[bflochick]

I am running a select statement on our production database and our testing database. On the production database this select returns results, but none on the testing database. I have verified that the tables/data on both are identical, so I am stumped as to why the testing database returns no results. Is there some sort of setting that may need to be changed?

Query:
SELECT e.ID_NUMBER
FROM ENTITY e
WHERE e.PERSON_OR_ORG = 'P'
AND EXISTS (SELECT *
FROM HANDLING
WHERE id_number = e.id_number
AND hnd_type_code = 'X'
AND hnd_status_code = 'A')
AND EXISTS (SELECT *
FROM HARRIS_DATA
WHERE id_number = e.id_number
AND NVL(lost_deceased, ' ') <> 'D')

 

[Dagon]

Are you logged on as the user who owns all the tables ? Prefix the tables with the schema name to verify that it is picking up the table you intend and not a synonym for something else e.g.

SELECT e.ID_NUMBER
FROM owner.ENTITY e
WHERE e.PERSON_OR_ORG = 'P'
AND EXISTS (SELECT *
FROM owner.HANDLING
WHERE id_number = e.id_number
AND hnd_type_code = 'X'
AND hnd_status_code = 'A')
AND EXISTS (SELECT *
FROM owner.HARRIS_DATA
WHERE id_number = e.id_number
AND NVL(lost_deceased, ' ') <> 'D')

Also try removing bits of the query to find out exactly the point at which it gives you no data.

 

[bflochick]

I am logged in as the user who owns the tables, we only have one schema. This also happens with another select query, which is much simpler, so this tells me there has got to be something different between the two databases because the data is identical.

 

[SantaMufasa]

bflo,

As a preliminary QA confirmation for us, could you please run the following queries against each database and post the results here:

SELECT count(*) FROM ENTITY e;
SELECT count(*) FROM HANDLING;
SELECT count(*) FROM HARRIS_DATA;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bflochick]

The counts do not match because we refresh the test database nightly and there is constantly changes being made to the entity and handling table from our users. We do many loads in the morning as well into production. But I did verify a select group of the entities which appear in the production database, against the testing database and their records in each table queried are identical. They should be picked up.

 

[SantaMufasa]

I have verified that the tables/data on both are identical, so I am stumped as to why the testing database returns no results.

The counts do not match because we refresh the test database nightly and there is constantly changes being made to the entity and handling table from our users.

Since the tables are not really identical, you must do as Dagon suggests: Break down your original (malfunctioning) query into pieces and execute those pieces (with literal values for "e.id_number") until you can isolate why your results are not behaving as expected.

Please post your findings here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]