Link to home
Start Free TrialLog in
Avatar of BritaJ
BritaJFlag for United States of America

asked on

Fetch First Problem in DB2 9.5

Hi All,
I hope you can help, I have a query (actually a sub query part of a much longer query) which works fine in DB2 8.2, but gives funny results in 9.5.

Here is the query:

SELECT  first.lot_id, first.eqp_id FROM lot_eqp_table AS first
WHERE first.eqp_id = (select eqp_id FROM lot_eqp_table where lot_id = eqp_id FETCH FIRST ROW ONLY)

This brings back multiple records for each lot when I run it in 9.5 but has run just fine in 8.2.  The only other difference is that the tables in the 9.5 system are aliased from the production database running 8.2.  Everything else works fine.
This is the only query which has this problem.

Another strange thing I found, if this helps when I add " WHERE first.lot_id = 'abc' " it brings back one record, but if I add " WHERE first.lot_id like 'abc' " I get 4 records.


Thanks,
Brita
SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kent Olsen
Hi Brita,

I'm in agreement with momi that this is a data issue, not a DB2 issue.

SELECT  first.lot_id, first.eqp_id FROM lot_eqp_table AS first
WHERE first.eqp_id = (select eqp_id FROM lot_eqp_table where lot_id = eqp_id FETCH FIRST ROW ONLY)

Note in the query that the implied join limits the results to 1 row with the FETCH FIRST ROW ONLY clause.  The only reasons to write the query this way would be due to a programmer having encountered a duplicate eqp_id value, or expecting a duplicate value.

Is there a primary key on the table?  Or a unique index?  That would be a big clue as to the table's expected usage.

And Momi's query should definitively answer this.  :)
Avatar of BritaJ

ASKER

Hi Momi and Kdo,

This query was not written by me and will probably need to be rewritten.  The data in the table looks something like this:

Lot_id     Eqp_id
aaa          100
aaa          101
bbb         100
ccc           101
ccc           102

The query was supposed to remove the duplicates from the data.  The strange thing is that the data in the 8.2 system is exactly the same as the 9.5 system (an alias is used to access the same table)

I think that I will rewrite the query and use a "group by" to achieve the same results, but we (my coworker brought this to me) can't figure out why we get two aaa records as a result in the 9.5 system but only one in the 8.2 system.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BritaJ

ASKER

Hi Kent,
I believe that you are the most correct. I work in DbVisualizer and when I created similar queries on both my environments I got similar results.  The only time I have a problem is when I am using a nickname to access a table in a different database.  

I changed the query to use group by and that is working with the nicknamed table.  Thank you all for your help.

Brita

ps. mustaccio,
yes that was a typo, it should have said lot_id=first.lot_id.
Avatar of BritaJ

ASKER

Thank you all for your quick response and time working on this issue for me.

Brita
Hi Brita,

You should probably make sure the DBVisualizer is using the correct driver(s) to access DB2.  Right or wrong, consistent answers are a requirement.


Kent