Re: Distinct query not working after database upgrade!!

The J Man wrote:
> Michel Cadot wrote:
>
> > Hey dummy, if you copy/paste what you actually done maybe we
> > can help you, there it is just blahblah even your query is wrong.
> >
> > Regards
> > Michel Cadot
>
> Apologies...
> the query i am running is essentially this:
>
> SELECT DISTINCT A.AID, A.TEXT FROM A, B WHERE A.ID = B.ID AND B.date
> >=mydate;
>
> Table B is basically just a logger type table of changes that were made
> to elements in table A. The user would run this query with their own
> date to see what rows have changed in table A since that date. I am
> basically using table B for its date attribute. If there was a date
> attribute in table A i would be saving myself a whole lot of bother,
> but alas it was not me who designed the schema, so i will have to make
> do with the date in table B for the time being...
>
> So essentially i have not "done" anything, the database itself was
> updated and the distinct clause just seems to have stopped working.

Well DISTINCT is a poor way of getting your answer anyway. IMHO, there is no place for DISTINCT in a production query. (one offs from sqlplus is a different matter and even the, I always suggest at least doing a count(*) instead)

SO,
try writing something like this for a test:

SELECT A.AID, A.TEXT, count(*)

    FROM A, B
WHERE A.ID = B.ID AND B.date >=mydate
group by A.AID, A.TEXT ;

then when it is working, drop the count(*) before going to production. The count(*) in this case gives you a feel for how frequently the "log" entries are made.

Your data model still seems a little funky, but then it is hard to tell from a generic example.

Check the release notes, but DISTINCT is, as I've said before, a crutch. Stand up and walk!

  hth,
  ed Received on Tue Aug 01 2006 - 14:16:07 CDT